Table 9-27. Date/Time Operators

Table 9-28. Date/Time Functions

9.9.1.EXTRACT,date_part

EXTRACT(field FROMsource)

Theextract function retrieves subfields such as year or hour from date/time values.source must be a value expression of typetimestamp,time, orinterval. (Expressions of typedate are cast totimestamp and can therefore be used as well.)field is an identifier or string that selects what field to extract from the source value. Theextract function returns values of typedouble precision. The following are valid field names:

Theextract function is primarily intended for computational processing. For formatting date/time values for display, seeSection 9.8.

Thedate_part function is modeled on the traditionalIngres equivalent to theSQL-standard functionextract:

date_part('field',source)

Note that here thefield parameter needs to be a string value, not a name. The valid field names fordate_part are the same as forextract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');Result:16SELECT date_part('hour', INTERVAL '4 hours 3 minutes');Result:4

9.9.2.date_trunc

The functiondate_trunc is conceptually similar to thetrunc function for numbers.

date_trunc('field',source)

source is a value expression of typetimestamp orinterval. (Values of typedate andtime are cast automatically totimestamp orinterval, respectively.)field selects to which precision to truncate the input value. The return value is of typetimestamp orinterval with all fields that are less significant than the selected one set to zero (or one, for day and month).

Valid values forfield are:

Examples:

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

9.9.3.AT TIME ZONE

TheAT TIME ZONE construct allows conversions of time stamps to different time zones.Table 9-29 shows its variants.

In these expressions, the desired time zonezone can be specified either as a text string (e.g.,'PST') or as an interval (e.g.,INTERVAL '-08:00'). In the text case, a time zone name can be specified in any of the ways described inSection 8.5.3.

Examples (assuming the local time zone isPST8PDT):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';Result:2001-02-16 19:38:40-08SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';Result:2001-02-16 18:38:40

The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

The functiontimezone(zone,timestamp) is equivalent to the SQL-conforming constructtimestamp AT TIME ZONEzone.

9.9.4. Current Date/Time

PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:

CURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPCURRENT_TIME(precision)CURRENT_TIMESTAMP(precision)LOCALTIMELOCALTIMESTAMPLOCALTIME(precision)LOCALTIMESTAMP(precision)

CURRENT_TIME andCURRENT_TIMESTAMP deliver values with time zone;LOCALTIME andLOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME,CURRENT_TIMESTAMP,LOCALTIME, andLOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

Some examples:

SELECT CURRENT_TIME;Result:14:39:53.662522-05SELECT CURRENT_DATE;Result:2001-12-23SELECT CURRENT_TIMESTAMP;Result:2001-12-23 14:39:53.662522-05SELECT CURRENT_TIMESTAMP(2);Result:2001-12-23 14:39:53.66-05SELECT LOCALTIMESTAMP;Result:2001-12-23 14:39:53.662522

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the"current" time, so that multiple modifications within the same transaction bear the same time stamp.

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:

transaction_timestamp()statement_timestamp()clock_timestamp()timeofday()now()

transaction_timestamp() is equivalent toCURRENT_TIMESTAMP, but is named to clearly reflect what it returns.statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client).statement_timestamp() andtransaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands.clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.timeofday() is a historicalPostgreSQL function. Likeclock_timestamp(), it returns the actual current time, but as a formattedtext string rather than atimestamp with time zone value.now() is a traditionalPostgreSQL equivalent totransaction_timestamp().

All the date/time data types also accept the special literal valuenow to specify the current date and time (again, interpreted as the transaction start time). Thus, the following three all return the same result:

SELECT CURRENT_TIMESTAMP;SELECT now();SELECT TIMESTAMP 'now';  -- incorrect for use with DEFAULT

9.9.5. Delaying Execution

The following functions are available to delay execution of the server process:

pg_sleep(seconds)pg_sleep_for(interval)pg_sleep_until(timestamp with time zone)

pg_sleep makes the current session's process sleep untilseconds seconds have elapsed.seconds is a value of typedouble precision, so fractional-second delays can be specified.pg_sleep_for is a convenience function for larger sleep times specified as aninterval.pg_sleep_until is a convenience function for when a specific wake-up time is desired. For example:

SELECT pg_sleep(1.5);SELECT pg_sleep_for('5 minutes');SELECT pg_sleep_until('tomorrow 03:00');