get_duration function
The get_duration function 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.
- Return Value: 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 12-12 What is the duration in days, hours, or minutes between the time the baggage was boarded at one leg and reached the next leg for the passenger with ticket number 1762355527825?
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 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.
The bagArrivalDate and flightDate is
in an unsupported format for the get_duration function, so wrap it
in the timestamp_diff function to make it valid.
Output:
{"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 12-13 How long does it take in days, hours, or minutes from the time of check-in to when the bag is scanned at the point of boarding for the passenger with ticket number 176234463813?
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
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.
The input values for the get_durration function is in an
unsupported format, so pass it in the timestamp_diff function to make it valid.
Output:
{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":"- 1 hour 24 minutes"}Example 12-14 How long does it take in days, hours, or minutes for the baggage of a customer with ticket no 1762320369957 to reach the first transit point?
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 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.
The input values for the get_durration function is in an
unsupported format, so pass it in the timestamp_diff function to make it valid.
{"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"}