Previous Next JavaScript must be enabled to correctly display this content

## 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-19 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"}