ROUND

Syntax

ROUND
(
  expr  IN date [, fmt   IN text]
)
RETURN date;

ROUND
(
  expr  IN timestamp without time zone [, fmt   IN text]
)
RETURN timestamp without time zone;

ROUND
(
  expr  IN timestamp with time zone [, fmt   IN text]
)
RETURN timestamp with time zone;

Overview

The ROUND function rounds a date or timestamp value according to a given format model.

In other words, it adjusts the date or time value according to the specified unit (e.g., year, month, minute, etc.) and returns it.

The format string (fmt) allows you to specify the rounding unit, and if the value is NULL, it will be returned as NULL for the new value.

For the timestamp version, the seconds are set to zero after rounding up the time portion.

Parameters

Parameter	Description
expr	date, timestamp, timestamptz type: the date or timestamp value to round to.
fmt	text type: A format string indicating the rounding criteria. For example, you can round to various units such as ‘YYYY’, ‘MM’, ‘DDD’, ‘HH24’. If this value is NULL, it will be treated as the default, returning the original value, or as 'DDD' (rounded to the nearest day).

Example

# test 1
-- round date example: rounds the date '2023-05-15' to month
SELECT oracle.ROUND('2023-05-15'::date, 'MONTH');
-- result: returns a date adjusted to the start of the month or a specific reference date in the month.

   round    
------------
 2023-05-01
(1 row)

# test 2
-- round date example (unformatted): rounds to the default format('DDD')
SELECT oracle.ROUND('2023-05-15'::date);
-- result: returns the result with the date '2023-05-15' rounded according to the format model

   round    
------------
 2023-05-15
(1 row)


# test 3
-- round timestamp example: rounds timestamp '2023-05-15 14:35:20' to hours
SELECT oracle.ROUND('2023-05-15 14:35:20'::timestamp, 'HH24');
-- result: '2023-05-15 15:00:00' (minutes and seconds are adjusted to 0)

        round        
---------------------
 2023-05-15 15:00:00
(1 row)

# test 4
-- round timestamp with time zone example: rounds to the default format('DDD')
SELECT oracle.ROUND('2023-05-15 14:35:20+09'::timestamptz);
-- result: returns the timestamp rounded according to the format model

         round          
------------------------
 2023-05-16 00:00:00+09
(1 row)