1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Timestamps
  4. timestamp_add function

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

Syntax:
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)
Semantics:
  • 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.
  • Return Value: TIMESTAMP(9)

Example 12-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 datatype.

Example 12-2 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 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.

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 12-3 Find the number of 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 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.

Example 12-4 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.