Previous Next JavaScript must be enabled to correctly display this content

## timestamp_ceil function

The `timestamp_ceil` function returns the rounded-up value of the given timestamp to the specified unit.

If the input timestamp value is already rounded up to the specified unit, then the return value is the same as the input timestamp value.

Syntax:

    TIMESTAMP timestamp_ceil(<timestamp>[, unit])

Semantics:

- timestamp: The `timestamp` argument takes a TIMESTAMP value or a value that can be cast to TIMESTAMP type.
- unit: The `unit` argument is optional and a STRING data type. If not specified, DAY is the default unit. For more details, see Supported units.
- Return Value: TIMESTAMP(0)
  The function returns NULL in the following cases:
  - If either the `timestamp` or `unit` argument is set to NULL.
  - If the input `timestamp` is not castable to TIMESTAMP type.

Example 12-15 For airline passengers with reservation code 'LE6J4Z' to 'ZG8Z5N', print the routing information and bag arrival dates rounded up to closest hour

    SELECT $b.routing AS ROUTE,
    timestamp_ceil($b.bagArrivalDate, 'HOUR') AS BAGTIME
    FROM BaggageInfo bag, bag.bagInfo[0] AS $b
    WHERE confNo BETWEEN 'LE6J4Z' and 'ZG8Z5N'

Explanation: Use the `timestamp_ceil` function with the unit value of HOUR to round up the checked bag arrival dates to the beginning of the next hour.

To avoid the duplication of results due to multiple checked baggage by a passenger, you consider only the first element of the `bagInfo` array in this query.

Output:

    {"ROUTE":"SFO/IST/ATH/JTR","BAGTIME":"2019-02-03T00:00:00Z"}
    {"ROUTE":"JFK/IST/VIE","BAGTIME":"2019-03-05T12:00:00Z"}
    {"ROUTE":"SFO/IST/ATH/JTR","BAGTIME":"2019-03-12T16:00:00Z"}
    {"ROUTE":"MSQ/FRA/HKG","BAGTIME":"2019-02-03T09:00:00Z"}
    {"ROUTE":"MXP/CDG/SLC/BZN","BAGTIME":"2019-03-15T11:00:00Z"}
    {"ROUTE":"MIA/LAX/MEL","BAGTIME":"2019-02-04T11:00:00Z"}
    {"ROUTE":"SFO/ORD/FRA","BAGTIME":"2019-03-02T14:00:00Z"}
    {"ROUTE":"SFO/IST/ATH/JTR","BAGTIME":"2019-03-12T16:00:00Z"}
    {"ROUTE":"SFO/IST/ATH/JTR","BAGTIME":"2019-03-07T17:00:00Z"}
    {"ROUTE":"MIA/LAX/MEL","BAGTIME":"2019-02-02T00:00:00Z"}
    {"ROUTE":"MEL/LAX/MIA","BAGTIME":"2019-03-02T17:00:00Z"}
    {"ROUTE":"MXP/CDG/SLC/BZN","BAGTIME":"2019-02-21T15:00:00Z"}
    {"ROUTE":"MSQ/FRA/HKG","BAGTIME":"2019-02-13T12:00:00Z"}
    {"ROUTE":"JFK/MAD","BAGTIME":"2019-03-07T14:00:00Z"}

Example 12-16 From airline baggage tracking data, print the bag arrival date and the bag auction date for a passenger with ticket number 1762344493810, considering 90 days as the luggage retention period

    SELECT $b.bagArrivalDate AS BagArrival,
    timestamp_ceil(timestamp_add($b.bagArrivalDate, "90 Days"), 'day') AS BagCollection
    FROM BaggageInfo bag, bag.bagInfo AS $b
    WHERE ticketNo=1762344493810

Explanation: This query shows how to nest the timestamp functions. To determine the date an unclaimed bag is retained, add 90 days to the `bagArrivalDate` using the timestamp_add function. The `timestamp_ceil` function rounds up the value to the beginning of the next day.

Output:

    {"BagArrival":"2019-02-01T16:13:00Z","BagCollection":"2019-05-03T00:00:00Z"}