1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Timestamps
  4. timestamp_round function

timestamp_round function

The timestamp_round function returns a timestamp after rounding the supplied timestamp to the specified unit.

Note:

See also, timestamp_ceil and timestamp_floor/timestamp_trunc functions.

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

Syntax:

TIMESTAMP timestamp_round(<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.

The units and their behavior are as follows:

Table 12-2 timestamp_round functional behavior

Unit Actions
YEAR, IYEAR Rounds up to the next year if the timestamp is on or after July first, otherwise rounds down.
QUARTER Rounds up to the next quarter if the timestamp is on or after the 16th day of the 2nd month of the quarter, otherwise rounds down.
MONTH Rounds up to the next month if the timestamp is on or after the 16th day, otherwise rounds down.
WEEK, IWEEK Rounds up to the next week if the timestamp is on or after the mid-day(12 PM) of the 4th day of the week, otherwise rounds down.
DAY Rounds up to the next day if the timestamp is on or after the mid-day(12 PM) of the current day, otherwise rounds down.
HOUR Rounds up to the next hour if the timestamp is on or after half an hour of the current hour, otherwise rounds down.
MINUTE Rounds up to the next minute if the timestamp is on or after 30 seconds of the current minute, otherwise rounds down.
SECOND Rounds up to the next second if the timestamp is on or after 500 milliseconds of the current second, otherwise rounds down.

Example 12-15 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"}