タイムスタンプの関数
荷物スキーマの作成と移入
例のとおりに実施する場合は、次に示すようにスクリプト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.sqltimestamp_add関数
タイムスタンプ(timestamp)値に期間(duration)を追加し、新しいタイムスタンプを返します。期間(duration)は正でも負でもかまいません。結果型は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キーワードの大文字と小文字は区別されません。 - 戻り値: TIMESTAMP(9)
SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762399766476説明: この航空会社アプリケーションでは、出発地と到着地に応じて顧客にフライト区間がいくつあってもかまいません。前述の問合せでは、旅程の最初の区間(first leg)での推定到着時間(estimated arrival)をフェッチしています。そのため、flightsLeg配列の最初のレコードがフェッチされ、その配列からestimatedArrival時間がフェッチされ、5分間(5 minutes)のバッファがそれに追加されて表示されます。
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}ノート:
列estimatedArrivalはSTRINGです。この列にISO-8601形式でSTRING値が含まれている場合は、SQLランタイムによってそれがTIMESTAMPデータ型に自動変換されます。
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配列とflightsLegs配列はネストされていません。
{"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条件は、荷物の到着日が過去1週以内であるかどうかを判定するためのフィルタとして使用されています。count関数により、この期間内の荷物の合計数が算出されます。
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関数により、この期間内の荷物の合計数が算出されます。
timestamp_diff関数とget_duration関数
timestamp_diff: 2つのタイムスタンプ値の間のミリ秒数を返します。結果型はLONGです。
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP timestamp2- timestamp1: TIMESTAMP値、またはTIMESTAMPにキャストできる値
- timestamp2: TIMESTAMP値、またはTIMESTAMPにキャストできる値
- 戻り値: LONG
get_duration: 指定したミリ秒数を期間文字列に変換します。結果型はSTRINGです。
STRING get_duration(LONG duration_millis)- duration_millis: 期間(ミリ秒数)
- 戻り値: STRING。返される期間文字列の書式は、[-](<n> <UNIT>)+です。ここで、<UNIT>はDAY、HOUR、MINUTE、SECONDおよびMILLISECONDです(例: "1 day 2 hours"または"-10 minutes 0 second 500 milliseconds")。
例1: チケット番号が1762355527825の乗客に関して、ある区間で荷物が載せられてから次の区間に到着するまでの期間はどのくらいでしょうか。
timestamp_diff関数を使用します。SELECT $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とflightDateの差によって決まります。
{"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}timestamp_diff関数の結果をget_duration関数に渡します。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":"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"}例2: チケット番号が176234463813の乗客に関して、チェックインの時点から、搭乗時に荷物がスキャンされるまでにかかる時間はどのくらいでしょうか。
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)説明: 荷物データでは、どのflightLegにもactions配列があります。actions配列には3つの異なるアクションがあります。この配列内の最初の要素のアクション・コードはCheckinまたはOffloadです。最初の区間については、アクション・コードはCheckinであり、他の区間については、アクション・コードはOffload at the hopです。この配列の2番目の要素のアクション・コードはBagTag Scanです。前述の問合せでは、バッグ・タグ・スキャンとチェックイン時間の間のアクション時間の差を特定します。アクション・コードがCheckinまたはBagScanの場合のみ、contains関数を使用してアクション時間をフィルタします。最初のフライト区間にのみチェックインおよびバッグ・スキャンの詳細があるため、starts_with関数を使用してさらにそのデータをフィルタ処理し、ソース・コードfltRouteSrcのみをフェッチします。
{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z","bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}timestamp_diff関数の結果をget_duration関数に渡します。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){"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":"- 1 hour 24 minutes"}例3: チケット番号が1762320369957の顧客の荷物が最初の乗継地に到着するまでにかかる時間はどのくらいでしょうか。
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説明: 航空会社アプリケーションでは、顧客ごとに、出発地と到着地の間の航程または区間の数が異なる可能性があります。前述の例では、荷物が最初の乗継地に到着するまでにかかる時間を特定します。荷物データでは、flightLegは配列です。この配列内の最初のレコードでは、最初の乗継地の詳細が示されています。最初のレコード内のflightDateは荷物が出発地を出る時間であり、最初のフライト区間レコード内のestimatedArrivalは最初の乗継地に到着する時間を示しています。それら2つの差によって、荷物が最初の乗継地に到着するまでにかかる時間がわかります。
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:00:00Z","diff":46800000}
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:40:00Z","diff":49200000}timestamp_diff関数の結果をget_duration関数に渡します。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{"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"}その他のタイムスタンプ関数
year関数
指定されたタイムスタンプの年を返します。戻り値の範囲は、-6383から9999です。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? year(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
month関数
指定されたタイムスタンプの月を1から12の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? month(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
day関数
タイムスタンプの月の日付を1から31の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? day(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
hour関数
タイムスタンプの時間を0から23の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? hour(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
minute関数
タイムスタンプの分を0から59の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? minute(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
second関数
タイムスタンプの秒を0から59の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? second(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
millisecond関数
タイムスタンプの小数秒(ミリ秒)を0から999の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? millisecond(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
microsecond関数
タイムスタンプの小数秒(マイクロ秒)を0から999999の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? microsecond(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
nanosecond関数
タイムスタンプの小数秒(ナノ秒)を0から999999999の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? nanosecond(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
例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をTIMESTAMPに変換し、タイムスタンプから日付、月および年の詳細をフェッチします。
{"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"}問合せによって、乗客のクイック参照として機能するフライト詳細が返されます。
week関数
週が日曜日から始まり、最初の週にこの年の少なくとも1日が含まれる年内の週番号を1から54の範囲で返します。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? week(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
isoweek関数
IS0-8601に基づいて年内の週番号を返します。週は月曜日から始まり、最初の週はこの年の最低4日で、範囲は0から53です。引数がNULLまたは空の場合、結果もNULLまたは空になります。
integer? isoweek(timestamp?)セマンティクス:
- timestamp: この関数は、入力引数としてタイムスタンプを想定します。
- 戻り型: integer
current_time_millis関数
現在の時間(UTC)をUTC 1970年1月1日以降のミリ秒数で返します。
long current_time_millis()セマンティクス:
- この関数は、入力引数を想定していません。
- 戻り型: long
current_time関数
現在の時間をUTCで返します(精度がミリ秒のタイムスタンプ値として)。
timestamp(3) current_time()セマンティクス:
- この関数は、入力引数を想定していません。
- 戻り型: timestamp(3)
例2: その他のタイムスタンプ関数
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,
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関数の詳細は、「timestamp_diff関数とget_duration関数」を参照してください。
{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7,"LastTravel":"1453 days 6 hours 20 minutes 56 seconds 601 milliseconds"}
{"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5,"LastTravel":"1451 days 23 hours 19 minutes 39 seconds 543 milliseconds"}week関数は旅行週を決定し、isoweek関数はISO週の詳細を提供します。current_time関数を使用して、現在の時間を計算します。timestamp_diff関数を使用して、現在の時間と最終フライト日の時間差を計算します。最初にCASTを使用してflightDatesをTIMESTAMPに変換し、タイムスタンプから日、月および年の詳細をフェッチします。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ミリ秒に変換します。