Utiliser des fonctions d'horodatage dans les requêtes

Vous pouvez effectuer différentes opérations sur les valeurs d'horodatage et de durée.

Vous pouvez ajouter une durée à un horodatage, rechercher la différence entre deux horodatages et arrondir l'horodatage à une unité spécifiée. Vous pouvez convertir un horodatage vers/depuis une chaîne avec des modèles personnalisés. Certaines fonctions prennent en charge l'extraction de la partie date d'un horodatage. Vous pouvez également utiliser ces fonctions pour afficher l'heure actuelle.

Les fonctions d'horodatage suivantes sont prises en charge :

Tableau 1 - Fonctions d'horodatage

Fonction Description
horodatage_add Ajoute une durée à une valeur d'horodatage.
diff_horodatage Renvoie le nombre de millisecondes entre deux valeurs d'horodatage.
durée d'obtention Convertit le nombre donné de millisecondes en une chaîne de durée.
timestamp_ceil Arrondit la valeur d'horodatage à l'unité indiquée.
timestamp_floor/timestamp_trunc Arrondit la valeur d'horodatage à l'unité indiquée.
timestamp_round Arrondit la valeur d'horodatage à l'unité indiquée.
timestamp_bucket Arrondit la valeur d'horodatage au début de l'intervalle spécifié, en commençant par une valeur d'origine spécifiée.
format_horodatage Convertit un horodatage en chaîne en fonction du modèle et du fuseau horaire spécifiés.
parse_to_timestamp Convertit une chaîne dans le modèle spécifié en une valeur d'horodatage.
to_last_day_of_month Renvoie le dernier jour du mois à partir d'un horodatage donné.
Fonctions d'extraction d'horodatage

Extrait la partie de date correspondante d'un horodatage donné. Les fonctions prises en charge sont les suivantes :

  • année
  • mois
  • jour
  • heure
  • minute
  • deuxième
  • milliseconde
  • microseconde
  • nanoseconde

Renvoie le numéro de semaine dans l'année. Les fonctions prises en charge sont les suivantes :

  • semaine
  • isoweek

Renvoie l'index correspondant à partir d'un horodatage donné. Les fonctions prises en charge sont les suivantes :

  • quarter
  • jour_de_semaine
  • jour_du_mois
  • jour_de_l'année
temps_en_cours Renvoie l'heure actuelle en tant que nombre de millisecondes.
temps_actuel Renvoie l'heure actuelle sous forme de valeur d'horodatage.

Si vous souhaitez suivre les exemples, reportez-vous à Exemples de données pour exécuter des requêtes afin de visualiser un exemple de données et d'utiliser les scripts pour charger des exemples de données à des fins de test. Les scripts créent les tables utilisées dans les exemples et chargent les données dans les tables.

Si vous souhaitez suivre les exemples, reportez-vous à Exemples de données pour exécuter des requêtes afin de visualiser un exemple de données et d'apprendre à utiliser la console OCI pour créer les exemples de tables et charger des données à l'aide de fichiers JSON.

Fonctions arithmétiques d'horodatage

Vous pouvez utiliser les fonctions timestamp_add, timestamp_diff ou get_duration pour effectuer des opérations arithmétiques sur les valeurs d'horodatage et de durée.

Exemple 1 - Dans la demande de la compagnie aérienne, un délai de cinq minutes est considéré comme "à temps". Affichez l'heure d'arrivée estimée sur la première étape avec un tampon de cinq minutes pour le passager avec le numéro de billet 1762399766476.

SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag
WHERE ticketNo=1762399766476

Explication :Dans l'application de la compagnie aérienne, un client peut avoir n'importe quel nombre de jambes de vol en fonction de la source et de la destination. Dans la requête ci-dessus, vous récupérez l'arrivée estimée dans la "première étape" du voyage. Le premier enregistrement du tableau flightsLeg est donc extrait et l'heure estimatedArrival est extraite du tableau et un tampon de "5 minutes" est ajouté à ce tableau et affiché.

Sortie :

{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}

Remarque :

La colonne estimatedArrival est une chaîne (STRING). Si la colonne contient des valeurs STRING au format ISO-8601, elle sera automatiquement convertie par l'exécution SQL en type de données TIMESTAMP.

ISO8601 décrit une façon internationalement acceptée de représenter les dates, les heures et les durées.

Syntaxe : Date with time : YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

Où :

Exemple 2 : imprimer l'heure d'arrivée estimée dans chaque jambe avec un tampon de cinq minutes pour le passager portant le numéro de billet 1762399766476.

SELECT $s.ticketno, $value as estimate,
timestamp_add($value, '5 minute') AS add5min
FROM baggageinfo $s,
$s.bagInfo.flightLegs.estimatedArrival as $value
WHERE ticketNo=1762399766476

Explication :Vous souhaitez afficher l'heure estimatedArrival sur chaque portion de route. Le nombre de jambes peut être différent pour chaque client. La référence de variable est donc utilisée dans la requête ci-dessus, et le tableau baggageInfo et le tableau flightLegs ne sont pas imbriqués pour exécuter la requête.

Sortie :

{"ticketno":1762399766476,"estimate":"2019-02-03T06:00:00Z",
"add5min":"2019-02-03T06:05:00.000000000Z"}
{"ticketno":1762399766476,"estimate":"2019-02-03T08:22:00Z",
"add5min":"2019-02-03T08:27:00.000000000Z"}

Exemple 3 - Combien de sacs sont arrivés la semaine dernière ?

SELECT count(*) AS COUNT_LASTWEEK FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate < current_time()
AND $element.bagArrivalDate > timestamp_add(current_time(), "-7 days")]

Explication :Vous obtenez le nombre de sacs traités par l'application de la compagnie aérienne au cours de la dernière semaine. Un client peut avoir plusieurs sacs (le tableau bagInfo peut avoir plusieurs enregistrements). La valeur de bagArrivalDate doit être comprise entre aujourd'hui et les 7 derniers jours. Pour chaque enregistrement du tableau bagInfo, vous déterminez si l'heure d'arrivée du conteneur est comprise entre l'heure actuelle et celle de la semaine précédente. La fonction current_time vous donne le temps maintenant. Une condition EXISTS est utilisée comme filtre pour déterminer si la poche a une date d'arrivée au cours de la dernière semaine. La fonction count détermine le nombre total de sacs au cours de cette période.

Sortie :

{"COUNT_LASTWEEK":0}

Exemple 4 - Trouver le nombre de sacs arrivant dans les prochaines 6 heures.

SELECT count(*) AS COUNT_NEXT6HOURS FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate > current_time()
AND $element.bagArrivalDate < timestamp_add(current_time(), "6 hours")]

Explication :Vous obtenez le nombre de sacs qui seront traités par l'application de la compagnie aérienne dans les 6 prochaines heures. Un client peut avoir plusieurs sacs (le tableau bagInfo peut avoir plusieurs enregistrements). La valeur bagArrivalDate doit être comprise entre le moment présent et les 6 prochaines heures. Pour chaque enregistrement du tableau bagInfo, vous déterminez si l'heure d'arrivée du conteneur est comprise entre l'heure actuelle et six heures plus tard. La fonction current_time vous donne le temps maintenant. Une condition EXISTS est utilisée comme filtre pour déterminer si la poche a une date d'arrivée dans les six heures suivantes. La fonction count détermine le nombre total de sacs au cours de cette période.

Sortie :

{"COUNT_NEXT6HOURS":0}

Exemple 5 - Quelle est la durée entre le moment où le bagage a été embarqué à une jambe et celui où le passager a atteint la jambe suivante avec le numéro de billet 1762355527825 ?

SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825

Explication :Dans une application de compagnie aérienne, chaque client peut avoir un nombre différent de sauts/jambes entre sa source et sa destination. Dans cette requête, vous déterminez la durée entre chaque segment de vol. Cette différence est déterminée par la différence entre bagArrivalDate et flightDate pour chaque segment de vol. Pour déterminer la durée en jours, heures ou minutes, transmettez le résultat de la fonction timestamp_diff à la fonction get_duration.

Sortie :

{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z",
"diff":"3 hours 17 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z",
"diff":"2 hours 54 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z",
"diff":"1 hour 54 minutes"}

Pour déterminer la durée en millisecondes, utilisez la fonction timestamp_diff.

SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo,
$bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825

Sortie :

{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z","diff":11820000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z","diff":10440000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z","diff":6840000}

Exemple 6 - Combien de temps faut-il entre le moment de l'enregistrement et le moment où le sac est scanné au point d'embarquement pour le passager avec le numéro de billet 176234463813 ?

SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
get_duration(timestamp_diff(
    $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
    $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
)) AS diff
FROM baggageinfo $s,
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

Explication :Dans les données de bagages, chaque flightLeg dispose d'un tableau d'actions. Le tableau d'actions comporte trois actions différentes. Le code d'action du premier élément du tableau est Checkin/Offload. Pour la première jambe, le code d'action est Archiver et pour les autres jambes, le code d'action est Décharger au niveau du saut. Le code d'action du deuxième élément du tableau est BagTag Scan. Dans la requête ci-dessus, vous déterminez la différence de temps d'action entre l'analyse des étiquettes de poche et l'heure d'admission. La fonction contains permet de filtrer l'heure de l'action uniquement si le code d'action est Archiver ou Analyse de poche. Etant donné que seule la première portion de route comporte des détails d'enregistrement et de balayage de conteneur, vous pouvez également filtrer les données à l'aide de la fonction starts_with pour extraire uniquement le code source fltRouteSrc. Pour déterminer la durée en jours, heures ou minutes, transmettez le résultat de la fonction timestamp_diff à la fonction get_duration.

Pour déterminer la durée en millisecondes, utilisez la fonction timestamp_diff.

SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
timestamp_diff(
   $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
   $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
) AS diff
FROM baggageinfo $s,
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

Sortie :

{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":"- 1 hour 24 minutes"}

Exemple 7 : combien de temps faut-il pour que les sacs d'un client portant le numéro de ticket 1762320369957 atteignent le premier point de transit ?

SELECT  $bagInfo.flightLegs[1].actions[2].actionTime,
$bagInfo.flightLegs[0].actions[0].actionTime,
get_duration(timestamp_diff($bagInfo.flightLegs[1].actions[2].actionTime,
                            $bagInfo.flightLegs[0].actions[0].actionTime)) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo
WHERE ticketNo=1762320369957

Explication :Dans une application de compagnie aérienne, chaque client peut avoir un nombre différent de sauts/jambes entre sa source et sa destination. Dans l'exemple ci-dessus, vous déterminez le temps nécessaire pour que le sac atteigne le premier point de transit. Dans les données relatives aux bagages, flightLeg est un tableau. Le premier enregistrement du tableau fait référence aux détails du premier point de transit. Le paramètre flightDate du premier enregistrement correspond à l'heure à laquelle le conteneur quitte la source et le paramètre estimatedArrival du premier enregistrement de jambe de vol indique l'heure à laquelle il atteint le premier point de transit. La différence entre les deux donne le temps nécessaire pour que le sac atteigne le premier point de transit. Pour déterminer la durée en jours, heures ou minutes, transmettez le résultat de la fonction timestamp_diff à la fonction get_duration.

Pour déterminer la durée en millisecondes, utilisez la fonction timestamp_diff.

SELECT  $bagInfo.flightLegs[0].flightDate,
$bagInfo.flightLegs[0].estimatedArrival,
timestamp_diff($bagInfo.flightLegs[0].estimatedArrival,
$bagInfo.flightLegs[0].flightDate) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo
WHERE ticketNo=1762320369957

Sortie :

{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:00:00Z","diff":"13 hours"}
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:40:00Z","diff":"13 hours 40 minutes"}

Fonctions d'arrondi d'horodatage

Vous pouvez utiliser les fonctions timestamp_ceil, timestamp_floor, timestamp_trunc, timestamp_round et timestamp_bucket pour arrondir les valeurs d'horodatage.

Pour les fonctions timestamp_ceil, timestamp_floor, timestamp_trunc et timestamp_round, vous devez fournir un argument unit comme deuxième argument. unit indique la précision à prendre en compte lors de l'arrondi de l'horodatage d'entrée.

Les unités suivantes sont prises en charge au format singulier ou pluriel : YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND.

Vous pouvez utiliser la fonction timestamp_bucket pour arrondir la valeur d'horodatage donnée au début de l'intervalle indiqué (bucket). L'intervalle commence à une origine spécifiée sur la chronologie.

timestamp_bucket prend en charge les intervalles suivants au format singulier ou pluriel : WEEK, DAY, HOUR, MINUTE, SECOND.

Exemple 1 : à partir des données de suivi des bagages de la compagnie aérienne, imprimez la date d'arrivée et la date d'enchère des bagages pour un passager portant le numéro de billet 1762344493810, en considérant 90 jours comme période de conservation des bagages.

SELECT $b.bagArrivalDate AS BagArrival,
timestamp_ceil(timestamp_add($b.bagArrivalDate, "90 Days"), 'day') AS BagCollection
FROM BaggageInfo bag, bag.bagInfo AS $b
WHERE ticketNo=1762344493810

Explication : cette requête montre comment imbriquer les fonctions d'horodatage. Pour déterminer la date à laquelle un conteneur non réclamé est conservé, ajoutez 90 jours à bagArrivalDate à l'aide de la fonction timestamp_add. La fonction timestamp_ceil arrondit la valeur au début du jour suivant.

Sortie :

{"BagArrival":"2019-02-01T16:13:00Z","BagCollection":"2019-05-03T00:00:00Z"}

Exemple 2 - Imprimer le nom, le numéro de vol et la date de voyage de tous les passagers qui sont montés à l'aéroport d'origine JFK au mois de mars 2019.

SELECT bag.fullName, $f.flightNo, $f.flightDate
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
WHERE $f.fltRouteSrc = "JFK" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-03-01'

Explication : vous utilisez la fonction timestamp_floor avec la valeur d'unité MONTH pour arrondir les dates de déplacement au début du mois. Vous comparez ensuite la valeur d'horodatage obtenue avec la chaîne "2019-03-01" pour sélectionner les passagers souhaités. Cette requête ne prend pas en compte les passagers en transit.

Cet exemple fournit la date dans une chaîne au format ISO-8601, qui obtient implicitement CAST dans une valeur TIMESTAMP.

Pour éviter la duplication des résultats en raison de plusieurs sacs enregistrés par un passager, vous ne considérez que le premier élément du tableau bagInfo dans cette requête.

Sortie :

{"fullName":"Kendal Biddle","flightNo":"BM127","flightDate":"2019-03-04T06:00:00Z"}
{"fullName":"Dierdre Amador","flightNo":"BM495","flightDate":"2019-03-07T07:00:00Z"}

Exemple 3 - A partir des données de suivi des bagages de la compagnie aérienne, imprimez toutes les activités effectuées sur les bagages enregistrés dans la station d'origine MEL. Alignez les actions sur un intervalle d'une minute.

SELECT $b.actionAt,
       $b.actionCode,
       timestamp_round($b.actionTime, 'MINUTE') as actionTime
FROM baggageInfo bag, bag.bagInfo[0].flightLegs[0].actions[] AS $b
WHERE bag.bagInfo[0].flightLegs[0].fltRouteSrc = "MEL"

Explication : dans cette requête, vous utilisez la fonction timestamp_round avec l'unité MINUTE pour arrondir l'élément actionTime à la MINUTE la plus proche.

Pour éviter la duplication des résultats en raison de plusieurs bagages enregistrés par un passager, vous ne considérez que le premier élément du tableau bagInfo dans cette requête.

Sortie :

{"actionAt":"MEL","actionCode":"ONLOAD to LAX","actionTime":"2019-03-01T12:20:00Z"}
{"actionAt":"MEL","actionCode":"BagTag Scan at MEL","actionTime":"2019-03-01T11:52:00Z"}
{"actionAt":"MEL","actionCode":"Checkin at MEL","actionTime":"2019-03-01T11:43:00Z"}

Exemple 4 - Extraire les statistiques du nombre de passagers au départ de l'aéroport IST toutes les 12 heures avec des seaux à partir du 1er janvier 2019. Considérez les données uniquement pour le mois de février 2019.

SELECT $t AS DATE,
count($t) AS FLIGHTCOUNT
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[] $f,
timestamp_bucket($f.flightDate, '12 HOURS', '2019-01-01T00') $t
WHERE $f.fltRouteSrc =any "IST" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-02-01T00:00:00Z'
GROUP BY $t
ORDER BY $t

Explication : pour considérer les passagers voyageant en février 2019, utilisez la fonction timestamp_floor et arrondissez le flightDate au début du mois. Comparez le résultat avec la chaîne "2019-02-01T00:00:00Z". Cet exemple fournit la date dans une chaîne au format ISO-8601, qui obtient implicitement CAST dans une valeur TIMESTAMP.

Pour inclure les vols de transit depuis l'aéroport IST, utilisez le constructeur de tableau [ ] pour indiquer que flightLegs est un tableau et tenez compte de chaque élément de tableau fltRouteSrc dans la recherche.

Utilisez la fonction timsestamp_bucket sur les champs flightDate avec un intervalle de 12 heures et une origine de 1er janvier 2019.

Sortie :

{"DATE":"2019-02-02T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T00:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T12:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-07T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-11T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-12T00:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-12T12:00:00.000000000Z","FLIGHTCOUNT":1}

Fonctions de format d'horodatage

Vous pouvez utiliser les fonctions format_timestamp et parse_to_timestamp pour formater les valeurs d'horodatage. Vous pouvez également utiliser la fonction to_last_day_of_month pour extraire le dernier jour du mois à partir d'un horodatage donné.

Exemple 1 - Pour un passager avec un numéro de billet spécifique, imprimez l'heure d'arrivée estimée sur la première jambe en fonction de pattern et du timezone saisi.

SELECT $info.estimatedArrival,
format_timestamp($info.estimatedArrival, "MMM dd, yyyy HH:mm:ss O", "America/Vancouver") AS FormattedTimestamp
FROM BaggageInfo bag, bag.bagInfo.flightLegs[0] AS $info
WHERE ticketNo= 1762399766476

Explication : Dans cette requête, vous spécifiez le champ estimatedArrival, pattern, et le nom complet de timezone en tant qu'arguments de la fonction format_timestamp pour convertir la chaîne timestamp au modèle "MMM dd, yyyy HH:mm:ss" spécifié.

Remarque : la lettre "O" dans l'argument pattern représente ZoneOffset, qui affiche la durée qui diffère de Greenwich/UTC dans la chaîne résultante.

Sortie :

{"estimatedArrival":"2019-02-03T06:00:00Z","FormattedTimestamp":"Feb 02, 2019 22:00:00 GMT-8"}

Exemple 2 : analysez le fichier string donné avec le fichier pattern spécifié, qui inclut un décalage de zone, dans un horodatage.

SELECT format_timestamp(parse_to_timestamp('2024/02/12 18:30:54 GMT+02:00', "yyyy/dd/MM HH:mm:ss OOOO"),"yyyy-MM-dd HH:mm:ss OOOO","GMT+02:00")AS TIMESTAMP
FROM BaggageInfo
WHERE ticketNo=1762390789239

Explication : Dans cette requête, l'argument string a un TimeZoneID, GMT+02:00, de sorte que l'argument pattern doit inclure un symbole de zone ou un ZoneOffset. Lorsque la fonction format_timestamp est encapsulée, l'horodatage de sortie s'affiche dans le fuseau horaire GMT+02:00.

Sortie :

{"TIMESTAMP":"2024-12-02 18:30:54 GMT+02:00"}

Exemple 3 - Pour un abonné, imprimez le dernier jour du mois au cours duquel l'abonnement au compte expire.

SELECT sa.acct_id, to_last_day_of_month(sa.account_expiry) AS lastday FROM stream_acct sa WHERE profile_name="DM"

Sortie :

{"acct_id":4,"lastday":"2024-03-31T00:00:00Z"}

Fonctions d'extraction d'horodatage

Les fonctions d'extraction d'horodatage extraient la date, la semaine ou la valeur d'index correspondante à partir d'un horodatage donné.

Les fonctions d'extraction de date renvoient l'année/le mois/le jour/l'heure/la minute/la seconde/la milliseconde/la microseconde/la nanoseconde correspondante à partir d'un horodatage.

Exemple 1 - Obtenir les détails de voyage consolidés des passagers à partir des données de suivi des bagages de la compagnie aérienne.

Dans une application aérienne, il est avantageux pour les passagers d'avoir un résumé rapide de leurs détails de voyage à venir. Vous pouvez utiliser diverses fonctions de temps pour obtenir les détails de voyage consolidés des passagers à partir du tableau BaggageInfo.

SELECT DISTINCT
$s.fullName,
$s.bagInfo[].flightLegs[].flightNo AS flightnumbers,
$s.bagInfo[].flightLegs[].fltRouteSrc AS From,
concat ($t1,":", $t2,":", $t3) AS Traveldate
FROM baggageinfo $s, $s.bagInfo[].flightLegs[].flightDate AS $bagInfo,
day(CAST($bagInfo AS Timestamp(0))) $t1,
month(CAST($bagInfo AS Timestamp(0))) $t2,
year(CAST($bagInfo AS Timestamp(0))) $t3

Explication:

Vous pouvez utiliser les fonctions de temps pour extraire la date, le mois et l'année du voyage. La fonction de chaîne concat permet de concaténer les enregistrements de déplacement extraits pour les afficher dans le format souhaité sur l'application. Vous devez d'abord utiliser l'expression CAST pour convertir flightDates en TIMESTAMP, puis extraire les détails de date, de mois et d'année à partir de l'horodatage.

Sortie :

{"fullName":"Adam Phillips","flightnumbers":["BM604","BM667"],"From":["MIA","LAX"],"Traveldate":"1:2:2019"}

{"fullName":"Adelaide Willard","flightnumbers":["BM79","BM907"],"From":["GRU","ORD"],"Traveldate":"15:2:2019"}

La requête renvoie les détails du vol qui peuvent servir de recherche rapide pour les passagers.

Les fonctions d'extraction de semaine renvoient la semaine/semaine correspondante à partir d'un horodatage.

Exemple 2 - Déterminer la semaine et le numéro de semaine ISO à partir de la date de voyage d'un passager.

SELECT
$s.fullName,
$s.contactPhone,
week(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS TravelWeek,
isoweek(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS ISO_TravelWeek
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explication : Vous devez d'abord utiliser l'expression CAST pour convertir le fichier flightDate en un TIMESTAMP, puis extraire la semaine et l'isoweek de l'horodatage.

Sortie :

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5}

Les fonctions d'extraction d'index d'horodatage renvoient l'index de trimestre/semaine/mois/année correspondant à partir d'un horodatage.

Exemple 3 - Trouver le jour de la semaine pour les horodatages donnés.

SELECT day_of_week("2024-06-19") AS DAYVAL1,
day_of_week(parse_to_timestamp('06/19/24', 'MM/dd/yy')) AS DAYVAL2
FROM BaggageInfo
WHERE ticketNo=1762344493810

Explication : le deuxième horodatage de la requête est dans un format '06/19/24' non pris en charge par lui-même, alors encapsulez-le dans la fonction parse_to_timestamp pour le rendre valide.

Sortie :

{
  "DAYVAL1" : 3,
  "DAYVAL2" : 3
}

Fonctions de temps en cours

Vous pouvez utiliser les fonctions current_time_millis et current_time pour extraire l'heure en cours. La fonction current_time_millis renvoie l'heure en tant que nombre de millisecondes. La fonction current_time renvoie l'heure en tant que valeur d'horodatage.

Exemple 1 - Déterminer le délai entre la dernière date de voyage d'un passager et la date courante.

Dans une application de compagnie aérienne, quelques clients voyagent très fréquemment et ont droit à des récompenses de miles de voyage fréquents. Vous pouvez déterminer le laps de temps entre la dernière date de voyage d'un passager et la date actuelle pour évaluer s'ils peuvent être pris en compte pour un tel programme de récompense.

SELECT
$s.fullName,
$s.contactPhone,
get_duration(timestamp_diff(current_time(), CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0)))) AS LastTravel
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explication:

Vous pouvez utiliser la fonction current_time pour obtenir l'heure actuelle. Pour déterminer la durée entre la dernière date de déplacement et la date actuelle, vous pouvez fournir l'heure actuelle à la fonction get_duration/timestamp_diff avec la dernière heure de déplacement. Pour plus de détails sur les fonctions timestamp_diff et get_duration.

Sortie :

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","LastTravel":"1453 days 6 hours 20 minutes 56 seconds 601 milliseconds"}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","LastTravel":"1451 days 23 hours 19 minutes 39 seconds 543 milliseconds"}

La fonction current_time permet de calculer l'heure en cours. Utilisez la fonction timestamp_diff pour calculer la différence de temps entre l'heure actuelle et la date du dernier vol. Vous devez d'abord utiliser l'expression CAST pour convertir flightDates en TIMESTAMP, puis extraire les détails du jour, du mois et de l'année à partir de l'horodatage. Etant donné que la fonction timestamp_diff renvoie le nombre de millisecondes entre deux valeurs d'horodatage, vous utilisez ensuite la fonction get_duration pour convertir les millisecondes en chaîne de durée.

La fonction get_duration convertit les millisecondes en jours, heures, minutes, secondes et millisecondes en fonction de la valeur renvoyée. Les conversions suivantes sont prises en compte à des fins de calcul :

1000 milliseconds = 1 second
60 seconds = 1 minute
60 minutes = 1 hour
24 hours = 1 day

Par exemple : si la fonction timestamp_diff renvoie la valeur 129084684821 millisecondes, la fonction get_duration la convertit en 1494 jours 52 minutes 4 secondes 687 millisecondes.

Exemples utilisant l'API QueryRequest

Vous pouvez utiliser l'API QueryRequest et appliquer des fonctions SQL pour extraire des données d'une table NoSQL.

Pour exécuter la requête, utilisez l'API NoSQLHandle.query().

Téléchargez le code complet SQLFunctions.java à partir des exemples ici.

 //Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
}
String ts_func1="SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")"+
                         " AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
System.out.println("Using timestamp_add function ");
fetchRows(handle,ts_func1);
String ts_func2="SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate, "+
                "get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff "+
                 "FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg "+
                 "WHERE ticketNo=1762344493810";
System.out.println("Using get_duration and timestamp_diff function ");
fetchRows(handle,ts_func2);

Pour exécuter votre requête, utilisez la méthode borneo.NoSQLHandle.query().

Téléchargez le code complet SQLFunctions.py à partir des exemples ici.

# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))
 ts_func1 = '''SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                 AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625'''
print('Using timestamp_add function:')
fetch_data(handle,ts_func1)

ts_func2 = '''SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
              get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
              FROM baggageinfo $s,
              $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
              WHERE ticketNo=1762344493810'''
print('Using get_duration and timestamp_diff function:')
fetch_data(handle,ts_func2)

Pour exécuter une requête, utilisez la fonction Client.Query.

Téléchargez le code complet SQLFunctions.go à partir des exemples ici.

 //fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
  	return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
}
ts_func1 := `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
fmt.Printf("Using timestamp_add function::\n")
fetchData(client, err,tableName,ts_func1)

ts_func2 := `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
             get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
             FROM baggageinfo $s,
             $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
             WHERE ticketNo=1762344493810`
fmt.Printf("Using get_duration and timestamp_diff function:\n")
fetchData(client, err,tableName,ts_func2)

Pour exécuter une requête, utilisez la méthode query.

JavaScript : téléchargez le code complet SQLFunctions.js à partir des exemples ici.

  //fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

TypeScript : téléchargez le code complet SQLFunctions.ts à partir des exemples ici.

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
const ts_func1 = `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                  AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
console.log("Using timestamp_add function:");
await fetchData(handle,ts_func1);

const ts_func2 = `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
                  get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
                  FROM baggageinfo $s,
                  $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
                  WHERE ticketNo=1762344493810`
console.log("Using get_duration and timestamp_diff function:");
await fetchData(handle,ts_func2);

Pour exécuter une requête, vous pouvez appeler la méthode QueryAsync ou la méthode GetQueryAsyncEnumerable et itérer sur l'énumérable asynchrone obtenu.

Téléchargez le code complet SQLFunctions.cs à partir des exemples ici.

private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
}
private const string ts_func1 =@"SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, ""5 minutes"")
                                       AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
Console.WriteLine("\nUsing timestamp_add function!");
await fetchData(client,ts_func1);

private const string ts_func2 =@"SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
                                       get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
                                       FROM baggageinfo $s,
                                       $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
                                       WHERE ticketNo=1762344493810";
Console.WriteLine("\nUsing get_duration and timestamp_diff function!");
await fetchData(client,ts_func2);

Rubriques connexes