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 1 - 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 2 - 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"}