Timestamp Round Functions

You can use timestamp_ceil, timestamp_floor, timestamp_trunc, timestamp_round, and timestamp_bucket functions to round the timestamp values.

For timestamp_ceil, timestamp_floor, timestamp_trunc, and timestamp_round functions, you must supply a unit as the second argument. The unit specifies the precision to be considered while rounding the input timestamp.

The following units are supported in either singular or plural format: YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND.

You can use the timestamp_bucket function to round the given timestamp value to the beginning of the specified interval (bucket). The interval starts at a specified origin on the timeline.

The timestamp_bucket supports the following intervals in either singular or plural format: WEEK, DAY, HOUR, MINUTE, SECOND.

Example 1: 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"}

Example 2: Print the name, flight number, and travel date for all the passengers who boarded at originating airport JFK in the month of March 2019.

SELECT bag.fullName, $f.flightNo, $f.flightDate
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
WHERE $f.fltRouteSrc = "JFK" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-03-01'

Explanation: You use the timestamp_floor function with the unit value as MONTH to round down the travel dates to the beginning of the month. You then compare the resulting timestamp value with the string "2019-03-01" to select the desired passengers. This query does not consider the passengers in transit.

This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

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

Output:

{"fullName":"Kendal Biddle","flightNo":"BM127","flightDate":"2019-03-04T06:00:00Z"}
{"fullName":"Dierdre Amador","flightNo":"BM495","flightDate":"2019-03-07T07:00:00Z"}

Example 3: From the airline baggage tracking data, print all the activities performed on the checked bags in the originating station MEL. Align the actions to one minute interval.

SELECT $b.actionAt, 
       $b.actionCode, 
       timestamp_round($b.actionTime, 'MINUTE') as actionTime
FROM baggageInfo bag, bag.bagInfo[0].flightLegs[0].actions[] AS $b
WHERE bag.bagInfo[0].flightLegs[0].fltRouteSrc = "MEL"

Explanation: In this query, you use the timestamp_round function with unit as MINUTE to round the actionTime to the nearest minute.

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:

{"actionAt":"MEL","actionCode":"ONLOAD to LAX","actionTime":"2019-03-01T12:20:00Z"}
{"actionAt":"MEL","actionCode":"BagTag Scan at MEL","actionTime":"2019-03-01T11:52:00Z"}
{"actionAt":"MEL","actionCode":"Checkin at MEL","actionTime":"2019-03-01T11:43:00Z"}

Example 4: Fetch the statistics of the number of passengers departing from the IST airport every 12 hrs with buckets starting from January 1st, 2019. Consider data only for the month of February 2019.

SELECT $t AS DATE,
count($t) AS FLIGHTCOUNT 
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[] $f, 
timestamp_bucket($f.flightDate, '12 HOURS', '2019-01-01T00') $t
WHERE $f.fltRouteSrc =any "IST" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-02-01T00:00:00Z'
GROUP BY $t
ORDER BY $t

Explanation: To consider passengers traveling in February 2019, use the timestamp_floor function and round down the flightDate to the beginning of the month. Compare the result with the string "2019-02-01T00:00:00Z". This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

To include the transit flights from the IST airport, use the array constructor [ ] to indicate that the flightLegs is an array and consider each fltRouteSrc array element in the search.

Use the timsestamp_bucket function on the flightDate fields with interval as 12 hours and origin as 1st of January 2019.

Output:

{"DATE":"2019-02-02T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T00:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T12:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-07T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-11T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-12T00:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-12T12:00:00.000000000Z","FLIGHTCOUNT":1}