問合せでのタイムスタンプ関数の使用

タイムスタンプおよび期間値に対して様々な操作を実行できます。

タイムスタンプに期間を追加し、2つのタイムスタンプの差分を見つけて、指定した単位にタイムスタンプを丸めることができます。カスタマイズされたパターンを使用して、文字列との間でタイムスタンプをキャストできます。一部の関数では、タイムスタンプの日付部分の抽出がサポートされます。これらの関数を使用して、現在の時間を表示することもできます。

次のタイムスタンプ関数がサポートされています。

表1 - タイムスタンプ関数

機能	説明
timestamp_add	タイムスタンプ値に期間を追加します。
timestamp_diff	2つのタイムスタンプ値の間のミリ秒数を返します。
取得期間	指定されたミリ秒数を期間文字列に変換します。
タイムスタンプ・セイル	タイムスタンプ値を、指定した単位に切り上げます。
timestamp_floor/timestamp_trunc	タイムスタンプ値を、指定した単位に切り捨てます。
タイムスタンプ・ラウンド	タイムスタンプ値を、指定された単位に丸めます。
timestamp_bucket	タイムスタンプ値を、指定された起点値から開始して、指定された間隔の先頭まで丸めます。
format_timestamp	指定されたパターンおよびタイムゾーンに従って、タイムスタンプを文字列に変換します。
解析からタイムスタンプへ	指定されたパターンの文字列をタイムスタンプ値に変換します。
月の末日	指定されたタイムスタンプから月の最終日を返します。
タイムスタンプ抽出関数	指定されたタイムスタンプの対応する日付部分を抽出します。次のファンクションがサポートされています。年月日時間分秒ミリ秒マイクロ秒ナノ秒年内の週番号を返します。次のファンクションがサポートされています。週アイソウィーク指定されたタイムスタンプから対応するインデックスを返します。次のファンクションがサポートされています。四半期曜日 day_of_month 通算日数
現在の時刻ミリ	現在の時間をミリ秒数で返します。
現在時刻	現在の時間をタイムスタンプ値として返します。

例に従う場合は、「問合せを実行するサンプル・データ」を参照してサンプル・データを表示し、スクリプトを使用してテスト用のサンプル・データをロードします。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。

例に従う場合は、問合せを実行するサンプル・データを参照してサンプル・データを表示し、OCIコンソールを使用してサンプル表を作成し、JSONファイルを使用してデータをロードする方法を学習します。

タイムスタンプ算術関数

timestamp_add、timestamp_diffまたはget_durationファンクションを使用して、タイムスタンプおよび期間の値に対して算術演算を実行できます。

例1 - 航空会社アプリケーションにおいて、5分の遅延のバッファを定時とみなします。チケット番号が1762399766476の乗客に関して、5分の間のバッファを含んで最初の区間の推定到着時間を出力します。

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

説明:航空会社アプリケーションでは、発送元と着信先に応じて顧客にフライト区間にいくつあげてもかまいません。前述の問合せでは、移動の第1区間での推定到着をフェッチしています。そのため、flightsLeg配列の最初のレコードがフェッチされ、その配列からestimatedArrival時間がフェッチされ、その時間のバッファに5分のバッファが追加されて表示されます。

出力:

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

ノート:

列estimatedArrivalはSTRINGです。STRING値がISO-8601形式で含まれている場合は、SQLランタイムによってTIMESTAMデータ型に自動的に変換されます。

ISO8601は、日付、時刻および継続時間を表すために国際的に受け入れられている方法について説明しています。

構文:日付と時刻: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

説明:

YYYYは、年を4桁の小数桁数で指定します。
MMは、月を2桁の10進数(00から12)で指定します。
DDは、日を2桁の10進数(00から31)で指定します。
hhは、時間を2桁の10進数(00から23)で指定します。
mmは、分を2桁の10進数(00から59)で指定します。
ss[.s[s[s[s[s]]]]]は秒を2桁の10進数を00から59で指定します。オプションで、小数点を1から6桁の10進数を1から6桁の10進数をそれぞれ指定します。
Zは、UTC時間またはタイム・ゾーン0を指定します。UTC時間は、+00:00を使用して指定することもできますが、-00:00を使用して指定することはできません。
(+|-)hh:mmは、タイムゾーンをUTCとの差として指定します。+または-のいずれか1つを指定する必要があります。

例2 - チケット番号が1762399766476の乗客に関して、5分の間のバッファを含んで全区間の推定到着時間を出力します

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

説明:各区間での estimatedArrival時間を表示します。区間の数は顧客ごとに異なる可能性があります。したがって、前述の問合せでは変数参照が使用され、baggageInfo配列およびflightLegs配列はネストされずに問合せが実行されます。

出力:

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

例3 - 先週到着した荷物の数はいくらですか。

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

説明:先週に航空会社アプリケーションによって処理された荷物の数を把握します。顧客は、複数の手荷物を所持できます(つまり、bagInfo配列には複数のレコードが含まれる場合があります)。bagArrivalDateには、本日から過去7日間までの値が含まれます。bagInfo配列の各レコードについて、手荷物到着時刻が現在から1週間前までの間であるかどうかを判断しますcurrent_time関数により、現在時間が返されます。EXISTS条件は、手荷物が先週到着日であるかどうかを判断するためのフィルタとして使用されます。count関数は、この期間の手荷物の合計数を決定します。

出力:

{"COUNT_LASTWEEK":0}

例4 - 次の6時間以内に到着する手荷物の数を確認します。

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

説明:今後6時間に航空会社アプリケーションによって処理される荷物の数を把握します。顧客は、複数の手荷物を所持できます(つまり、bagInfo配列には複数のレコードが含まれる場合があります)。bagArrivalDateは、現在時間から次の6時間までである必要があります。bagInfo配列の各レコードについて、手荷物到着時刻が現在時刻と6時間後までの間であるかどうかを判断しますcurrent_time関数により、現在時間が返されます。EXISTS条件は、手荷物に今後6時間以内の到着日があるかどうかを判断するためのフィルタとして使用されます。count関数は、この期間の手荷物の合計数を決定します。

出力:

{"COUNT_NEXT6HOURS":0}

例5 - チケット番号が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

説明:航空会社アプリケーションでは、顧客ごとに、発信元と着信先の間のホップ数または区間の数が異なる可能性があります。この問合せでは、各飛行区間の所要時間を特定します。これは、各飛行区間のbagArrivalDateとflightDateの間の差異によって決まります。日数、時間数または分数で期間を確認するには、timestamp_diff関数の結果をget_duration関数に渡します。

出力:

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

期間(ミリ秒数)を特定するには、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

出力:

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

例6 - チェックインの時点から、チケット番号が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)

説明:手荷物データでは、それぞれのflightLegに1つのアクション配列があります。action配列には3つの異なるアクションがあります。配列の最初の要素のアクション・コードは、Checkin/Offloadです。最初の区間ではアクション・コードがCheckinとなり、他の区間ではアクション・コードが中継点でのOffloadとなります。配列の2番目の要素のアクション・コードは、BagTag Scanです。前述の問合せでは、バッグ・タグ・スキャンとチェックイン時間の間のアクション時間の差異を確認します。アクション・コードがCheckinまたはBagScanの場合のみ、contains関数を使用してアクション時間をフィルタ処理します。最初の飛行区間にのみチェックインおよび手荷物スキャンの詳細が含まれるため、starts_with関数を使用してさらにデータをフィルタ処理し、ソース・コードfltRouteSrcのみをフェッチします。日数、時間数または分数で期間を確認するには、timestamp_diff関数の結果をget_duration関数に渡します。

期間(ミリ秒数)を特定するには、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)

出力:

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

例7 - チケット番号が1762320369957の顧客の荷物が最初のトランジット・ポイントに到達するまでにかかる時間はどのくらいですか。

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

説明:航空会社アプリケーションでは、顧客ごとに、発信元と着信先の間のホップ数または区間の数が異なる可能性があります。前述の例では、荷物が最初のトランジット・ポイントに到着するまでの所要時間を決定します。手荷物データでは、flightLegは配列です。配列内の最初のレコードは、最初のトランジット・ポイントの詳細を示します。最初のレコードのflightDateは荷物が発信元を出る時間であり、最初のフライト区間レコード内のestimatedArrivalは最初のトランジット・ポイントに到着する時間を示します。2つの間の差異は、手荷物が最初のトランジット・ポイントに到着するまでの所要時間を示します。日数、時間数または分数で期間を確認するには、timestamp_diff関数の結果をget_duration関数に渡します。

期間(ミリ秒数)を特定するには、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

出力:

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

タイムスタンプ丸め関数

timestamp_ceil、timestamp_floor、timestamp_trunc、timestamp_roundおよびtimestamp_bucketファンクションを使用して、タイムスタンプ値を丸めることができます。

timestamp_ceil関数、timestamp_floor関数、timestamp_trunc関数およびtimestamp_round関数の場合は、2番目の引数としてunitを指定する必要があります。unitは、入力タイムスタンプの端数処理中に考慮される精度を指定します。

次の単位は、単数形式または複数形式のいずれかでサポートされます: YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND。

timestamp_bucketファンクションを使用すると、指定したタイムスタンプ値を、指定した間隔(バケット)の先頭まで丸めることができます。間隔は、タイムライン上の指定された起点から始まります。

timestamp_bucketでは、単数形式または複数形式の間隔WEEK, DAY, HOUR, MINUTE, SECONDがサポートされます。

例1 - 航空会社の手荷物追跡データから、手荷物保持期間として90日を考慮して、チケット番号が1762344493810の乗客の手荷物到着日と手荷物オークション日を印刷します。

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

説明: このクエリーは、タイムスタンプ関数をネストする方法を示しています。未請求のバッグが保持される日付を判別するには、timestamp_add関数を使用してbagArrivalDateに90日を追加します。timestamp_ceil関数は、値を次の日の開始に切り上げます。

出力:

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

例2 - 2019年3月に出発空港JFKで搭乗したすべての乗客の名前、フライト番号および移動日を出力します。

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'

説明: timestamp_floor関数をユニット値MONTHとともに使用して、移動日を月の始めに切り捨てます。次に、結果のタイムスタンプ値を文字列"2019-03-01"と比較し、目的の乗客を選択します。この問合せでは、輸送中の乗客は考慮されません。

この例では、日付を ISO-8601形式の文字列で指定します。この文字列は暗黙的にCASTをTIMESTAMP値に入れます。

乗客による複数のチェック・バッグによる結果の重複を回避するために、この問合せではbagInfo配列の最初の要素のみが考慮されます。

出力:

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

例3 - 航空会社の手荷物追跡データから、出発地MELのチェック・バッグで実行されたすべてのアクティビティを印刷します。アクションを1分間隔に揃えます。

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"

説明: この問合せでは、単位がMINUTEのtimestamp_round関数を使用して、actionTimeを最も近い分に丸めます。

乗客が複数の手荷物をチェックしたために結果が重複しないように、この問合せではbagInfo配列の最初の要素のみを考慮します。

出力:

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

例4 - 2019年1月1日からバケットを使用して、IST空港から出発する乗客数の統計を12時間ごとにフェッチします。2019年2月のデータのみを考慮します。

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

説明: 2019年2月に旅行する乗客を考慮するには、timestamp_floor関数を使用して、flightDateを月の最初に切り捨てます。結果を文字列"2019-02-01T00:00:00Z"と比較します。この例では、日付を ISO-8601形式の文字列で指定します。この文字列は暗黙的にCASTをTIMESTAMP値に入れます。

IST空港からの輸送フライトを含めるには、配列コンストラクタ[ ]を使用して、flightLegsが配列であることを示し、検索で各fltRouteSrc配列要素を考慮します。

間隔が12時間、起点が2019年1月1日であるflightDateフィールドで、timsestamp_bucket関数を使用します。

出力:

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

タイムスタンプ書式関数

format_timestamp関数およびparse_to_timestamp関数を使用して、タイムスタンプ値を書式設定できます。また、to_last_day_of_month関数を使用して、特定のタイムスタンプから月の最終日をフェッチすることもできます。

例1 - 特定のチケット番号を持つ乗客の場合、patternおよび入力されたtimezoneに従って、最初のレッグに推定到着時間を出力します。

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

説明:このクエリーでは、format_timestamp関数の引数として estimatedArrivalフィールド、pattern、および timezoneのフルネームを指定して、timestamp文字列を指定された「MMM dd、 yyyy HH:mm:ss」パターンに変換します。

ノート: pattern引数の文字'O'はZoneOffsetを表し、結果の文字列でグリニッジ/UTCと異なる時間を出力します。

出力:

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

例2 - 指定されたstringを、ゾーン・オフセットを含む指定されたpatternでタイムスタンプに解析します。

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

説明:この問合せでは、string引数にTimeZoneID (GMT+02:00)があるため、pattern引数にはゾーンシンボルまたはZoneOffsetを含める必要があります。format_timestamp関数でラップすると、出力タイムスタンプはGMT+02:00タイムゾーンで表示されます。

出力:

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

例3 - サブスクライバの場合、アカウント・サブスクリプションが失効する月の最終日を出力します。

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

出力:

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

タイムスタンプ抽出関数

タイムスタンプ抽出関数は、指定されたタイムスタンプから対応する日付、週または索引値をフェッチします。

日付抽出関数は、タイムスタンプから対応する年/月/日/時/分/秒/ミリ秒/マイクロ秒/ナノ秒を返します。

例1 - 航空会社の手荷物追跡データから乗客の連結出張詳細を取得します。

航空会社アプリケーションでは、乗客が今度の旅行詳細の簡単な概要がわかると便利です。その他の時間関数を使用して、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

説明:

時間関数を使用して、旅行の日付、月および年を取得できます。concat文字列関数は、取得した旅行レコードを連結して、アプリケーションに必要な形式で表示するために使用されます。最初にCAST式を使用してflightDatesをTIMESTAMに変換し、タイムスタンプから日付、月および年の詳細のフェッチを行います。

出力:

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

問合せによって、乗客のクイック参照として機能するフライト詳細が返されます。

週抽出関数は、タイムスタンプから対応する週/isoweekを返します。

例2 - 旅客の出張日から週番号とISO週番号を決定する。

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

説明:まず、CAST式を使用して flightDateをTIMESTAMPに変換してから、タイムスタンプから週とisoweekをフェッチします。

出力:

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

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

Timestamp索引抽出関数は、タイムスタンプから対応する四半期/週/月/年索引を返します。

例3 - 指定されたタイムスタンプの曜日を検索します。

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

説明: 問合せの2番目のタイムスタンプは、サポートされていない形式'06/19/24'自体であるため、それを parse_to_timestamp関数にラップして有効にします。

出力:

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

現在の時間関数

current_time_millis関数およびcurrent_time関数を使用して、現在の時間をフェッチできます。current_time_millisファンクションは、時間をミリ秒数として戻します。current_timeファンクションは、時間をタイムスタンプ値として返します。

例1 - 乗客の最終移動日と現在の日付の間の経過時間を判断します。

航空会社アプリケーションで、数人の顧客が頻繁に旅行し、フリークエント・フライヤのマイルの報奨を受ける権利があります。乗客の最終旅行日と現在の日付の間の時間経過を判断して、このような報奨プログラムに対して考慮できるかどうかを評価できます。

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

説明:

current_time関数を使用して、現在の時間を取得できます。最終旅行日と現在の日付の間の期間を決定するために、現在の時間をget_duration/timestamp_diff関数に最終旅行時間とともに指定できます。timestamp_diff関数とget_duration関数の詳細は、次のとおりです。

出力:

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

current_time関数を使用して、現在の時間を計算します。timestamp_diff関数を使用して、現在の時間と最終フライト日の時間差を計算します。最初にCAST式を使用してflightDatesをTIMESTAMに変換し、タイムスタンプから日、月および年の詳細のフェッチを行います。timestamp_diff関数では2つのタイムスタンプ値の間のミリ秒数が返されるため、get_duration関数を使用してミリ秒を期間文字列に変換します。

get_duration関数は、戻り値に基づいてミリ秒を日、時間、分、秒およびミリ秒に変換します。計算のために次の変換が考慮されます:

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

例: timestamp_diff関数が値129084684821ミリ秒を返す場合、get_duration関数は、それに応じて1494日52分4秒687ミリ秒に変換します。

QueryRequest APIの使用例

QueryRequest APIを使用し、SQL関数を適用してNoSQL表からデータをフェッチできます。

問合せを実行するには、NoSQLHandle.query() APIを使用します。

こちらにあるサンプル中からフル・コードSQLFunctions.javaをダウンロードします。

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

問合せを実行するには、borneo.NoSQLHandle.query()メソッドを使用します。

こちらにあるサンプル中からフル・コードSQLFunctions.pyをダウンロードします。

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

問合せを実行するには、Client.Query関数を使用します。

こちらにあるサンプル中からフル・コードSQLFunctions.goをダウンロードします。

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

問合せを実行するには、queryメソッドを使用します。

JavaScript: こちらのサンプルの中からフル・コードSQLFunctions.jsをダウンロードします。

  //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: こちらのサンプルの中からフル・コードSQLFunctions.tsをダウンロードします。

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

問合せを実行するには、QueryAsyncメソッドをコールするか、GetQueryAsyncEnumerableメソッドをコールして、結果の非同期列挙可能性に対して反復処理します。

こちらにあるサンプルの中にフル・コードSQLFunctions.csをダウンロードします。

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

問合せでのタイムスタンプ関数の使用

タイムスタンプ算術関数

タイムスタンプ丸め関数

タイムスタンプ書式関数

タイムスタンプ抽出関数

現在の時間関数

QueryRequest APIの使用例

関連トピック