Date/time functions and operators v17

The date/time functions table shows the available functions for date/time value processing.

Overview of basic arithmetic operators

The following table shows the behaviors of the basic arithmetic operators (+, -). For formatting functions, refer to IMMUTABLE TO_CHAR(TIMESTAMP, format). For more information on date/time data types, see Date/time types.

Overview of date and time functions

In the date/time functions of the following table the use of the DATE and TIMESTAMP data types are interchangeable.

ADD_MONTHS

The ADD_MONTHS functions adds or subtracts the specified number of months to or from the given date. Use a negative value to subtract. The resulting day of the month is the same as the day of the month of the given date except when the day is the last day of the month. In that case, the resulting date always falls on the last day of the month.

Any fractional portion of the number of months parameter is truncated before performing the calculation.

If the given date contains a time portion, it's carried forward to the result unchanged.

These examples show the ADD_MONTHS function:

SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;

     add_months
--------------------
 13-OCT-07 00:00:00
(1 row)

SELECT ADD_MONTHS('31-DEC-06',2) FROM DUAL;

     add_months
--------------------
 28-FEB-07 00:00:00
(1 row)

SELECT ADD_MONTHS('31-MAY-04',-3) FROM DUAL;

     add_months
--------------------
 29-FEB-04 00:00:00
(1 row)

CURRENT DATE/TIME

EDB Postgres Advanced Server provides functions that return values related to the current date and time. These functions return values based on the start time of the current transaction:

• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• LOCALTIMESTAMP(precision)

CURRENT_DATE returns the current date and time based on the start time of the current transaction. The value of CURRENT_DATE doesn't change if called multiple times in a transaction.

SELECT CURRENT_DATE FROM DUAL;

date
-----------
 06-AUG-07

CURRENT_TIMESTAMP returns the current date and time. When called from a single SQL statement, it returns the same value for each occurrence in the statement. If called from multiple statements in a transaction, it might return different values for each occurrence. If called from a function, it might return a value different from the one returned by current_timestamp in the caller.

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP FROM DUAL;

current_timestamp | current_timestamp
----------------------------------+----------------------------------
 02-SEP-13 17:52:29.261473 +05:00 | 02-SEP-13 17:52:29.261474 +05:00

You can optionally give LOCALTIMESTAMP a precision parameter. The parameter causes the result to be rounded to the specified number of fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

SELECT LOCALTIMESTAMP FROM DUAL;

timestamp
------------------------
 06-AUG-07 16:11:35.973
(1 row)

SELECT LOCALTIMESTAMP(2) FROM DUAL;

timestamp
-----------------------
 06-AUG-07 16:11:44.58
(1 row)

Since these functions return the start time of the current transaction, their values don't change during the transaction. The intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications in the same transaction have the same timestamp. Other database systems might advance these values more frequently.

TIMEZONE functions

EDB Postgres Advanced Server provides functions that return timezone set values.

DBTIMEZONE

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset in the format [+\|-]TZH:TZM or a time zone region name. The return type depends on the database time zone value specified in the recent CREATE DATABASE or ALTER DATABASE statement.

ALTER DATABASE edb SET TIMEZONE='-09:00';
SELECT dbtimezone FROM DUAL;

 dbtimezone 
------------
 -09:00
(1 row)

ALTER DATABASE edb SET TIMEZONE='Europe/London';
SELECT dbtimezone FROM DUAL;

  dbtimezone   
---------------
 Europe/London
(1 row)

SESSIONTIMEZONE

SESSIONTIMEZONE returns the time zone of the current session. The return type is a time zone offset in the format [+\|-]TZH:TZM or a time zone region name. The return type depends on the session time zone value specified in the recent ALTER SESSION or SET statement.

ALTER SESSION SET timezone to 'America/New_York';
SELECT sessiontimezone() FROM dual;

 sessiontimezone  
------------------
 America/New_York
(1 row)

SET timezone TO '+5:30';
SELECT sessiontimezone() FROM dual;

 sessiontimezone 
-----------------
 +5:30
(1 row)

EXTRACT

The EXTRACT function retrieves subfields such as year or hour from date/time values. The EXTRACT function returns values of type DOUBLE PRECISION. The following are valid field names:

YEAR

The year field.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
     2001
(1 row)

MONTH

The number of the month in the year (1 - 12).

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
       2
(1 row)

DAY

The day of the month field (1 - 31).

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
       16
(1 row)

HOUR

The hour field (0 - 23).

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
       20
(1 row)

MINUTE

The minutes field (0 - 59).

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
       38
(1 row)

SECOND

The seconds field, including fractional parts (0 - 59).

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

 date_part
-----------
       40
(1 row)

MONTHS_BETWEEN

The MONTHS_BETWEEN function returns the number of months between two dates. The result is a numeric value that's positive if the first date is greater than the second date. It's negative if the first date is less than the second date.

The result is always a whole number of months if the day of the month of both date parameters is the same or both date parameters fall on the last day of their respective months.

These examples show the MONTHS_BETWEEN function:

SELECT MONTHS_BETWEEN('15-DEC-06','15-OCT-06') FROM DUAL;

 months_between
----------------
              2
(1 row)

SELECT MONTHS_BETWEEN('15-OCT-06','15-DEC-06') FROM DUAL;

 months_between
----------------
             -2
(1 row)

SELECT MONTHS_BETWEEN('31-JUL-00','01-JUL-00') FROM DUAL;

 months_between
----------------
    0.967741935
(1 row)

SELECT MONTHS_BETWEEN('01-JAN-07','01-JAN-06') FROM DUAL;

 months_between
----------------
             12
(1 row)

NEXT_DAY

The NEXT_DAY function returns the first occurrence of the given weekday strictly greater than the given date. You must specify at least the first three letters of the weekday, for example, SAT. If the given date contains a time portion, it's carried forward to the result unchanged.

These examples show the NEXT_DAY function.

SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY') FROM DUAL;

      next_day
--------------------
 19-AUG-07 00:00:00
(1 row)

SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON') FROM DUAL;

      next_day
--------------------
 20-AUG-07 00:00:00
(1 row)

NEW_TIME

The NEW_TIME function converts a date and time from one time zone to another. NEW_TIME returns a value of type DATE. The syntax is:

NEW_TIME(DATE, time_zone1, time_zone2)

time_zone1 and time_zone2 must be string values from the Time zone column of the following table.

This example shows the NEW_TIME function:

SELECT NEW_TIME(TO_DATE('08-13-07 10:35:15','MM-DD-YY HH24:MI:SS'),'AST',
'PST') "Pacific Standard Time" FROM DUAL;

Pacific Standard Time
---------------------
 13-AUG-07 06:35:15
(1 row)

NUMTODSINTERVAL

The NUMTODSINTERVAL function converts a numeric value to a time interval that includes day-through-second interval units. When calling the function, specify the smallest fractional interval type to include in the result set. The valid interval types are DAY, HOUR, MINUTE, and SECOND.

This example converts a numeric value to a time interval that includes days and hours:

SELECT numtodsinterval(100, ‘hour’);

numtodsinterval
---------------
4 days 04:00:00
(1 row)

This example converts a numeric value to a time interval that includes minutes and seconds:

SELECT numtodsinterval(100, ‘second’);

numtodsinterval
---------------
1 min 40 secs
(1 row)

NUMTOYMINTERVAL

The NUMTOYMINTERVAL function converts a numeric value to a time interval that includes year-through-month interval units. When calling the function, specify the smallest fractional interval type to include in the result set. The valid interval types are YEAR and MONTH.

This example converts a numeric value to a time interval that includes years and months:

SELECT numtoyminterval(100, ‘month’);

numtoyminterval
---------------
8 years 4 mons
(1 row)

This example converts a numeric value to a time interval that includes years only:

SELECT numtoyminterval(100, ‘year’);

numtoyminterval
---------------
100 years
(1 row)

ROUND

The ROUND function returns a date rounded according to a specified template pattern. If you omit the template pattern, the date is rounded to the nearest day. The following table shows the template patterns for the ROUND function.

These examples show the use of the ROUND function.

These examples round to the nearest hundred years:

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)

These examples round to the nearest 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)

These examples round to the nearest ISO year. The first example rounds to 2004, and the ISO year for 2004 begins on December 29, 2003. The second example rounds to 2005, and the ISO year for 2005 begins on January 3 of that same year.

(An ISO year begins on the first Monday from which a seven-day span, Monday through Sunday, contains at least four days of the new year. It's possible for the beginning of an ISO year to start in December of the prior year.)

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)

These examples round to the nearest quarter:

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)

These examples round to the nearest 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)

These examples round to the nearest week. The first day of 2007 lands on a Monday. In the first example, January 18 is closest to the Monday that lands on January 15. In the second example, January 19 is closer to the Monday that falls on 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)

These examples round to the nearest ISO week. An ISO week begins on a Monday. In the first example, January 1, 2004 is closest to the Monday that lands on December 29, 2003. In the second example, January 2, 2004 is closer to the Monday that lands on 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)

These examples round to the nearest week, where a week is considered to start on the same day as the first day of the month:

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)

These examples round to the nearest 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)

These examples round to the start of the nearest day of the week (Sunday):

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)

These examples round to the nearest 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)

These examples round to the nearest 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)

SYSDATE

The SYSDATE function returns the current date and time (timestamp without timezone) of the operating system on which the database server resides. The function is STABLE and requires no arguments.

When called from a single SQL statement, it returns the same value for each occurrence in the statement. If called from multiple statements in a transaction, it might return different values for each occurrence. If called from a function, it might return a value different from the one returned by SYSDATE in the caller.

This example shows a call to SYSDATE:

SELECT SYSDATE, SYSDATE FROM DUAL;

   sysdate          |      sysdate
--------------------+--------------------
 28-APR-20 16:45:28 | 28-APR-20 16:45:28
(1 row)

TRUNC

The TRUNC function returns a date truncated according to a specified template pattern. If you omit the template pattern, the date is truncated to the nearest day. The following table shows the template patterns for the TRUNC function.

Following are examples that use the TRUNC function.

This example truncates down to the hundred-years unit:

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

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

This example truncates down to the year:

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

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

This example truncates down to the beginning of the ISO year:

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

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

This example truncates down to the start date of the quarter:

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

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

This example truncates to the start of the month:

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

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

This example truncates down to the start of the week determined by the first day of the year. The first day of 2007 lands on a Monday, so the Monday just prior to January 19 is January 15.

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

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

This example truncates to the start of an ISO week. An ISO week begins on a Monday. January 2, 2004 falls in the ISO week that starts on Monday, December 29, 2003.

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

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

This example truncates to the start of the week, where a week is considered to start on the same day as the first day of the month:

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

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

This example truncates to the start of the day:

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

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

This example truncates to the start of the week (Sunday):

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

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

This example truncates to the start of the hour:

SELECT TO_CHAR(TRUNC(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 08:00:00
(1 row)

This example truncates to the minute:

SELECT TO_CHAR(TRUNC(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:30:00
(1 row)