Date/Time Functions

Table 3-15 shows the available functions for date/time value processing. The basic arithmetic operators (+, *, etc.) are also available. For formatting functions, refer to the Section called Formatting Functions. You should be familiar with the background information on date/time data types (see the Section called Date/Time Types in Chapter 2).

Table 3-15. Date/Time Functions

NameReturn TypeDescriptionExampleResult
current_datedate returns current date; see also below   
current_timetime returns current time (of day); see also below   
current_timestamptimestamp returns current date and time; see also below   
date_part(text, timestamp)double precision extract subfield from date/time value (equivalent to extract); see also below date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precision extract subfield from interval value (equivalent to extract); see also below date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestamp truncate date to specified precision; see also below date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00+00
extract(identifier from timestamp)double precision extract subfield from date/time value; see also below extract(hour from timestamp '2001-02-16 20:38:40')20
extract(identifier from interval)double precision extract subfield from interval value; see also below extract(month from interval '2 years 3 months')3
isfinite(timestamp)booleanReturns true if the time stamp is finite (neither invalid nor infinity)isfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)booleanReturns true if the interval is finite in lengthisfinite(interval '4 hours')true
now()timestamp returns current date and time (equivalent to current_timestamp); see also below   
timeofday()text returns high-precision date and time; see also below timeofday()Wed Feb 21 17:01:13.000126 2001 EST
timestamp(date)timestampconvert date to timestamptimestamp(date '2000-12-25')2000-12-25 00:00:00
timestamp(date, time)timestampcombine date and time into a timestamptimestamp(date '1998-02-24',time '23:07')1998-02-24 23:07:00

EXTRACT

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 in most cases.

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

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 for field:

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 considers most years in the 1900's to be 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

Note that dow is available for timestamp values only.

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

Note that doy is available for timestamp values only.

epoch

For date and timestamp values, the number of seconds since 1970-01-01 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.

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

Note that this includes full seconds.

millennium

The year field divided by 1000

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

Note that this is not really the millennium that the date is in.

milliseconds

The seconds field, including fractional parts, multiplied by 1000.

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

Note that this includes full seconds.

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.

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

Note that quarter is available for timestamp values only.

second

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

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

Note that a value of 60 may be valid if leap seconds are implemented by the operating system

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.

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

year

The year field

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

date_part

The date_part function is the traditional PostgreSQL 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.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes')
Result: 4

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

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00+00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00+00

Current Date/Time

The following functions are available to obtain the current date and/or time:
CURRENT_TIME
CURRENT_DATE
CURRENT_TIMESTAMP
Note that because of the requirements of the SQL standard, these functions must not be called with trailing parentheses.

SELECT CURRENT_TIME;
19:07:32

SELECT CURRENT_DATE;
2001-02-17

SELECT CURRENT_TIMESTAMP;
2001-02-17 19:07:32-05

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

There is also timeofday(), which returns current time to higher precision than the CURRENT_TIMESTAMP family does:

SELECT timeofday();
 Sat Feb 17 19:07:32.000126 2001 EST

timeofday() uses the operating system call gettimeofday(2), which may have resolution as good as microseconds (depending on your platform); the other functions rely on time(2) which is restricted to one-second resolution. For historical reasons, timeofday() returns its result as 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 datatypes 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';

Caution

You should not use the third form when specifying a DEFAULT value while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used. The first two forms will not be evaluated until the default value is used because they are function calls. They will give the desired behavior of defaulting to the time of row insertion.