Previous Next JavaScript must be enabled to correctly display this content

## 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-5 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-6 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-7 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 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 one week. The count function determines the total number of bags in this time period.

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