Table 4-14 shows the available functions for date/time value processing. The basic arithmetic operators (+,*, etc.) are also available. For formatting functions, refer toSection 4.6. You should be familiar with the background information on date/time data types (seeSection 3.4).
Table 4-14. Date/Time Functions
| Name | Return Type | Description | Example | Result |
|---|---|---|---|---|
| age(timestamp) | interval | Subtract from today | age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
| age(timestamp, timestamp) | interval | Subtract arguments | age('2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
| current_date | date | Today's date; seebelow | ||
| current_time | time | Time of day; seebelow | ||
| current_timestamp | timestamp | date and time; see alsobelow | ||
| date_part(text, timestamp) | double precision | Get subfield (equivalent toextract); see alsobelow | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
| date_part(text, interval) | double precision | Get subfield (equivalent toextract); see alsobelow | date_part('month', interval '2 years 3 months') | 3 |
| date_trunc(text, timestamp) | timestamp | Truncate to specified precision; see alsobelow | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00+00 |
| extract(field from timestamp) | double precision | Get subfield; see alsobelow | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
| extract(field from interval) | double precision | Get subfield; see alsobelow | extract(month from interval '2 years 3 months') | 3 |
| isfinite(timestamp) | boolean | Test for finite time stamp (neither invalid nor infinity) | isfinite(timestamp '2001-02-16 21:28:30') | true |
| isfinite(interval) | boolean | Test for finite interval | isfinite(interval '4 hours') | true |
| now() | timestamp | Current date and time (equivalent tocurrent_timestamp); see alsobelow | ||
| timeofday() | text | High-precision date and time; see alsobelow | timeofday() | Wed Feb 21 17:01:13.000126 2001 EST |
| timestamp(date) | timestamp | Date to timestamp | timestamp(date '2000-12-25') | 2000-12-25 00:00:00 |
| timestamp(date, time) | timestamp | Date and time to a timestamp | timestamp(date '1998-02-24',time '23:07') | 1998-02-24 23:07:00 |
EXTRACT (field FROMsource)
Theextract function retrieves sub-fields from date/time values, such as year or hour.source is a value expression that evaluates to typetimestamp orinterval. (Expressions of typedate ortime will be 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 values:
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.
The day (of the month) field (1 - 31)
The year field divided by 10
The day of the week (0 - 6; Sunday is 0) (fortimestamp values only)
The day of the year (1 - 365/366) (fortimestamp values only)
Fordate andtimestamp values, the number of seconds since 1970-01-01 00:00:00 (Result may be negative.); forinterval 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)
Fortimestamp values, the number of the month within the year (1 - 12) ; forinterval values the number of months, modulo 12 (0 - 11)
The quarter of the year (1 - 4) that the day is in (fortimestamp values only)
The seconds field, including fractional parts (0 - 59[1])
From atimestamp 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. (TheISO week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year.
The year field
Theextract function is primarily intended for computational processing. For formatting date/time values for display, seeSection 4.6.
Thedate_part function is modeled on the traditionalIngres equivalent to theSQL-functionextract:
date_part('field',source)Note that here thefieldvalue needs to be a string. The valid field values fordate_part are the same as forextract.The functiondate_trunc is conceptually similar to thetrunc function for numbers.
date_trunc('field',source)source is a value expressionof typetimestamp (values of typedate andtime are cast automatically).field selects to which precision to truncate the time stamp value. The return value is of typetimestamp with all fields that are less than the selected one set to zero (or one, for day and month).Valid values forfield 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_TIMECURRENT_DATECURRENT_TIMESTAMPNote that because of the requirements of theSQL standard, these functions must not be calledwith trailing parentheses.
SELECT CURRENT_TIME;19:07:32SELECT CURRENT_DATE;2001-02-17SELECT CURRENT_TIMESTAMP;2001-02-17 19:07:32-05
The functionnow() is the traditionalPostgres equivalent toCURRENT_TIMESTAMP.
There is alsotimeofday(), which returns current time to higher precision than theCURRENT_TIMESTAMP family does:
timeofday() uses the operating system callgettimeofday(2), which may have resolution as good as microseconds (depending on your platform); the other functions rely ontime(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 thatCURRENT_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. Buttimeofday() returns the actual current time.
All the date/time datatypes also accept the special literal valuenow to specify the current date and time. Thus, the following three all return the same result:
SELECT CURRENT_TIMESTAMP;SELECT now();SELECT TIMESTAMP 'now';
Note: You do not want to use the third form when specifying a DEFAULT value while creating a table. The system will convertnow 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. Thus they will give the desired behavior of defaulting to the time of row insertion.
| [1] | 60 if leap seconds are implemented by the operating system |