Table 3-18 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).
The date/time operators described below behave similarly for types involving time zones as well as those without.
Table 3-17. Date/Time Operators
Name | Example | Result |
---|---|---|
+ | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00:00-04' |
+ | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00:00-04' |
+ | time '01:00' + interval '3 hours' | time '04:00:00' |
+ | date '2001-09-28' + 1 | date '2001-09-29' |
- | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00:00-04' |
- | date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00:00-04' |
- | time '05:00' - interval '2 hours' | time '03:00:00' |
- | interval '2 hours' - time '05:00' | time '03:00:00' |
- | date '2001-09-28' + 1 | date '2001-09-29' |
* | interval '1 hour' * int '3' | interval '03:00' |
/ | interval '1 hour' / int '3' | interval '00:20' |
The order of the operands matters. For instance, it is valid to add an interval (right operand) to a date (left operand), but not the reverse. Similarly, you can add an integer to a date, but not a date to an integer, and so on. |
The date/time functions are summarized below, with additional details in subsequent sections.
Table 3-18. Date/Time Functions
Name and Return Type | Description | Example and Result |
---|---|---|
current_date returns date | Today's date; see below | |
current_time returns time | Time of day; see below | |
current_timestamp returns timestamp | Date and time; see below | |
date_part(text, timestamp) returns double precision | Get subfield (equivalent to extract ); see also below | date_part('hour', timestamp '2001-02-16 20:38:40') returns 20 |
date_part(text, interval) returns double precision | Get subfield (equivalent to extract ); see also below | date_part('month', interval '2 years 3 months') returns 3 |
date_trunc(text, timestamp) returns timestamp | Truncate to specified precision; see also below | date_trunc('hour', timestamp '2001-02-16 20:38:40') returns 2001-02-16 20:00:00+00 |
extract(field from timestamp) returns double precision | Get subfield; see also below | extract(hour from timestamp '2001-02-16 20:38:40') returns 20 |
extract(field from interval) returns double precision | Get subfield; see also below | extract(month from interval '2 years 3 months') returns 3 |
isfinite(timestamp) returns boolean | Test for finite time stamp (neither invalid nor infinity) | isfinite(timestamp '2001-02-16 21:28:30') returns true |
isfinite(interval) returns boolean | Test for finite interval | isfinite(interval '4 hours') returns true |
now() returns timestamp | Current date and time (equivalent to current_timestamp ); see below | |
timeofday() returns text | Current date and time; see below | timeofday() returns Wed Feb 21 17:01:13.000126 2001 EST |
timestamp(date) returns timestamp | date to timestamp | timestamp(date '2000-12-25') returns 2000-12-25 00:00:00 |
timestamp(date, time) returns timestamp | date and time to timestamp | timestamp(date '1998-02-24',time '23:07') returns 1998-02-24 23:07:00 |
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:
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 considers most years in the 1900's to be in the twentieth century.
The day (of the month) field (1 - 31)
The year field divided by 10
The day of the week (0 - 6; Sunday is 0) (for timestamp values only)
Note that dow is available for timestamp values only.
The day of the year (1 - 365/366) (for timestamp values only)
Note that doy is available for timestamp values only.
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
The hour field (0 - 23)
The seconds field, including fractional parts, multiplied by 1,000,000. Note that this includes full seconds.
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.
The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.
The minutes field (0 - 59)
For timestamp values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11)
The quarter of the year (1 - 4) that the day is in (for timestamp values only).
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
The hour component of the time zone offset.
The minute component of the time zone offset.
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.
The year field
The date_part function is the traditional PostgreSQL equivalent to the SQL-function extract :
date_part('field', source) |
The function date_trunc is conceptually similar to the trunc function for numbers.
date_trunc('field', source) |
Valid values for field are:
microseconds |
milliseconds |
second |
minute |
hour |
day |
month |
year |
decade |
century |
millennium |
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 ) |
Prior to PostgreSQL 7.2, the precision parameters were unimplemented, and the result was always given in integer seconds. |
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. |
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:
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'; |
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. |