23rd September 2021: PostgreSQL 14 RC 1 Released!
Unsupported versions: 6.5
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

Date/Time Functions

The date/time functions provide a powerful set of tools for manipulating various date/time types.

Table 5-5. Date/Time Functions

Function Returns Description Example
abstime(datetime) abstime convert to abstime abstime('now'::datetime)
age(datetime,datetime) timespan preserve months and years age('now','1957-06-13'::datetime)
datetime(abstime) datetime convert to datetime datetime('now'::abstime)
datetime(date) datetime convert to datetime datetime('today'::date)
datetime(date,time) datetime convert to datetime datetime('1998-02-24'::datetime, '23:07'::time);
date_part(text,datetime) float8 portion of date date_part('dow','now'::datetime)
date_part(text,timespan) float8 portion of time date_part('hour','4 hrs 3 mins'::timespan)
date_trunc(text,datetime) datetime truncate date date_trunc('month','now'::abstime)
isfinite(abstime) bool a finite time? isfinite('now'::abstime)
isfinite(datetime) bool a finite time? isfinite('now'::datetime)
isfinite(timespan) bool a finite time? isfinite('4 hrs'::timespan)
reltime(timespan) reltime convert to reltime reltime('4 hrs'::timespan)
timespan(reltime) timespan convert to timespan timespan('4 hours'::reltime)

For the date_part and date_trunc functions, arguments can be `year', `month', `day', `hour', `minute', and `second', as well as the more specialized quantities `decade', `century', `millenium', `millisecond', and `microsecond'. date_part allows `dow' to return day of week and `epoch' to return seconds since 1970 (for datetime) or 'epoch' to return total elapsed seconds (for timespan).