Functions on Timestamps
Create and populate Baggage schema
If you want to follow along with the examples, download the script baggageschema_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table.
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
load
command, run the
script.load -file baggageschema_loaddata.sql
timestamp_add function
Adds a duration to a timestamp value and returns the new timestamp. The duration can
be positive or negative. The result type is TIMESTAMP(9)
.
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)
- timestamp: A TIMESTAMP value or a value that can be cast to TIMESTAMP.
- duration: A string with format [-](<n> <UNIT>)+, where 'n' is a
number and the <UNIT> can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
MILLISECOND, NANOSECOND or the plural form of these keywords (e.g. YEARS).
Note:
The UNIT keyword is case-insensitive. - returnvalue: TIMESTAMP(9)
SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762399766476;
Explanation: In the airline application, a customer can have any number of
flight legs depending on the source and destination. In the query above, you are
fetching the estimated arrival in the "first leg" of the travel. So the first record
of the flightsLeg
array is fetched and the estimatedArrival
time is fetched from the array and a buffer of "5 minutes" is added to
that and displayed.
{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}
Note:
The columnestimatedArrival
is a STRING. If the column has STRING
values in ISO-8601 format, then it will be automatically converted by the SQL
runtime into TIMESTAMP datatype.
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;
Explanation: You want to display the estimatedArrival
time in
every leg. The number of legs can be different for every customer. So variable
reference is used in the query above and the baggageInfo
array and
the flightsLegs
array are unnested to execute the query.
{"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")]
Explanation: You get a count of the number of bags processed by the airline
application in the last week. A customer can have more than one bag( that is
bagInfo
array can have more than one record). The
bagArrivalDate
should have a value between today and the last 7
days. For every record in thebagInfo
array, you determine if the
bag arrival time is between the time now and one week ago. The function
current_time
gives you the time now. An EXISTS condition is
used as a filter for determining if the bag has an arrival date in the last one
week. The count function determines the total number of bags in this time
period.
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")]
Explanation: You get a count of the number of bags that will be processed by
the airline application in the next 6 hours. A customer can have more than one bag(
that is bagInfo
array can have more than one record). The
bagArrivalDate
should be between the time now and the next 6
hours. For every record in the bagInfo
array, you determine if the
bag arrival time is between the time now and six hours later. The function
current_time
gives you the time now. An EXISTS condition is
used as a filter for determining if the bag has an arrival date in the next six
hours. The count function determines the total number of bags in this time
period.
timestamp_diff and get_duration functions
timestamp_diff: Returns the number of milliseconds between two timestamp
values. The result type is LONG
.
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP timestamp2
- timestamp1: A TIMESTAMP value or a value that can be cast to TIMESTAMP
- timestamp2: A TIMESTAMP value or a value that can be cast to TIMESTAMP
- returnvalue: LONG
get_duration: Converts the given number of milliseconds to a duration
string. The result type is STRING
.
STRING get_duration(LONG duration_millis)
- duration_millis: the duration in milliseconds
- returnvalue: STRING. The returned duration string format is [-](<n> <UNIT>)+, where the <UNIT> can be DAY, HOUR, MINUTE, SECOND and MILLISECOND, e.g. "1 day 2 hours" or "-10 minutes 0 second 500 milliseconds".
Example 1 : What is the duration between the time the baggage was boarded at one leg and reached the next leg for the passenger with ticket number 1762355527825?
timestamp_diff
function.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;
Explanation: In an airline application every customer can have different
number of hops/legs between their source and destination. In this query, you
determine the time taken between every flight leg. This is determined by the
difference between bagArrivalDate
and flightDate
for every flight leg.
{"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
function to the get_duration
function.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"}
Example 2: How long does it take from the time of check-in to the time the bag is scanned at the point of boarding for the passenger with ticket number 176234463813?
timestamp_diff
function.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);
Explanation: In the baggage data, every flightLeg
has an
actions
array. There are three different actions in the
actions
array. The action code for the first element in the
array is Checkin/Offload
. For the first leg, the action code is
Checkin
and for the other legs, the action code is
Offload at the hop
. The action code for the second element of
the array is BagTag Scan
. In the query above, you determine the
difference in action time between the bag tag scan and check-in time. You use the
contains
function to filter the action time only if the action
code is Checkin or BagScan. Since only the first flight leg has details of check-in
and bag scan, you additionally filter the data using starts_with
function to fetch only the source code fltRouteSrc
.
{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z","bagScanTime":"2019-03-02T04:52:00Z","diff":-5040000}
timestamp_diff
function to the get_duration
function.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"}
Example 3: How long does it take for the bags of a customer with ticket no 1762320369957 to reach the first transit point?
timestamp_diff
function.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;
Explanation: In an airline application every customer can have different
number of hops/legs between their source and destination. In the example above, you
determine the time taken for the bag to reach the first transit point. In the
baggage data, the flightLeg
is an array. The first record in the
array refers to the first transit point details. The flightDate
in
the first record is the time when the bag leaves the source and the
estimatedArrival
in the first flight leg record indicates the
time it reaches the first transit point. The difference between the two gives the
time taken for the bag to reach the first transit point.
{"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
function to the get_duration
function.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"}
Miscellaneous Timestamp functions
integer? year(timestamp?)
Returns the year for the given timestamp. The returned value is in the range -6383 to 9999. If the argument is NULL or empty, the result is also NULL or empty.
integer? month(timestamp?)
Returns the month for the given timestamp, in the range 1 ~ 12. If the argument is NULL or empty, the result is also NULL or empty.
integer? day(timestamp?)
Returns the day of month for the timestamp, in the range 1 ~ 31. If the argument is NULL or empty, the result is also NULL or empty.
integer? hour(timestamp?)
Returns the hour of day for the timestamp, in the range 0 ~ 23. If the argument is NULL or empty, the result is also NULL or empty.
integer? minute(timestamp?)
Returns the minute for the timestamp, in the range 0 ~ 59. If the argument is NULL or empty, the result is also NULL or empty.
integer? second(timestamp?)
Returns the second for the timestamp, in the range 0 ~ 59. If the argument is NULL or empty, the result is also NULL or empty.
integer? millisecond(timestamp?)
Returns the fractional second in millisecond for the timestamp, in the range 0 ~ 999. If the argument is NULL or empty, the result is also NULL or empty.
integer? microsecond(timestamp?)
Returns the fractional second in microsecond for the timestamp, in the range 0 ~ 999999. If the argument is NULL or empty, the result is also NULL or empty.
integer? nanosecond(timestamp?)
Returns the fractional second in nanosecond for the timestamp, in the range 0 ~ 999999999. If the argument is NULL or empty, the result is also NULL or empty.
integer? week(timestamp?)
Returns the week number within the year where a week starts on Sunday and the first week has a minimum of 1 day in this year, in the range 1 ~ 54. If the argument is NULL or empty, the result is also NULL or empty.
integer? isoweek(timestamp?)
Returns the week number within the year based on IS0-8601, where a week starts on Monday and the first week has a minimum of 4 days in this year, in range 0 ~ 53. If the argument is NULL or empty, the result is also NULL or empty.
long current_time_millis()
Returns the current time in UTC, as the number of milliseconds since January 1, 1970 UTC.
timestamp(3) current_time()
Returns the current time in UTC, as a timestamp value with millisecond precision.