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

NameExampleResult
+ 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

NameReturn TypeDescriptionExampleResult
age(timestamp)intervalSubtract from todayage(timestamp '1957-06-13')43 years 8 mons 3 days
age(timestamp, timestamp)intervalSubtract argumentsage('2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
current_datedateToday's date; see below   
current_timetimeTime of day; see below   
current_timestamptimestampDate and time; see below   
date_part(text, timestamp)double precisionGet subfield (equivalent to extract); see also below date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precisionGet subfield (equivalent to extract); see also below date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestampTruncate 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 precisionGet subfield; see also below extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double precisionGet subfield; see also below extract(month from interval '2 years 3 months')3
isfinite(timestamp)booleanTest for finite time stamp (neither invalid nor infinity)isfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)booleanTest for finite intervalisfinite(interval '4 hours')true
now()timestampCurrent date and time (equivalent to current_timestamp); see below   
timeofday()textCurrent date and time; see below timeofday()Wed Feb 21 17:01:13.000126 2001 EST
timestamp(date)timestampdate to timestamptimestamp(date '2000-12-25')2000-12-25 00:00:00
timestamp(date, time)timestampdate and time to timestamptimestamp(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

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)

decade

The year field divided by 10

dow

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

doy

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

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

hour

The hour field (0 - 23)

microseconds

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

millennium

The year field divided by 1000

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.

minute

The minutes field (0 - 59)

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)

quarter

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

second

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

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.

year

The year field

The extract function is primarily intended for computational processing. For formatting date/time values for display, see Section 4.7.

The date_part function is modeled on the traditional Ingres equivalent to the SQL-function extract:

date_part('field', source)

Note that here the field value needs to be a string. The valid field values for date_part are the same as for extract.

4.8.2. date_trunc

The function date_trunc is conceptually similar to the trunc function for numbers.

date_trunc('field', source)

source is a value expression of type timestamp (values of type date and time are cast automatically). field selects to which precision to truncate the time stamp value. The return value is of type timestamp with all fields that are less than the selected one set to zero (or one, for day and month).

Valid values for field are:

microseconds
milliseconds
second
minute
hour
day
month
year
decade
century
millennium

4.8.3. Current Date/Time

The following functions are available to obtain the current date and/or time:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME ( precision )
CURRENT_TIMESTAMP ( precision )

CURRENT_TIME and CURRENT_TIMESTAMP can optionally be given a precision parameter, which causes the result to be rounded to that many fractional digits. Without a precision parameter, the result is given to full available precision.

Note: Prior to PostgreSQL 7.2, the precision parameters were unimplemented, and the result was always given in integer seconds.

Note: The SQL99 standard requires these functions to be written without any parentheses, unless a precision parameter is given. As of PostgreSQL 7.2, an empty pair of parentheses can be written, but this is deprecated and may be removed in a future release.

The function now() is the traditional PostgreSQL equivalent to CURRENT_TIMESTAMP.

There is also timeofday(), which for historical reasons returns a text string rather than a timestamp value:

It is quite important to realize that CURRENT_TIMESTAMP and related functions all return the time as of the start of the current transaction; their values do not increment while a transaction is running. But timeofday() returns the actual current time.

All the date/time data types also accept the special literal value now to specify the current date and time. Thus, the following three all return the same result:

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

Notes

[1]

60 if leap seconds are implemented by the operating system