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".
Examples:
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
Explanation: In an airline application every customer can have a
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. To
determine the duration in days or hours or minutes, pass the result
of the timestamp_diff
function to the
get_duration
function.
{"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
function.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
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)
Explanation: In the baggage data, every flightLeg
has an actions array. There are three different actions in the
action 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
. To determine the
duration in days or hours or minutes, pass the result of the
timestamp_diff
function to the
get_duration
function.
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)
{"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
Explanation: In an airline application every customer can have a
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. To determine the duration in days or hours
or minutes, pass the result of the timestamp_diff
function to the get_duration
function.
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
{"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"}