タイムスタンプ関数
タイムスタンプおよび期間の値に対して様々な算術演算を実行できます。
例のとおりに操作する場合は、スクリプトbaggageschema_loaddata.sqlをダウンロードして、次に示すように実行します。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstoreloadコマンドを使用して、スクリプトを実行します。load -file baggageschema_loaddata.sqlEXTRACT式
extract_expression ::= EXTRACT "(" id FROM expression ")"FROMキーワードの後の式は、最大で1つのタイムスタンプまたはNULLを返す必要があります。この式の結果がNULLまたは空の場合、EXTRACTの結果もそれぞれNULLまたは空になります。それ以外場合は、そのidで指定されたコンポーネントが返されます。このIDは、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MILLISECOND、MICROSECOND、NANOSECOND、WEEK、ISOWEEKのいずれかのキーワードである必要があります。
SELECT fullName,
EXTRACT (YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0)))
AS YEAR FROM BaggageInfo bag
WHERE ticketNo=1762383911861説明: 最初にCASTを使用してbagArrivalDateをTIMESTAMPに変換してから、タイムスタンプからYEARコンポーネントをフェッチします。
{"fullName":"Joanne Diaz","YEAR":2019}SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc,
$t1 AS HOUR FROM BaggageInfo bag,
EXTRACT(HOUR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t1,
EXTRACT(YEAR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t2,
EXTRACT(MONTH FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t3
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "MIA" AND
$t2=2019 AND $t3=02 AND ($t1>10 AND $t1<20)説明: 2019年2月の午前10時から午後10時の間にMIAを経由して移動したフライトの詳細を把握する必要があります。ここでは、多数のフィルタ条件を使用します。まず、フライトはMIAから出発またはMIAを経由して移動しました。到着年は2019年、到着月は2 (2月)です。次に、到着時刻が午前10時から午後10時の間(20時間)であるかどうかでフィルタ処理します。
{"tagNum":"17657806255240","fltRouteSrc":["MIA","LAX"],"HOUR":16}
{"tagNum":"17657806292518","fltRouteSrc":["MIA","LAX"],"HOUR":16}SELECT fullName,
EXTRACT(YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS YEAR,
EXTRACT(MONTH FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS MONTH
FROM BaggageInfo bag WHERE bag.confNo="PQ1M8N"説明: 最初にCASTを使用してbagArrivalDateをTIMESTAMPに変換してから、タイムスタンプからYEARコンポーネントとMONTHコンポーネントをフェッチします。
{"fullName":"Kendal Biddle","YEAR":2019,"MONTH":3}SELECT EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0))) AS MONTH,
count(EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))) AS COUNT
FROM BaggageInfo $bag, $bag.bagInfo[].bagArrivalDate $bag_arr_date
GROUP BY EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))bagInfo配列はフラット化され、手荷物到着日の値が配列からフェッチされます。次に、CASTを使用してbagArrivalDateをTIMESTAMPに変換してから、タイムスタンプからYEARコンポーネントとMONTHコンポーネントをフェッチします。次に、count関数を使用して、各月に対応する合計手荷物を取得します。
ノート:
データには、すべての手荷物が同じ年に届いたという前提があります。したがって、月に基づいてのみデータをグループ化します。{"MONTH":2,"COUNT":11}
{"MONTH":3,"COUNT":10}timestamp_add()関数
タイムスタンプ値に期間を追加し、新しいタイムスタンプを返します。期間は正または負にすることができます。結果タイプはTIMESTAMP(9)です。
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)- timestamp: TIMESTAMP値またはTIMESTAMPにキャストできる値。
- duration: [-](<n> <UNIT>)+書式の文字列。ここで、nは数値で、<UNIT>はYEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MILLISECOND、NANOSECONDまたはこれらのキーワードの複数形式(例: YEARS)です。
ノート:
UNITキーワードの大文字と小文字は区別されません。 - returnvalue: TIMESTAMP(9)
SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag
WHERE ticketNo=1762399766476説明: 航空会社アプリケーションでは、1人の顧客に対して、出発地と目的地に応じて飛行区間をいくつでも設定できます。前述の問合せでは、移動の第1区間での推定到着をフェッチしています。そのため、flightsLeg配列の最初のレコードがフェッチされ、その配列からestimatedArrival時間がフェッチされ、その時間に5分のバッファが追加されて表示されます。
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}ノート:
列estimatedArrivalはSTRINGです。列にISO-8601形式のSTRING値がある場合は、SQLランタイムによってTIMESTAMPデータ型に自動的に変換されます。
ISO8601は、日付、時刻および継続時間を表すために国際的に受け入れられている方法について説明しています。
構文: 日付と時刻: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]
- YYYYは、4桁の10進数で年を指定します
- MMは、00から12までの2桁の10進数で月を指定します
- DDは、00から31までの2桁の10進数で日を指定します
- hhは、00から23までの2桁の10進数で時間を指定します
- mmは、00から59までの2桁の10進数で分を指定します
- ss[.s[s[s[s[s]]]]]は、00から59までの2桁の10進数で秒を指定します。オプションで、小数点と1から6桁の10進数(秒の小数部を表す)を続けます。
- Zは、UTC時間(タイムゾーン0)を指定します。(-00:00ではなく、+00:00で指定することもできます。)
- (+|-)hh:mmは、タイムゾーンをUTCとの差として指定します。(+または-のいずれか1つは必須です。)
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"}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}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}timestamp_diff()関数およびget_duration()関数
timestamp_diff()
2つのタイムスタンプ値の間のミリ秒数を返します。結果タイプはLONGです。
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP
timestamp2)- timestamp1: TIMESTAMP値またはTIMESTAMPにキャストできる値
- timestamp2: TIMESTAMP値またはTIMESTAMPにキャストできる値
- returnvalue: LONG
get_duration()
指定されたミリ秒数を期間文字列に変換します。結果タイプはSTRINGです。
STRING get_duration(LONG duration_millis)- duration_millis: 期間(ミリ秒)
- returnvalue: STRING。返される期間文字列の書式は[-](<n> <UNIT>)+です。ここで、<UNIT>はDAY、HOUR、MINUTE、SECONDおよびMILLISECONDです(例: 1 day 2 hoursまたは-10 minutes 0 second 500)。
例:
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=1762355527825SELECT $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つのactions配列があります。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"}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"}