ROUND function for date time and interval - PolarDB

ROUND returns a date value rounded to the unit specified by a format pattern.

Without a format pattern, the behavior depends on the input type:

DATE or TIMESTAMP: rounds to the nearest day.
INTERVAL: extracts months and days, then returns the total number of days assuming 30 days per month.

Format patterns

Format pattern	Rounding unit
`CC`, `SCC`	Century — rounds to January 1 of the nearest century. Rounds down if the last two digits of the year are 50 or earlier; rounds up to the next century if 51 or later.
`SYYY`, `YYYY`, `YEAR`, `SYEAR`, `YYY`, `YY`, `Y`	Year — rounds to January 1 of the nearest year. Rounds down on June 30 or earlier; rounds up on July 1 or later.
`IYYY`, `IYY`, `IY`, `I`	ISO year — rounds to the start of the nearest ISO year. Rounds down on June 30 or earlier; rounds up on July 1 or later.
`Q`	Quarter — rounds to the first day of the nearest quarter. Rounds down on or before the 15th of the second month of the quarter; rounds up on the 16th or later.
`MONTH`, `MON`, `MM`, `RM`	Month — rounds to the first day of the nearest month. Rounds down on the 15th or earlier; rounds up on the 16th or later.
`WW`	Week — rounds to the same day of the week as the first day of the year.
`IW`	ISO week — rounds to the nearest Monday (the first day of an ISO week).
`W`	Week — rounds to the same day of the week as the first day of the month.
`DDD`, `DD`, `J`	Day — rounds to the nearest day.
`DAY`, `DY`, `D`	Week start — rounds to the nearest Sunday.
`HH`, `HH12`, `HH24`	Hour — rounds to the nearest hour.
`MI`	Minute — rounds to the nearest minute.

Note

Each ISO year begins on the first Monday of the year. The first ISO week contains at least four days of the new year, so an ISO year may start in December of the previous year.

Examples

Round to the nearest century

1950 falls in the first half of the 20th century (last two digits ≤ 50), so it rounds back to 1901. 1951 falls past the midpoint (last two digits > 50), so it rounds forward to 2001.

SELECT TO_CHAR(ROUND(TO_DATE('1950','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century
-------------
 01-JAN-1901
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century
-------------
 01-JAN-2001
(1 row)

Round to the nearest year

Dates on or before June 30 round down to January 1 of the current year. Dates on or after July 1 round up to January 1 of the next year.

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year
-------------
 01-JAN-1999
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year
-------------
 01-JAN-2000
(1 row)

Round to the nearest ISO year

The ISO year for 2004 begins on December 29, 2003. The ISO year for 2005 begins on January 3, 2005. June 30, 2004 is closer to the start of ISO year 2004; July 1, 2004 is closer to the start of ISO year 2005.

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

  ISO Year
-------------
 29-DEC-2003
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

  ISO Year
-------------
 03-JAN-2005
(1 row)

Round to the nearest quarter

February is the second month of Q1. Dates on or before February 15 round back to January 1; dates on or after February 16 round forward to April 1.

SELECT ROUND(TO_DATE('15-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

      Quarter
--------------------
 01-JAN-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

      Quarter
--------------------
 01-APR-07 00:00:00
(1 row)

Round to the nearest month

Dates on or before the 15th round to the first day of the current month; dates on or after the 16th round to the first day of the next month.

SELECT ROUND(TO_DATE('15-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

       Month
--------------------
 01-DEC-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

       Month
--------------------
 01-JAN-08 00:00:00
(1 row)

Round to the nearest week (WW)

WW rounds to the same day of the week as January 1 of the given year. January 1, 2007 fell on a Monday, so all results are Mondays. January 18 is closer to Monday January 15; January 19 is closer to Monday January 22.

SELECT ROUND(TO_DATE('18-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week
--------------------
 15-JAN-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week
--------------------
 22-JAN-07 00:00:00
(1 row)

Round to the nearest ISO week (IW)

IW rounds to the nearest Monday. January 1, 2004 is closest to Monday December 29, 2003; January 2, 2004 is closest to Monday January 5, 2004.

SELECT ROUND(TO_DATE('01-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week
--------------------
 29-DEC-03 00:00:00
(1 row)

SELECT ROUND(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week
--------------------
 05-JAN-04 00:00:00
(1 row)

Round to the nearest week (W)

W rounds to the same day of the week as the first day of the month. March 1, 2007 was a Thursday, so all results are Thursdays. March 5 is closer to Thursday March 8; March 4 is closer to Thursday March 1.

SELECT ROUND(TO_DATE('05-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week
--------------------
 08-MAR-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('04-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week
--------------------
 01-MAR-07 00:00:00
(1 row)

Round to the nearest day

Times before noon (12:00:00 PM) round back to the current day; times at or after noon round forward to the next day.

SELECT ROUND(TO_DATE('04-AUG-07 11:59:59 AM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

        Day
--------------------
 04-AUG-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

        Day
--------------------
 05-AUG-07 00:00:00
(1 row)

Round to the nearest Sunday

DAY rounds to the nearest Sunday. August 8, 2007 was a Wednesday — closer to Sunday August 5. August 9, 2007 was a Thursday — closer to Sunday August 12.

SELECT ROUND(TO_DATE('08-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

    Day of Week
--------------------
 05-AUG-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

    Day of Week
--------------------
 12-AUG-07 00:00:00
(1 row)

Round to the nearest hour

Times with 29 minutes or fewer round back to the current hour; times with 30 minutes or more round forward to the next hour.

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:29','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

        Hour
--------------------
 09-AUG-07 08:00:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

        Hour
--------------------
 09-AUG-07 09:00:00
(1 row)

Round to the nearest minute

Seconds 0–29 round back to the current minute; seconds 30–59 round forward to the next minute.

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:29','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

       Minute
--------------------
 09-AUG-07 08:30:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

       Minute
--------------------
 09-AUG-07 08:31:00
(1 row)

Round an INTERVAL value

Without a format pattern, ROUND on an INTERVAL value converts the interval to a total number of days, assuming 30 days per month.

SELECT ROUND(TIMESTAMP '2020-10-10 10:22:22' - TIMESTAMP '2020-10-05 12:22:22');
 round
-------
     4
(1 row)

SELECT ROUND(INTERVAL '1 year 13 months 3 days');
 round
-------
   753
(1 row)

PolarDB:ROUND