Utilizzo delle funzioni Timestamp nelle query

È possibile eseguire varie operazioni sui valori di indicatore orario e durata.

È possibile aggiungere una durata a un indicatore orario, trovare la differenza tra due indicatori orari e arrotondare l'indicatore orario a un'unità specificata. Puoi lanciare un timestamp da/per la stringa con motivi personalizzati. Alcune delle funzioni supportano l'estrazione della parte data di un indicatore orario. È inoltre possibile utilizzare queste funzioni per visualizzare l'ora corrente.

Sono supportate le seguenti funzioni di indicatore orario:

Tabella 1 - Funzioni indicatore orario

Funzione Descrizione
data/ora Aggiunge una durata a un valore di indicatore orario.
timestamp_diff Restituisce il numero di millisecondi tra due valori di indicatore orario.
get_duration Converte il numero di millisecondi specificato in una stringa di durata.
timestamp_ceil Arrotonda il valore dell'indicatore orario all'unità specificata.
timestamp_floor/timestamp_trunc Arrotonda il valore dell'indicatore orario all'unità specificata.
timestamp_round Arrotonda il valore dell'indicatore orario all'unità specificata.
timestamp_bucket Arrotonda il valore dell'indicatore orario all'inizio dell'intervallo specificato, a partire da un valore di origine specificato.
indicatore orario formato Converte un indicatore orario in una stringa in base al pattern specificato e al fuso orario.
analizza_to_timestamp Converte una stringa nel pattern specificato in un valore di indicatore orario.
to_last_day_of_month Restituisce l'ultimo giorno del mese da un determinato indicatore orario.
Funzioni di estrazione indicatore orario

Estrae la parte data corrispondente di un determinato indicatore orario. Sono supportate le funzioni riportate di seguito:

  • anno
  • mese
  • giorno
  • ora
  • minuto
  • secondo
  • millisecondo
  • microsecondo
  • nanosecondo

Restituisce il numero della settimana nell'anno. Sono supportate le funzioni riportate di seguito:

  • settimana
  • isoweek

Restituisce l'indice corrispondente da un determinato indicatore orario. Sono supportate le funzioni riportate di seguito:

  • trimestre
  • day_of_week
  • giorno_del_mese
  • giorno_di_anno
ora_corrente_mill Restituisce l'ora corrente come numero di millisecondi.
ora_corrente Restituisce l'ora corrente come valore dell'indicatore orario.

Se si desidera seguire gli esempi, vedere Dati di esempio per eseguire le query per visualizzare i dati di esempio e utilizzare gli script per caricare i dati di esempio per i test. Gli script creano le tabelle utilizzate negli esempi e caricano i dati nelle tabelle.

Se si desidera seguire gli esempi, vedere Dati di esempio per eseguire le query per visualizzare dati di esempio e imparare a utilizzare OCI Console per creare le tabelle di esempio e caricare i dati utilizzando i file JSON.

Funzioni aritmetiche indicatore orario

È possibile utilizzare le funzioni timestamp_add, timestamp_diff o get_duration per eseguire operazioni aritmetiche sui valori di data e ora e durata.

Esempio 1 - Nell'applicazione della compagnia aerea, un buffer di cinque minuti di ritardo è considerato "in tempo". Stampare l'orario di arrivo previsto sulla prima tappa con un buffer di cinque minuti per il passeggero con numero di biglietto 1762399766476.

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

Spiegazione: nell'applicazione della compagnia aerea, un cliente può avere un numero qualsiasi di tratte di volo a seconda dell'origine e della destinazione. Nella query precedente, si sta recuperando l'arrivo previsto nella "prima tappa" del viaggio. Quindi il primo record dell'array flightsLeg viene recuperato e l'ora estimatedArrival viene recuperata dall'array e un buffer di "5 minuti" viene aggiunto a quello e visualizzato.

Output:

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

Nota:

La colonna estimatedArrival è una STRING. Se la colonna contiene valori STRING in formato ISO-8601, verrà convertita automaticamente dal runtime SQL nel tipo di dati TIMESTAMP.

ISO8601 descrive un modo accettato a livello internazionale per rappresentare date, orari e durate.

Sintassi: data e ora: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

dove:

Esempio 2 - Stampa l'orario di arrivo stimato in ogni tappa con un buffer di cinque minuti per il passeggero con numero di biglietto 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

Spiegazione: si desidera visualizzare l'ora estimatedArrival in ogni tappa. Il numero di gambe può essere diverso per ogni cliente. Il riferimento variabile viene quindi utilizzato nella query precedente e l'array baggageInfo e l'array flightLegs non vengono nidificati per eseguire la query.

Output:

{"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"}

Esempio 3 - Quante borse sono arrivate nell'ultima settimana?

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")]

Spiegazione: si ottiene un conteggio del numero di bagagli elaborati dall'applicazione della compagnia aerea nell'ultima settimana. Un cliente può avere più borse (l'array bagInfo può avere più record). Il valore di bagArrivalDate deve essere compreso tra oggi e gli ultimi 7 giorni. Per ogni record nell'array bagInfo, si determina se l'orario di arrivo della borsa è compreso tra l'ora corrente e una settimana fa. La funzione current_time ti dà il tempo ora. Una condizione EXISTS viene utilizzata come filtro per determinare se la borsa ha una data di arrivo nell'ultima settimana. La funzione count determina il numero totale di sacchetti in questo periodo di tempo.

Output:

{"COUNT_LASTWEEK":0}

Esempio 4 - Trova il numero di borse in arrivo nelle prossime 6 ore.

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")]

Spiegazione: si ottiene un conteggio del numero di bagagli che verranno elaborati dall'applicazione della compagnia aerea nelle prossime 6 ore. Un cliente può avere più borse (l'array bagInfo può avere più record). bagArrivalDate deve essere compreso tra l'ora corrente e le 6 ore successive. Per ogni record nell'array bagInfo, si determina se l'orario di arrivo della borsa è compreso tra l'ora corrente e sei ore dopo. La funzione current_time ti dà il tempo ora. Una condizione EXISTS viene utilizzata come filtro per determinare se la borsa ha una data di arrivo nelle sei ore successive. La funzione count determina il numero totale di sacchetti in questo periodo di tempo.

Output:

{"COUNT_NEXT6HOURS":0}

Esempio 5 - Qual è la durata tra il momento in cui il bagaglio è stato imbarcato su una gamba e il momento in cui ha raggiunto la tappa successiva per il passeggero con numero di biglietto 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

Spiegazione:in un'applicazione aerea ogni cliente può avere un numero diverso di luppoli/gambe tra l'origine e la destinazione. In questa query, si determina il tempo impiegato tra ogni tappa di volo. Ciò è determinato dalla differenza tra bagArrivalDate e flightDate per ogni tappa di volo. Per determinare la durata in giorni, ore o minuti, passare il risultato della funzione timestamp_diff alla funzione get_duration.

Output:

{"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"}

Per determinare la durata in millisecondi, utilizzare la funzione 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

Output:

{"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}

Esempio 6 - Quanto tempo occorre dal momento del check-in al momento della scansione del bagaglio nel punto di imbarco per il passeggero con numero di biglietto 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)

Spiegazione: nei dati del bagaglio, ogni flightLeg dispone di un array di azioni. Nell'array di azioni sono disponibili tre azioni diverse. Il codice azione per il primo elemento dell'array è Check-in/Offload. Per la prima gamba, il codice azione è Check-in e per le altre gambe, il codice azione è Offload all'hop. Il codice azione per il secondo elemento dell'array è BagTag Scan. Nella query precedente, si determina la differenza di tempo di azione tra la scansione dei tag bag e il tempo di check-in. Utilizzare la funzione contains per filtrare il tempo di azione solo se il codice azione è Check-in o BagScan. Dal momento che solo la prima tappa di volo ha i dettagli del check-in e della scansione delle borse, è inoltre possibile filtrare i dati utilizzando la funzione starts_with per recuperare solo il codice sorgente fltRouteSrc. Per determinare la durata in giorni, ore o minuti, passare il risultato della funzione timestamp_diff alla funzione get_duration.

Per determinare la durata in millisecondi, utilizzare la funzione 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)

Output:

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

Esempio 7 - Quanto tempo ci vuole perché i bagagli di un cliente con biglietto n. 1762320369957 raggiungano il primo punto di transito?

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

Spiegazione:in un'applicazione aerea ogni cliente può avere un numero diverso di luppoli/gambe tra l'origine e la destinazione. Nell'esempio precedente, si determina il tempo impiegato per il sacchetto per raggiungere il primo punto di transito. Nei dati del bagaglio, il flightLeg è un array. Il primo record nell'array si riferisce ai dettagli del primo punto di transito. Il valore flightDate nel primo record indica l'ora in cui il sacchetto lascia l'origine e il valore estimatedArrival nel record della prima tappa di volo indica l'ora in cui raggiunge il primo punto di transito. La differenza tra i due dà il tempo necessario perché il sacchetto raggiunga il primo punto di transito. Per determinare la durata in giorni, ore o minuti, passare il risultato della funzione timestamp_diff alla funzione get_duration.

Per determinare la durata in millisecondi, utilizzare la funzione 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

Output:

{"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"}

Funzioni arrotondamento indicatore orario

È possibile utilizzare le funzioni timestamp_ceil, timestamp_floor, timestamp_trunc, timestamp_round e timestamp_bucket per arrotondare i valori dell'indicatore orario.

Per le funzioni timestamp_ceil, timestamp_floor, timestamp_trunc e timestamp_round, è necessario fornire un unit come secondo argomento. unit specifica la precisione da considerare durante l'arrotondamento dell'indicatore orario di input.

Le seguenti unità sono supportate in formato singolare o plurale: YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND.

È possibile utilizzare la funzione timestamp_bucket per arrotondare il valore dell'indicatore orario specificato all'inizio dell'intervallo (bucket) specificato. L'intervallo inizia da un'origine specificata nella sequenza temporale.

timestamp_bucket supporta i seguenti intervalli in formato singolare o plurale: WEEK, DAY, HOUR, MINUTE, SECOND.

Esempio 1: dai dati di tracciamento del bagaglio della compagnia aerea, stampa la data di arrivo del bagaglio e la data dell'asta del bagaglio per un passeggero con numero di biglietto 1762344493810, considerando 90 giorni come periodo di conservazione del bagaglio.

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

Spiegazione: questa query mostra come nidificare le funzioni dell'indicatore orario. Per determinare la data in cui un sacchetto non reclamato viene conservato, aggiungere 90 giorni a bagArrivalDate utilizzando la funzione timestamp_add. La funzione timestamp_ceil arrotonda il valore all'inizio del giorno successivo.

Output:

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

Esempio 2 - Stampa il nome, il numero di volo e la data di viaggio per tutti i passeggeri che si sono imbarcati presso l'aeroporto di partenza JFK nel mese di marzo 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'

Spiegazione: utilizzare la funzione timestamp_floor con il valore unitario MESE per arrotondare le date di viaggio all'inizio del mese. Si confronta quindi il valore dell'indicatore orario risultante con la stringa "2019-03-01" per selezionare i passeggeri desiderati. Questa query non considera i passeggeri in transito.

In questo esempio viene fornita la data in una stringa formattata ISO-8601, che ottiene implicitamente CAST in un valore TIMESTAMP.

Per evitare la duplicazione dei risultati a causa di più sacchetti controllati da un passeggero, si considera solo il primo elemento dell'array bagInfo in questa query.

Output:

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

Esempio 3: dai dati di tracciamento dei bagagli della compagnia aerea, stampare tutte le attività eseguite sui bagagli registrati nella stazione di origine MEL. Allineare le azioni a un intervallo di un minuto.

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"

Spiegazione: in questa query si utilizza la funzione timestamp_round con unità MINUTE per arrotondare actionTime al minuto più vicino.

Per evitare la duplicazione dei risultati a causa di più bagagli registrati da parte di un passeggero, si considera solo il primo elemento dell'array bagInfo in questa query.

Output:

{"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"}

Esempio 4: recuperare le statistiche del numero di passeggeri in partenza dall'aeroporto IST ogni 12 ore con periodi fissi a partire dal 1° gennaio 2019. Considera i dati solo per il mese di febbraio 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

Spiegazione: per considerare i passeggeri che viaggiano a febbraio 2019, utilizzare la funzione timestamp_floor e arrotondare per difetto il valore flightDate all'inizio del mese. Confrontare il risultato con la stringa "2019-02-01T00:00:00Z". In questo esempio viene fornita la data in una stringa formattata ISO-8601, che ottiene implicitamente CAST in un valore TIMESTAMP.

Per includere i voli di transito dall'aeroporto IST, utilizzare il costruttore di array [ ] per indicare che il flightLegs è un array e considerare ogni elemento di array fltRouteSrc nella ricerca.

Utilizzare la funzione timsestamp_bucket nei campi flightDate con intervallo di 12 ore e origine al 1° gennaio 2019.

Output:

{"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}

Funzioni formato indicatore orario

È possibile utilizzare le funzioni format_timestamp e parse_to_timestamp per formattare i valori dell'indicatore orario. Inoltre, è possibile utilizzare la funzione to_last_day_of_month per recuperare l'ultimo giorno del mese da un determinato indicatore orario.

Esempio 1 - Per un passeggero con un numero di biglietto specifico, stampare l'orario di arrivo stimato sulla prima tappa secondo il pattern e il timezone inserito.

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

Spiegazione: in questa query, specificare il campo estimatedArrival, pattern e il nome completo di timezone come argomenti della funzione format_timestamp per convertire la stringa timestamp nel pattern "MMM dd, yyyy HH:mm:ss" specificato.

Nota: la lettera 'O' nell'argomento pattern rappresenta ZoneOffset, che stampa la quantità di tempo diversa da Greenwich/UTC nella stringa risultante.

Output:

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

Esempio 2: analizzare il valore string specificato con il valore pattern specificato, che include un offset di zona, in un indicatore orario.

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

Spiegazione: in questa query, l'argomento string ha un TimeZoneID, GMT+02:00, quindi l'argomento pattern deve includere un simbolo di zona o un ZoneOffset. Quando viene eseguito il wrapping nella funzione format_timestamp, l'indicatore orario di output viene visualizzato nel fuso orario GMT+02:00.

Output:

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

Esempio 3 - Per un sottoscrittore, stampare l'ultimo giorno del mese in cui scade la sottoscrizione all'account.

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

Output:

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

Funzioni di estrazione indicatore orario

Le funzioni di estrazione dell'indicatore orario recuperano la data, la settimana o il valore di indice corrispondente da un determinato indicatore orario.

Le funzioni di estrazione data restituiscono l'anno/mese/giorno/ora/minuto/secondo/millisecondo/microsecondo/nanosecondo corrispondente da un indicatore orario.

Esempio 1 - Ottieni dettagli di viaggio consolidati dei passeggeri dai dati di tracciamento del bagaglio della compagnia aerea.

In un'applicazione aerea, è vantaggioso per i passeggeri avere un breve riepilogo dei loro prossimi dettagli di viaggio. È possibile utilizzare funzioni temporali varie per ottenere dettagli di viaggio consolidati dei passeggeri dalla tabella 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

Spiegazione:

È possibile utilizzare le funzioni tempo per recuperare la data, il mese e l'anno del viaggio. La funzione stringa concat consente di concatenare i record viaggio recuperati per visualizzarli nel formato desiderato nell'applicazione. Utilizzare prima l'espressione CAST per convertire flightDates in TIMESTAMP, quindi recuperare i dettagli di data, mese e anno dall'indicatore orario.

Output:

{"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 query restituisce i dettagli del volo che possono servire come ricerca rapida per i passeggeri.

Le funzioni di estrazione della settimana restituiscono la settimana o l'isoweek corrispondente da un indicatore orario.

Esempio 2 - Determinare la settimana e il numero della settimana ISO dalla data di viaggio di un passeggero.

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

Spiegazione: utilizzare prima l'espressione CAST per convertire flightDate in TIMESTAMP, quindi recuperare la settimana e isoweek dall'indicatore orario.

Output:

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

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

Le funzioni di estrazione dell'indice indicatore orario restituiscono l'indice trimestre/settimana/mese/anno corrispondente da un indicatore orario.

Esempio 3 - Trova il giorno della settimana per gli indicatori orari specificati.

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

Spiegazione: il secondo indicatore orario della query è in un formato non supportato '06/19/24' da solo, quindi racchiuderlo nella funzione parse_to_timestamp per renderlo valido.

Output:

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

Funzioni ora corrente

È possibile utilizzare le funzioni current_time_millis e current_time per recuperare l'ora corrente. La funzione current_time_millis restituisce il tempo come numero di millisecondi. La funzione current_time restituisce l'ora come valore dell'indicatore orario.

Esempio 1 - Determinare l'intervallo di tempo tra la data dell'ultimo viaggio di un passeggero e la data corrente.

In un'applicazione aerea, alcuni clienti viaggiano molto frequentemente e hanno diritto a premi frequenti per miglia volanti. È possibile determinare l'intervallo di tempo tra la data dell'ultimo viaggio di un passeggero e la data corrente per valutare se possono essere considerati per tale programma di ricompensa.

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

Spiegazione:

È possibile utilizzare la funzione current_time per ottenere l'ora corrente. Per determinare l'intervallo di tempo tra la data dell'ultimo viaggio e la data corrente, è possibile fornire l'ora corrente alla funzione get_duration/timestamp_diff insieme all'ora dell'ultimo viaggio. Per maggiori dettagli sulle funzioni timestamp_diff e get_duration.

Output:

{"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 funzione current_time consente di calcolare l'ora corrente. Utilizzare la funzione timestamp_diff per calcolare la differenza di tempo tra l'ora corrente e la data dell'ultimo volo. Utilizzare prima l'espressione CAST per convertire flightDates in TIMESTAMP, quindi recuperare i dettagli del giorno, del mese e dell'anno dall'indicatore orario. Poiché la funzione timestamp_diff restituisce il numero di millisecondi tra due valori di indicatore orario, è possibile utilizzare la funzione get_duration per convertire i millisecondi in una stringa di durata.

La funzione get_duration converte i millisecondi in giorni, ore, minuti, secondi e millisecondi in base al valore restituito. Ai fini del calcolo vengono prese in considerazione le seguenti conversioni:

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

Ad esempio: se la funzione timestamp_diff restituisce il valore 129084684821 millisecondi, la funzione get_duration la converte in modo corrispondente a 1494 giorni e 52 minuti e 4 secondi e 687 millisecondi.

Esempi che utilizzano l'API QueryRequest

È possibile utilizzare l'API QueryRequest e applicare le funzioni SQL per recuperare i dati da una tabella NoSQL.

Per eseguire la query, utilizzare l'API NoSQLHandle.query().

Scarica il codice completo SQLFunctions.java dagli esempi qui.

 //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);

Per eseguire la query, utilizzare il metodo borneo.NoSQLHandle.query().

Scarica il codice completo SQLFunctions.py dagli esempi qui.

# 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)

Per eseguire una query, utilizzare la funzione Client.Query.

Scarica il codice completo SQLFunctions.go dagli esempi qui.

 //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)

Per eseguire una query, utilizzare il metodo query.

JavaScript: scarica il codice completo SQLFunctions.js dagli esempi qui.

  //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: scarica il codice completo SQLFunctions.ts dagli esempi qui.

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);

Per eseguire una query, è possibile chiamare il metodo QueryAsync o chiamare il metodo GetQueryAsyncEnumerable e ripetere l'enumerazione asincrona risultante.

Scarica il codice completo SQLFunctions.cs dagli esempi qui.

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);

Argomenti correlati