Zeitstempelfunktionen in Abfragen verwenden

Sie können verschiedene Vorgänge für den Zeitstempel und die Dauer ausführen.

Sie können eine Dauer zu einem Zeitstempel hinzufügen, die Differenz zwischen zwei Zeitstempeln suchen und einen Zeitstempel zu einer bestimmten Einheit runden. Sie können einen Zeitstempel in eine/von Zeichenfolge mit benutzerdefinierten Mustern umwandeln. Einige der Funktionen unterstützen die Extraktion des Datumsteils eines Zeitstempels. Sie können diese Funktionen auch verwenden, um die aktuelle Uhrzeit anzuzeigen.

Die folgenden Zeitstempelfunktionen werden unterstützt:

Tabelle 1 - Zeitstempelfunktionen

Funktion Beschreibung
timestamp_add Fügt einem Zeitstempelwert eine Dauer hinzu.
timestamp_diff Gibt die Anzahl der Millisekunden zwischen zwei Zeitstempelwerten zurück.
get_dauer Konvertiert die angegebene Anzahl von Millisekunden in eine Dauerzeichenfolge.
Zeitstempel Runden Sie den Zeitstempelwert auf die angegebene Einheit auf.
timestamp_floor/timestamp_trunc Runden Sie den Zeitstempelwert auf die angegebene Einheit ab.
Zeitstempelrunde Rundet den Zeitstempelwert auf die angegebene Einheit.
timestamp_bucket Rundet den Zeitstempelwert auf den Anfang des angegebenen Intervalls ab einem angegebenen Ursprungswert.
format_zeitstempel Konvertiert einen Zeitstempel gemäß dem angegebenen Muster und der Zeitzone in eine Zeichenfolge.
parse-to-Zeitstempel Konvertiert eine Zeichenfolge im angegebenen Muster in einen Zeitstempelwert.
to_last_day_of_month Gibt den letzten Tag des Monats aus einem bestimmten Zeitstempel zurück.
Extraktionsfunktionen für Zeitstempel

Extrahiert den entsprechenden Datumsbereich eines bestimmten Zeitstempels. Die folgenden Funktionen werden unterstützt:

  • Jahr
  • Monat
  • Tag
  • Stunde
  • Minute
  • Sekunde
  • Millisekunde
  • Mikrosekunde
  • Nanosekunde

Gibt die Wochennummer innerhalb des Jahres zurück. Die folgenden Funktionen werden unterstützt:

  • Woche
  • Isoweek

Gibt den entsprechenden Index aus einem bestimmten Zeitstempel zurück. Die folgenden Funktionen werden unterstützt:

  • quarter
  • day_of_week
  • day_of_month
  • day_of_year
aktuell_zeit_millis Gibt die aktuelle Zeit als Anzahl von Millisekunden zurück.
current_time Gibt die aktuelle Zeit als Zeitstempelwert zurück.

Wenn Sie zusammen mit den Beispielen folgen möchten, lesen Sie Beispieldaten zum Ausführen von Abfragen, um Beispieldaten anzuzeigen und die Skripte zum Laden von Beispieldaten zum Testen zu verwenden. Die Skripte erstellen die in den Beispielen verwendeten Tabellen und laden Daten in die Tabellen.

Wenn Sie zusammen mit den Beispielen folgen möchten, lesen Sie Beispieldaten zum Ausführen von Abfragen, um Beispieldaten anzuzeigen. Außerdem erfahren Sie, wie Sie mit der OCI-Konsole die Beispieltabellen erstellen und Daten mit JSON-Dateien laden.

Arithmetische Funktionen für Zeitstempel

Mit den Funktionen timestamp_add, timestamp_diff oder get_duration können Sie arithmetische Vorgänge für die Zeitstempel- und Dauerwerte ausführen.

Beispiel 1 - In der Airline-Anwendung wird ein Puffer von fünf Minuten Verzögerung als "pünktlich" betrachtet. Drucken Sie die voraussichtliche Ankunftszeit auf der ersten Etappe mit einem Puffer von fünf Minuten für den Passagier mit der Ticketnummer 1762399766476 aus.

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

Erläuterung: In der Airline-Anwendung kann ein Kunde je nach Quelle und Ziel beliebig viele Flugstrecken haben. In der obigen Abfrage holen Sie die geschätzte Ankunft im "ersten Bein" der Reise ab. Daher wird der erste Datensatz des Arrays flightsLeg abgerufen, und die Zeit estimatedArrival wird aus dem Array abgerufen, und ein Puffer von "5 Minuten" wird hinzugefügt und angezeigt.

Ausgabe:

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

Hinweis:

Die Spalte estimatedArrival ist ein STRING. Wenn die Spalte STRING-Werte im ISO-8601-Format aufweist, wird sie automatisch von der SQL-Laufzeit in den TIMESTAMP-Datentyp konvertiert.

ISO8601 beschreibt eine international akzeptierte Art, Daten, Zeiten und Dauer darzustellen.

Syntax: Datum mit Uhrzeit: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

Dabei gilt:

Beispiel 2: Drucken Sie die geschätzte Ankunftszeit in jeder Etappe mit einem Puffer von fünf Minuten für den Passagier mit der Ticketnummer 1762399766476 aus.

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

Erläuterung: Sie möchten die estimatedArrival-Zeit auf jeder Teilstrecke anzeigen. Die Anzahl der Beine kann für jeden Kunden unterschiedlich sein. Daher wird die Variablenreferenz in der obigen Abfrage verwendet, und das Array baggageInfo und das Array flightLegs werden für die Ausführung der Abfrage entschachtelt.

Ausgabe:

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

Beispiel 3 - Wie viele Taschen sind in der letzten Woche angekommen?

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

Erläuterung: Sie erhalten die Anzahl der von der Airline-Anwendung in der letzten Woche verarbeiteten Taschen. Ein Kunde kann mehrere Mischentitys haben (das Array bagInfo kann mehrere Datensätze enthalten). Der Wert für bagArrivalDate muss zwischen dem heutigen Tag und den letzten 7 Tagen liegen. Für jeden Datensatz im Array bagInfo legen Sie fest, ob die Ankunftszeit der Tasche zwischen der aktuellen Zeit und einer Woche liegt. Die Funktion current_time gibt Ihnen jetzt die Zeit. Eine EXISTS-Bedingung wird als Filter verwendet, um zu bestimmen, ob der Beutel ein Ankunftsdatum in der letzten Woche hat. Die Funktion count bestimmt die Gesamtanzahl der Beutel in diesem Zeitraum.

Ausgabe:

{"COUNT_LASTWEEK":0}

Beispiel 4 - Finden Sie die Anzahl der Taschen, die in den nächsten 6 Stunden ankommen.

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

Erläuterung: Sie erhalten die Anzahl der Taschen, die in den nächsten 6 Stunden von der Airline-Anwendung verarbeitet werden. Ein Kunde kann mehr als einen Beutel haben (das Array bagInfo kann mehr als einen Datensatz enthalten). Die bagArrivalDate muss zwischen der aktuellen Zeit und den nächsten 6 Stunden liegen. Für jeden Datensatz im Array bagInfo legen Sie fest, ob die Ankunftszeit der Tasche zwischen der aktuellen Zeit und sechs Stunden später liegt. Die Funktion current_time gibt Ihnen jetzt die Zeit. Eine EXISTS-Bedingung wird als Filter verwendet, um festzustellen, ob der Beutel in den nächsten sechs Stunden ein Ankunftsdatum hat. Die Funktion count bestimmt die Gesamtanzahl der Beutel in diesem Zeitraum.

Ausgabe:

{"COUNT_NEXT6HOURS":0}

Beispiel 5 - Wie lange dauerte es, bis das Gepäck an einem Bein an Bord war und das nächste Bein für den Passagier mit der Ticketnummer 1762355527825 erreichte?

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

Erläuterung:In einer Airline-Anwendung kann jeder Kunde eine andere Anzahl von Hops/Beinen zwischen Quelle und Ziel haben. In dieser Abfrage bestimmen Sie die Zeit zwischen jedem Flugbein. Dies wird durch die Differenz zwischen bagArrivalDate und flightDate für jede Flugstrecke bestimmt. Um die Dauer in Tagen, Stunden oder Minuten zu bestimmen, übergeben Sie das Ergebnis der Funktion timestamp_diff an die Funktion get_duration.

Ausgabe:

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

Um die Dauer in Millisekunden zu bestimmen, verwenden Sie die Funktion 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

Ausgabe:

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

Beispiel 6 - Wie lange dauert es vom Check-in bis zum Scannen des Beutels beim Einsteigen für den Passagier mit der Ticketnummer 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)

Erläuterung:In den Gepäckdaten enthält jede flightLeg ein Aktionsarray. Es gibt drei verschiedene Aktionen im Aktionsarray. Der Aktionscode für das erste Element im Array lautet "Einchecken/Ausladen". Bei der ersten Etappe ist der Aktionscode "Einchecken" und bei den anderen Beinen der Aktionscode "Ausladen am Hop". Der Aktionscode für das zweite Element des Arrays ist BagTag Scan. In der obigen Abfrage bestimmen Sie den Unterschied in der Aktionszeit zwischen dem Bag-Tag-Scan und der Eincheckzeit. Mit der Funktion contains können Sie die Aktionszeit nur filtern, wenn der Aktionscode "Einchecken" oder "BagScan" lautet. Da nur die erste Flugstrecke Details zum Check-in und Bag Scan enthält, filtern Sie die Daten zusätzlich mit der Funktion starts_with, um nur den Quellcode fltRouteSrc abzurufen. Um die Dauer in Tagen, Stunden oder Minuten zu bestimmen, übergeben Sie das Ergebnis der Funktion timestamp_diff an die Funktion get_duration.

Um die Dauer in Millisekunden zu bestimmen, verwenden Sie die Funktion 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)

Ausgabe:

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

Beispiel 7: Wie lange dauert es, bis die Gepäckstücke eines Kunden mit der Ticketnummer 1762320369957 den ersten Transitpunkt erreichen?

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

Erläuterung:In einer Airline-Anwendung kann jeder Kunde eine andere Anzahl von Hops/Beinen zwischen Quelle und Ziel haben. Im obigen Beispiel legen Sie fest, wie lange die Tasche bis zum ersten Transitpunkt benötigt wird. In den Gepäckdaten ist die flightLeg ein Array. Der erste Datensatz im Array bezieht sich auf die ersten Transitpunktdetails. Die flightDate im ersten Datensatz ist die Zeit, zu der die Tasche die Quelle verlässt, und die estimatedArrival im ersten Flugstreckendatensatz gibt an, wann sie den ersten Transitpunkt erreicht. Der Unterschied zwischen den beiden gibt die Zeit an, die benötigt wird, damit der Beutel den ersten Transitpunkt erreicht. Um die Dauer in Tagen, Stunden oder Minuten zu bestimmen, übergeben Sie das Ergebnis der Funktion timestamp_diff an die Funktion get_duration.

Um die Dauer in Millisekunden zu bestimmen, verwenden Sie die Funktion 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

Ausgabe:

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

Rundungsfunktionen für Zeitstempel

Mit den Funktionen timestamp_ceil, timestamp_floor, timestamp_trunc, timestamp_round und timestamp_bucket können Sie die Zeitstempelwerte runden.

Für die Funktionen timestamp_ceil, timestamp_floor, timestamp_trunc und timestamp_round müssen Sie ein unit als zweites Argument angeben. unit gibt die Gesamtstellenzahl an, die beim Runden des Eingabezeitstempels berücksichtigt werden soll.

Die folgenden Einheiten werden im Singular- oder Pluralformat unterstützt: YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND.

Mit der Funktion timestamp_bucket können Sie den angegebenen Zeitstempelwert auf den Anfang des angegebenen Intervalls (Bucket) runden. Das Intervall beginnt an einem angegebenen Ursprung auf der Zeitleiste.

Die timestamp_bucket unterstützt die folgenden Intervalle im Singular- oder Pluralformat: WEEK, DAY, HOUR, MINUTE, SECOND.

Beispiel 1 - Drucken Sie aus den Gepäckverfolgungsdaten der Fluggesellschaft das Ankunftsdatum des Gepäcks und das Auktionsdatum des Gepäcks für einen Passagier mit der Ticketnummer 176234493810 aus, wobei 90 Tage als Aufbewahrungszeitraum für das Gepäck berücksichtigt werden.

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

Erläuterung: Diese Abfrage zeigt, wie die Zeitstempelfunktionen verschachtelt werden. Um das Datum zu bestimmen, an dem ein nicht beanspruchter Beutel aufbewahrt wird, fügen Sie der bagArrivalDate mit der Funktion timestamp_add 90 Tage hinzu. Die Funktion timestamp_ceil rundet den Wert auf den Anfang des nächsten Tages auf.

Ausgabe:

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

Beispiel 2 - Geben Sie den Namen, die Flugnummer und das Reisedatum für alle Passagiere aus, die im Monat März 2019 am Ursprungsflughafen JFK an Bord waren.

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'

Erläuterung: Mit der Funktion timestamp_floor mit dem Einheitenwert MONAT runden Sie die Reisedaten auf den Monatsanfang ab. Anschließend vergleichen Sie den resultierenden Zeitstempelwert mit der Zeichenfolge "2019-03-01", um die gewünschten Passagiere auszuwählen. Bei dieser Abfrage werden die Passagiere im Transit nicht berücksichtigt.

In diesem Beispiel wird das Datum in einer formatierten Zeichenfolge mit dem Format ISO-8601 angegeben, die implizit CAST in einen TIMESTAMP-Wert abgibt.

Um die Duplizierung von Ergebnissen aufgrund mehrerer geprüfter Gepäckstücke durch einen Passagier zu vermeiden, berücksichtigen Sie in dieser Abfrage nur das erste Element des bagInfo-Arrays.

Ausgabe:

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

Beispiel 3 - Drucken Sie aus den Gepäckverfolgungsdaten der Fluggesellschaft alle Aktivitäten aus, die an den aufgegebenen Taschen in der Ursprungsstation MEL durchgeführt wurden. Richten Sie die Aktionen an einem Minutenintervall aus.

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"

Erläuterung: In dieser Abfrage verwenden Sie die Funktion timestamp_round mit der Einheit MINUTE, um die actionTime auf die nächste MINUTE zu runden.

Um die Duplizierung von Ergebnissen aufgrund mehrerer aufgegebener Gepäckstücke durch einen Passagier zu vermeiden, berücksichtigen Sie in dieser Abfrage nur das erste Element des Arrays bagInfo.

Ausgabe:

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

Beispiel 4 - Rufen Sie ab dem 1. Januar 2019 die Statistiken zur Anzahl der Passagiere ab, die alle 12 Stunden mit Eimern vom IST-Flughafen abfliegen. Berücksichtigen Sie Daten nur für den Monat Februar 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

Erläuterung: Um Passagiere zu berücksichtigen, die im Februar 2019 reisen, verwenden Sie die Funktion timestamp_floor, und runden Sie die Funktion flightDate auf den Monatsanfang ab. Vergleichen Sie das Ergebnis mit der Zeichenfolge "2019-02-01T00:00:00Z". In diesem Beispiel wird das Datum in einer formatierten Zeichenfolge mit dem Format ISO-8601 angegeben, die implizit CAST in einen TIMESTAMP-Wert abgibt.

Um die Transitflüge vom IST-Flughafen einzuschließen, geben Sie mit dem Arraykonstruktor [ ] an, dass flightLegs ein Array IST, und berücksichtigen Sie jedes fltRouteSrc-Arrayelement in der Suche.

Verwenden Sie die Funktion timsestamp_bucket für die Felder flightDate mit einem Intervall von 12 Stunden und dem Ursprung vom 1. Januar 2019.

Ausgabe:

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

Zeitstempelformatfunktionen

Sie können die Funktionen format_timestamp und parse_to_timestamp verwenden, um Zeitstempelwerte zu formatieren. Außerdem können Sie mit der Funktion to_last_day_of_month den letzten Tag des Monats aus einem bestimmten Zeitstempel abrufen.

Beispiel 1 - Drucken Sie für einen Passagier mit einer bestimmten Ticketnummer die voraussichtliche Ankunftszeit auf der ersten Etappe gemäß der eingegebenen pattern und timezone aus.

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

Erläuterung: In dieser Abfrage geben Sie das Feld estimatedArrival, pattern und den vollständigen Namen der timezone als Argumente für die Funktion format_timestamp an, um die Zeichenfolge timestamp in das angegebene Muster "MMM dd, yyyy HH:mm:ss" zu konvertieren.

Hinweis: Der Buchstabe "O" im Argument pattern stellt den ZoneOffset dar, der die Zeitspanne druckt, die sich von Greenwich/UTC in der resultierenden Zeichenfolge unterscheidet.

Ausgabe:

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

Beispiel 2: Parsen Sie das angegebene string mit dem angegebenen pattern, das einen Zonen-Offset enthält, in einen Zeitstempel.

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

Erläuterung: In dieser Abfrage hat das Argument string eine TimeZoneID, GMT+02:00. Daher muss das Argument pattern ein Zonensymbol oder einen ZoneOffset enthalten. Wenn er in die Funktion format_timestamp eingewickelt wird, wird der Ausgabezeitstempel in der Zeitzone GMT+02:00 angezeigt.

Ausgabe:

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

Beispiel 3 - Drucken Sie für einen Abonnenten den letzten Tag des Monats aus, in dem das Kontoabonnement abläuft.

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

Ausgabe:

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

Zeitstemplextraktion - Funktionen

Extraktionsfunktionen für Zeitstempel rufen das entsprechende Datum, die entsprechende Woche oder den Indexwert aus einem bestimmten Zeitstempel ab.

Datumsextraktionsfunktionen geben das entsprechende Jahr/Monat/Tag/Stunde/Minute/Sekunde/Millisekunde/Mikrosekunde/Nanosekunde aus einem Zeitstempel zurück.

Beispiel 1 - Erhalten Sie konsolidierte Reisedetails der Passagiere aus den Gepäckverfolgungsdaten der Fluggesellschaft.

In einer Airline-Anwendung ist es für die Passagiere von Vorteil, eine kurze Zusammenfassung ihrer bevorstehenden Reisedaten zu haben. Sie können verschiedene Zeitfunktionen verwenden, um konsolidierte Reisedetails der Passagiere aus der Tabelle BaggageInfo abzurufen.

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

Erklärung:

Mit den Zeitfunktionen können Sie das Reisedatum, den Monat und das Jahr abrufen. Mit der Zeichenfolgenfunktion concat werden die abgerufenen Reisedatensätze verkettet, um sie im gewünschten Format in der Anwendung anzuzeigen. Sie verwenden zuerst den CAST-Ausdruck, um die flightDates in einen TIMESTAMP zu konvertieren und dann die Datums-, Monats- und Jahresdetails aus dem Zeitstempel abzurufen.

Ausgabe:

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

Die Abfrage gibt die Flugdetails zurück, die als Schnellsuche für die Passagiere dienen können.

Wochenextraktionsfunktionen geben die entsprechende Woche/Iswoche aus einem Zeitstempel zurück.

Beispiel 2 - Bestimmen Sie die Woche und die ISO-Wochennummer vom Reisedatum eines Passagiers.

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

Erläuterung: Sie verwenden zuerst den CAST-Ausdruck, um die flightDate in einen TIMESTAMP zu konvertieren und dann die Woche und die isoweek aus dem Zeitstempel abzurufen.

Ausgabe:

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

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

Die Funktionen zum Extrahieren von Zeitstempeln geben den entsprechenden Index für Quartal/Woche/Monat/Jahr aus einem Zeitstempel zurück.

Beispiel 3 - Suchen Sie den Wochentag für die angegebenen Zeitstempel.

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

Erläuterung: Der zweite Zeitstempel in der Abfrage hat das nicht unterstützte Format "06/19/24" für sich selbst. Um die Gültigkeit zu gewährleisten, wrappen Sie ihn in die Funktion parse_to_timestamp.

Ausgabe:

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

Aktuelle Zeit - Funktionen

Sie können die Funktionen current_time_millis und current_time verwenden, um die aktuelle Uhrzeit abzurufen. Die Funktion current_time_millis gibt die Zeit als Anzahl von Millisekunden zurück. Die Funktion current_time gibt die Zeit als Zeitstempelwert zurück.

Beispiel 1 - Bestimmen Sie die Zeitspanne zwischen dem letzten Reisedatum eines Passagiers und dem aktuellen Datum.

In einer Airline-Anwendung reisen einige Kunden sehr häufig und haben Anspruch auf Vielfliegermeilenbelohnungen. Sie können die Zeitspanne zwischen dem letzten Reisedatum eines Passagiers und dem aktuellen Datum bestimmen, um zu beurteilen, ob er für ein solches Prämienprogramm berücksichtigt werden kann.

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

Erklärung:

Mit der Funktion current_time können Sie die aktuelle Zeit abrufen. Um die Zeitspanne zwischen dem letzten Reisedatum und dem aktuellen Datum zu bestimmen, können Sie der Funktion get_duration/timestamp_diff zusammen mit der letzten Reisedauer die aktuelle Zeit angeben. Weitere Details zu den Funktionen timestamp_diff und get_duration.

Ausgabe:

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

Sie verwenden die Funktion current_time, um die aktuelle Zeit zu berechnen. Verwenden Sie die Funktion timestamp_diff, um die Zeitdifferenz zwischen der aktuellen Zeit und dem letzten Flugdatum zu berechnen. Sie verwenden zuerst den CAST-Ausdruck, um die flightDates in einen TIMESTAMP zu konvertieren und dann die Details für Tag, Monat und Jahr aus dem Zeitstempel abzurufen. Da die Funktion timestamp_diff die Anzahl der Millisekunden zwischen zwei Zeitstempelwerten zurückgibt, verwenden Sie die Funktion get_duration, um die Millisekunden in eine Dauerzeichenfolge zu konvertieren.

Die Funktion get_duration konvertiert die Millisekunden basierend auf dem Rückgabewert in Tage, Stunden, Minuten, Sekunden und Millisekunden. Für Berechnungszwecke werden folgende Umrechnungen berücksichtigt:

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

Beispiel: Wenn die Funktion timestamp_diff den Wert 129084684821 Millisekunden zurückgibt, konvertiert sie die Funktion get_duration entsprechend in 1494 Tage 52 Minuten 4 Sekunden 687 Millisekunden.

Beispiele für die Verwendung der QueryRequest-API

Sie können die QueryRequest-API verwenden und SQL-Funktionen anwenden, um Daten aus einer NoSQL-Tabelle abzurufen.

Zum Ausführen der Abfrage verwenden Sie die NoSQLHandle.query()-API.

Laden Sie den vollständigen Code SQLFunctions.java aus den Beispielen hier herunter.

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

Um die Abfrage auszuführen, verwenden Sie die Methode borneo.NoSQLHandle.query().

Laden Sie den vollständigen Code SQLFunctions.py aus den Beispielen hier herunter.

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

Um eine Abfrage auszuführen, verwenden Sie die Funktion Client.Query.

Laden Sie den vollständigen Code SQLFunctions.go aus den Beispielen hier herunter.

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

Um eine Abfrage auszuführen, verwenden Sie die Methode query.

JavaScript: Laden Sie den vollständigen Code SQLFunctions.js aus den Beispielen hier herunter.

  //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: Laden Sie den vollständigen Code SQLFunctions.ts aus den Beispielen hier herunter.

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

Um eine Abfrage auszuführen, können Sie die Methode QueryAsync aufrufen oder die Methode GetQueryAsyncEnumerable aufrufen und über die resultierende asynchrone aufzählbare Methode iterieren.

Laden Sie den vollständigen Code SQLFunctions.cs aus den Beispielen hier herunter.

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

Verwandte Themen