タイムスタンプ・ラウンド関数

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: 航空会社の手荷物追跡データから、チケット番号1762344493810の乗客の手荷物到着日と手荷物競売日を、荷物保持期間として90日とみなして出力。

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'

説明: 単位値をMONTHに設定したtimestamp_floor関数を使用して、旅行日を月の初めに切り下げます。次に、結果のタイムスタンプ値と文字列2019-03-01を比較して、目的の乗客を選択します。この問合せでは、乗継中の乗客は考慮されません。

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

1人の乗客が複数の受託手荷物を預けたことによる結果の重複を避けるため、この問合せでは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を最も近い分に丸めます。

1人の乗客が複数の受託手荷物を預けたことによる結果の重複を避けるため、この問合せでは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日から始まるバケットを使用した、12時間ごとにIST空港から出発する乗客数の統計のフェッチ。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配列要素を考慮します。

intervalが12時間でoriginが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}