Table 9-26 shows the available
functions for date/time value processing, with details appearing in
the following subsections. Table 9-25 illustrates the behaviors of
the basic arithmetic operators (+,
*, etc.). For formatting functions, refer to
Section 9.7. You should be familiar with
the background information on date/time data types from Section 8.5.

All the functions and operators described below that take time or timestamp
inputs actually come in two variants: one that takes time with time zone or timestamp
with time zone, and one that takes time without time zone or timestamp without time zone.
For brevity, these variants are not shown separately.

This expression yields true when two time periods (defined by their
endpoints) overlap, false when they do not overlap. The endpoints
can be specified as pairs of dates, times, or time stamps; or as
a date, time, or time stamp followed by an interval.

The extract function retrieves subfields
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 field names:

The time zone offset from UTC, measured in seconds. Positive values
correspond to time zones east of UTC, negative values to
zones west of UTC.

timezone_hour

The hour component of the time zone offset

timezone_minute

The minute component of the time zone offset

week

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-8601
week starts on Monday.) In other words, the first Thursday of
a year is in week 1 of that year. (for timestamp values only)

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 or interval.
(Values of type date and
time are cast automatically, to timestamp or
interval respectively.)
field selects to which precision to
truncate the input value. The return value is of type
timestamp or interval
with all fields that are less significant than the
selected one set to zero (or one, for day and month).

The first example takes a zone-less time stamp and interprets it as MST time
(UTC-7) to produce a UTC time stamp, which is then rotated 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 function timezone(zone,
timestamp) is equivalent to the SQL-conforming construct
timestamp AT TIME ZONE
zone.

CURRENT_TIME and
CURRENT_TIMESTAMP deliver values with time zone;
LOCALTIME and
LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME,
CURRENT_TIMESTAMP,
LOCALTIME, and
LOCALTIMESTAMP
can optionally be given
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.

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

The function now() is the traditional
PostgreSQL equivalent to
CURRENT_TIMESTAMP.

There is also the function timeofday(), which for historical
reasons returns a text string rather than a timestamp value:

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

It is important to know that
CURRENT_TIMESTAMP and related 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. timeofday()
returns the wall-clock time and does advance during transactions.

Note: Other database systems may advance these values more
frequently.

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
clause 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. Thus they will give the desired
behavior of defaulting to the time of row insertion.