Utilisation des fonctions d'horodatage dans les interrogations

Vous pouvez effectuer diverses 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 lancer 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 courante.

Les fonctions d'horodatage suivantes sont prises en charge :

Tableau 1 - Fonctions d'horodatage

Fonction Description
timestamp_add; Ajoute une durée à une valeur d'horodatage.
timestamp_diff; Retourne le nombre de millisecondes entre deux valeurs d'horodatage.
get_durée Convertit le nombre de millisecondes indiqué en chaîne de durée.
timestamp_ceil; Arrondit la valeur d'horodatage à l'unité spécifiée.
timestamp_floor/timestamp_trunc Arrondit la valeur d'horodatage à l'unité spécifiée.
timestamp_round; Arrondit la valeur d'horodatage à l'unité spécifié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_timestamp; Convertit un horodatage en chaîne en fonction du modèle spécifié et du fuseau horaire.
parse_to_timestamp Convertit une chaîne dans le modèle spécifié en une valeur d'horodatage.
to_last_day_of_month; Retourne 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 suivantes sont prises en charge :

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

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

  • semaine
  • isoweek

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

  • trimestre
  • jour_de_semaine
  • jour_du_mois
  • jour_de_l'année
current_time_millis Retourne l'heure courante en tant que nombre de millisecondes.
current_time Retourne l'heure courante en tant que valeur d'horodatage.

Si vous voulez suivre les exemples, voir Exemples de données pour exécuter des interrogations pour voir des données-échantillons et utiliser les scripts pour charger des données-échantillons à 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 voulez suivre les exemples, voir Exemples de données pour exécuter des interrogations pour voir des données-échantillons et apprendre à utiliser la console OCI pour créer les tables d'exemples 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 aérienne, un tampon de cinq minutes de retard est considéré "à temps". Imprimez l'heure d'arrivée estimative sur la première étape avec une mémoire 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 transport aérien, un client peut avoir n'importe quel nombre de portions 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. Ainsi, le premier enregistrement du tableau flightsLeg est extrait et l'heure estimatedArrival est extraite du tableau et un tampon de "5 minutes" est ajouté à celui-ci et affiché.

Sortie :

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

Note :

La colonne estimatedArrival est une STRING. Si la colonne a 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 et heure : YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

Où :

Exemple 2 - Imprimez l'heure d'arrivée estimative dans chaque portion de route 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 voulez afficher l'heure estimatedArrival sur chaque portion de route. Le nombre de jambes peut être différent pour chaque client. Ainsi, la référence de variable est utilisée dans l'interrogation ci-dessus et le tableau baggageInfo et le tableau flightLegs ne sont pas imbriqués pour exécuter l'interrogation.

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 aérienne au cours de la dernière semaine. Un client peut avoir plus d'un sac (un tableau bagInfo peut avoir plus d'un enregistrement). La valeur 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 sac se situe entre l'heure actuelle et l'heure il y a une semaine. La fonction current_time vous donne le temps maintenant. Une condition EXISTS est utilisée comme filtre pour déterminer si le sac 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 6 heures suivantes.

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 aérienne dans les prochaines 6 heures. Un client peut avoir plus d'un sac (c'est-à-dire que le tableau bagInfo peut avoir plus d'un enregistrement). La valeur bagArrivalDate doit être comprise entre le moment présent et les 6 heures suivantes. Pour chaque enregistrement du tableau bagInfo, vous déterminez si l'heure d'arrivée du sac se situe entre le moment présent 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 le sac a une date d'arrivée dans les six prochaines heures. 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 atteint la jambe suivante pour le passager 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 transport aérien, chaque client peut avoir un nombre différent de sauts/jambes entre sa source et sa destination. Dans cette interrogation, vous déterminez le temps nécessaire entre chaque portion de vol. Cela est déterminé par la différence entre bagArrivalDate et flightDate pour chaque portion 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 des bagages, chaque flightLeg comporte un tableau d'actions. Il existe trois actions différentes dans le tableau d'actions. Le code d'action du premier élément du tableau est Checkin/Offload. Pour la première jambe, le code d'action est Checkin et pour les autres jambes, le code d'action est Offload au saut. Le code d'action du deuxième élément du tableau est BagTag Scan. Dans l'interrogation ci-dessus, vous déterminez la différence de temps d'action entre le balayage de l'étiquette d'entité composite et l'heure d'enregistrement. Vous utilisez la fonction contains pour filtrer l'heure de l'action uniquement si le code d'action est Checkin ou BagScan. Comme seule la première portion de vol contient les détails de l'enregistrement et du balayage d'entité, vous filtrez également 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 sans billet 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 transport aérien, 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 de bagages, flightLeg est un tableau. Le premier enregistrement du tableau fait référence aux détails du premier point de transit. flightDate dans le premier enregistrement est l'heure à laquelle le sac quitte la source et estimatedArrival dans le premier enregistrement de portion 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'arrondissement 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 unit comme deuxième argument. unit spécifie la précision à prendre en compte lors de l'arrondissement 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 indiquée au début de l'intervalle spécifié (seau). L'intervalle commence à une origine spécifiée dans 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 des compagnies aériennes, imprimez la date d'arrivée des bagages 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 interrogation montre comment imbriquer les fonctions d'horodatage. Pour déterminer la date de conservation d'une sacoche non réclamée, 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 ont embarqué à l'aéroport d'origine JFK au cours du 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 question ne tient pas compte des 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 bagages 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 - À 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 interrogation, vous utilisez la fonction timestamp_round avec l'unité MINUTE pour arrondir 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. Envisagez 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 prendre en compte les passagers voyageant en février 2019, utilisez la fonction timestamp_floor et arrondissez 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 considérez 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 étape en fonction du pattern et du timezone entré.

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 interrogation, vous spécifiez le champ estimatedArrival, pattern et le nom complet de timezone comme arguments pour la fonction format_timestamp afin de convertir la chaîne timestamp au modèle "MMM dd, yyyy HH:mm:ss" spécifié.

Note : La lettre 'O' dans l'argument pattern représente ZoneOffset, qui imprime 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 la valeur string indiquée avec la valeur pattern spécifiée, 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 interrogation, l'argument string a un TimeZoneID, GMT+02:00, de sorte que l'argument pattern doit inclure un symbole de zone ou un ZoneOffset. Lorsqu'il est encapsulé dans la fonction format_timestamp, 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 et la nanoseconde correspondantes à partir d'un horodatage.

Exemple 1 - Obtenir des détails de voyage consolidés des passagers à partir des données de suivi des bagages des compagnies aériennes.

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 de déplacement. La fonction de chaîne concat est utilisée pour concaténer les enregistrements de déplacement extraits afin de les afficher dans le format souhaité dans 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 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 retournent la semaine/isoweek correspondante à partir d'un horodatage.

Exemple 2 - Déterminer la semaine et le numéro ISO de la semaine à 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 flightDate en TIMESTAMP, puis extraire la semaine et 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 trimestriel/semaine/mois/année correspondant à partir d'un horodatage.

Exemple 3 - Rechercher 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 l'interrogation est dans un format non pris en charge '06/19/24' par lui-même. Encapsulez-le dans la fonction parse_to_timestamp pour le rendre valide.

Sortie :

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

Fonctions de temps courantes

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

Exemple 1 - Déterminer l'intervalle de temps entre la dernière date de déplacement d'un passager et la date courante.

Dans une application aérienne, quelques clients voyagent très fréquemment et ont droit à des récompenses fréquentes de miles. Vous pouvez déterminer le délai entre la dernière date de voyage d'un passager et la date courante 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 courante. Pour déterminer l'intervalle entre la dernière date de déplacement et la date courante, vous pouvez fournir l'heure courante à la fonction get_duration/timestamp_diff avec le dernier temps 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"}

Vous utilisez la fonction current_time pour calculer l'heure courante. Utilisez la fonction timestamp_diff pour calculer l'écart de temps entre l'heure courante et la dernière date de 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 de l'horodatage. Comme la fonction timestamp_diff retourne 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 retournée. Les conversions suivantes sont prises en compte aux 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 retourne la valeur 129084684821 millisecondes, la fonction get_duration la convertit en conséquence en 1494 jours, 52 minutes et 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 une interrogation, 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 interrogation, 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 interrogation, 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 interrogation, 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 interrogation, vous pouvez appeler la méthode QueryAsync ou appeler la méthode GetQueryAsyncEnumerable et effectuer une itération sur l'énumérable asynchrone résultant.

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