Previous Next JavaScript must be enabled to correctly display this content

## Timestamp Arithmetic Functions

You can use `timestamp_add`, `timestamp_diff`, or `get_duration` functions to perform arithmetic operations on the timestamp and duration values.

Example 1: In the airline application, a buffer of five minutes delay is considered "on time". Print the estimated arrival time on the first leg with a buffer of five minutes for the passenger with ticket number 1762399766476.

    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.

Output:

    {"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}

Note:

The column `estimatedArrival` 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 data type.

ISO8601 describes an internationally accepted way to represent dates, times, and durations.

Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

where

- YYYY specifies the year, as four decimal digits
- MM specifies the month, as two decimal digits, 00 to 12
- DD specifies the day, as two decimal digits, 00 to 31
- hh specifies the hour, as two decimal digits, 00 to 23
- mm specifies the minutes, as two decimal digits, 00 to 59
- ss[.s[s[s[s[s]]]]] specifies the seconds, as two decimal digits, 00 to 59, optionally followed by a decimal point and 1 to 6 decimal digits (representing the fractional part of a second).
- Z specifies UTC time (time zone 0). (It can also be specified by +00:00, but not by –00:00.)
- (+|-)hh:mm specifies the time-zone as difference from UTC. (One of + or – is required.)

Example 1a: Print the estimated arrival time in every leg with a buffer of five minutes for the passenger with ticket number 1762399766476.

    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 on 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 `flightLegs` array are unnested to execute the query.

Output:

    {"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"}

Example 2 : How many bags arrived in the last week?

    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 the `bagInfo` 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 week. The `count` function determines the total number of bags in this time period.

Output:

    {"COUNT_LASTWEEK":0}

Example 3: Find the number of bags arriving in the next 6 hours.

    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.

Output:

    {"COUNT_NEXT6HOURS":0}

Example 4: 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 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

Output:

    {"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}

Example 5: 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, 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 6: 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"}