ROUND

Rounds the specified date or time. If you omit the precision argument, ROUND rounds to day (DD) precision.

Behavior type

• Immutable if thetarget date is a TIMESTAMP or DATE

• Stable if the target date is aTIMESTAMPTZ

Syntax

ROUND( rounding-target[, 'precision'] )

Parameters

*rounding-target*

An expression that evaluates to one of the following data types:

• DATE
• TIMESTAMP/TIMESTAMPTZ
• TIMESTAMPTZ

precision

A string constant that specifies precision for the rounded value, one of the following:

• Century: CC | SCC

• Year: SYYY | YYYY | YEAR | YYY | YY | Y

• ISO Year: IYYY | IYY | IY | I

• Quarter: Q

• Month: MONTH | MON | MM | RM

• Same weekday as first day of year: WW

• Same weekday as first day of ISO year: IW

• Same weekday as first day of month: W

• Day (default): DDD | DD | J

• First weekday: DAY | DY | D

• Hour: HH | HH12 | HH24

• Minute: MI

• Second: SS

Note

Hour, minute, and second rounding is not supported by DATE expressions.

Examples

Round to the nearest hour:

=> SELECT ROUND(CURRENT_TIMESTAMP, 'HH');
        ROUND
---------------------
 2016-04-28 15:00:00
(1 row)

Round to the nearest month:

=> SELECT ROUND('9-22-2011 12:34:00'::TIMESTAMP, 'MM');
        ROUND
---------------------
 2011-10-01 00:00:00
(1 row)

See also