Table 4-17 shows the available
functions for date/time value processing.
Table 4-16 illustrates the
behaviors of the basic arithmetic
operators (`+`, `*`, etc.).
For formatting functions, refer to Section 4.7. You should be familiar with the
background information on date/time data types (see Section 3.5).

The date/time operators described below behave similarly for types involving time zones as well as those without.

**Table 4-16. Date/Time Operators**

Name | Example | Result |
---|---|---|

+ | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00' |

+ | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00' |

+ | time '01:00' + interval '3 hours' | time '04:00' |

- | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28' |

- | date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00' |

- | time '05:00' - interval '2 hours' | time '03:00' |

- | interval '2 hours' - time '05:00' | time '03:00:00' |

* | interval '1 hour' * int '3' | interval '03:00' |

/ | interval '1 hour' / int '3' | interval '00:20' |

The date/time functions are summarized below, with additional details in subsequent sections.

**Table 4-17. 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; see below | ||

current_time | time | Time of day; see below | ||

current_timestamp | timestamp | Date and time; see below | ||

date_part(text, timestamp) | double precision | Get subfield (equivalent to
extract); see also below
| date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |

date_part(text, interval) | double precision | Get subfield (equivalent to
extract); see also below
| date_part('month', interval '2 years 3 months') | 3 |

date_trunc(text, timestamp) | timestamp | Truncate to specified precision; see also below | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00+00 |

extract( from fieldtimestamp) | double precision | Get subfield; see also below | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |

extract( from fieldinterval) | double precision | Get subfield; see also below | 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 to
current_timestamp); see below
| ||

timeofday() | text | Current date and time; see below | 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 timestamp | timestamp(date '1998-02-24',time '23:07') | 1998-02-24 23:07:00 |

EXTRACT (FROMfield)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

`century`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.

`day`The day (of the month) field (1 - 31)

`decade`The year field divided by 10

`dow`The day of the week (0 - 6; Sunday is 0) (for

`timestamp`values only)`doy`The day of the year (1 - 365/366) (for

`timestamp`values only)`epoch`For

`date`and`timestamp`values, the number of seconds since 1970-01-01 00:00:00-00 (Result may be negative.); for`interval`values, the total number of seconds in the interval`hour`The hour field (0 - 23)

`microseconds`The seconds field, including fractional parts, multiplied by 1 000 000. Note that this includes full seconds.

`millennium`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.

`milliseconds`The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

`minute`The minutes field (0 - 59)

`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)`quarter`The quarter of the year (1 - 4) that the day is in (for

`timestamp`values only)`second`The seconds field, including fractional parts (0 - 59[1])

`timezone_hour`The hour component of the time zone offset.

`timezone_minute`The minute component of the time zone offset.

`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.`year`The year field

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

The function `date_trunc` is conceptually
similar to the `trunc` function for numbers.

date_trunc('',field)source

` source` is a value expression of type

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 () CURRENT_TIMESTAMP (precision)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.

Note:Prior to PostgreSQL 7.2, the precision parameters were unimplemented, and the result was always given in integer seconds.

Note: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-05SELECT CURRENT_DATE;2001-12-23SELECT CURRENT_TIMESTAMP;2001-12-23 14:39:53.662522-05SELECT 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';

Note:You do not want to use the third form when specifying a DEFAULT value while creating a table. The system will convertnowto atimestampas 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 |