October 15, 2026: PostgreSQL 19 RC 1 Released!
Supported Versions: Current (18) / 17 / 16 / 15 / 14
Development Versions: 19 / devel
Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.
PostgreSQL 7.2.8 Documentation
Prev Chapter 4. Functions and Operators Next

4.8. Date/Time Functions and Operators

Table 4-17 shows the available functions for date/time value processing. Table 4-16 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 4.7. You should be familiar with the background information on date/time data types (see Section 3.5).

The date/time operators described below behave similarly for types involving time zones as well as those without.

Table 4-16. Date/Time Operators

Name Example Result
+ timestamp '2001-09-28 01:00' + interval '23 hours' timestamp '2001-09-29 00:00'
+ date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00'
+ time '01:00' + interval '3 hours' time '04:00'
- timestamp '2001-09-28 23:00' - interval '23 hours' timestamp '2001-09-28'
- date '2001-09-28' - interval '1 hour' timestamp '2001-09-27 23:00'
- time '05:00' - interval '2 hours' time '03:00'
- interval '2 hours' - time '05:00' time '03:00:00'
* interval '1 hour' * int '3' interval '03:00'
/ interval '1 hour' / int '3' interval '00:20'

The date/time functions are summarized below, with additional details in subsequent sections.

Table 4-17. Date/Time Functions

Name Return Type Description Example Result
age(timestamp) interval Subtract from today age(timestamp '1957-06-13') 43 years 8 mons 3 days
age(timestamp, timestamp) interval Subtract arguments age('2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days
current_date date Today's date; see below    
current_time time Time of day; see below    
current_timestamp timestamp Date and time; see below    
date_part(text, timestamp) double precision Get subfield (equivalent to extract); see also below date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_part(text, interval) double precision Get subfield (equivalent to extract); see also below date_part('month', interval '2 years 3 months') 3
date_trunc(text, timestamp) timestamp Truncate to specified precision; see also below date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00+00
extract(field from timestamp) double precision Get subfield; see also below extract(hour from timestamp '2001-02-16 20:38:40') 20
extract(field from interval) double precision Get subfield; see also below extract(month from interval '2 years 3 months') 3
isfinite(timestamp) boolean Test for finite time stamp (neither invalid nor infinity) isfinite(timestamp '2001-02-16 21:28:30') true
isfinite(interval) boolean Test for finite interval isfinite(interval '4 hours') true
now() timestamp Current date and time (equivalent to current_timestamp); see below    
timeofday() text Current date and time; see below timeofday() Wed Feb 21 17:01:13.000126 2001 EST
timestamp(date) timestamp date to timestamp timestamp(date '2000-12-25') 2000-12-25 00:00:00
timestamp(date, time) timestamp date and time to timestamp timestamp(date '1998-02-24',time '23:07') 1998-02-24 23:07:00

4.8.1. EXTRACT, date_part

EXTRACT (field FROM source)

The extract function retrieves sub-fields from date/time values, such as year or hour. source is a value expression that evaluates to type timestamp or interval. (Expressions of type date or time will be cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid values:

century

The year field divided by 100

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

Note that the result for the century field is simply the year field divided by 100, and not the conventional definition which puts most years in the 1900's in the twentieth century.

day

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

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

The year field divided by 10

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 200
dow

The day of the week (0 - 6; Sunday is 0) (for timestamp values only)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5
doy

The day of the year (1 - 365/366) (for timestamp values only)

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 47
epoch

For date and timestamp values, the number of seconds since 1970-01-01 00:00:00-00 (Result may be negative.); for interval values, the total number of seconds in the interval

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800
hour

The hour field (0 - 23)

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

The seconds field, including fractional parts, multiplied by 1 000 000. Note that this includes full seconds.

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000
millennium

The year field divided by 1000

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2

Note that the result for the millennium field is simply the year field divided by 1000, and not the conventional definition which puts years in the 1900's in the second millennium.

milliseconds

The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Result: 28500
minute

The minutes field (0 - 59)

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

For timestamp values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11)

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

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
Result: 3

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
Result: 1
quarter

The quarter of the year (1 - 4) that the day is in (for timestamp values only)

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 1
second

The seconds field, including fractional parts (0 - 59[1])

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

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.5
timezone_hour

The hour component of the time zone offset.

timezone_minute

The minute component of the time zone offset.

week

From a timestamp value, calculate the number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year.