1. Developers Guide
  2. Reference
  3. Timestamp functions
  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.
  • returnvalue: TIMESTAMP(9)
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). ThebagArrivalDate 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 isbagInfo 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}