1. Developers Guide
  2. Reference
  3. Timestamp functions
  4. timestamp_diff() and get_duration() functions

timestamp_diff() and get_duration() functions

timestamp_diff()

Returns the number of milliseconds between two timestamp values. The result type is LONG.

Syntax:
LONG timestamp_diff(TIMESTAMP timestamp1, TIMESTAMP
      timestamp2)
Semantics:
  • 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.

Syntax:
STRING get_duration(LONG duration_millis)
Semantics:
  • 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:

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? 
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.

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"}
To determine the duration in milliseconds, use only the 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
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?
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.

To determine the duration in milliseconds, only use the 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)
Output: 
{"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? 
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.

To determine the duration in milliseconds, use the 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
Output: 
{"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"}