4.8. Date/Time Functions and Operators

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:

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.

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

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

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:

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

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.

SELECT CURRENT_TIME;
14:39:53.662522-05

SELECT CURRENT_DATE;
2001-12-23

SELECT CURRENT_TIMESTAMP;
2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
2001-12-23 14:39:53.66-05

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:

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

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';