General Usage Dates and Time Expressions

Common Expression Values for date/time functions

Common Expression Values for date/time functions

Analyze ExpressionReturn TypeDescriptionExampleResult
func.age(timestamp, timestamp)intervalSubtract arguments, producing a “symbolic” result that uses years and monthsage (timestamp ‘2001-04-1 0’, timestamp ‘1957-06-1 3’)43 years 9 months 27 days
func.age(timestamp)intervalSubtract from current_dateage(timestamp ‘1957-06-1 3’)43 years 8 months 3 days
func.clock_timestamp()timestamp with time zoneCurrent date and time (changes during statement execution)
func.current_date()dateCurrent date
func.current_time()time with time zoneCurrent time of day
func.current_timestamptimestamp with time zoneCurrent date and time (start of current transaction)
func.date_part(text, timestamp)double precisionGet subfield (equivalent to extract)date_part (‘hour’, timestamp ‘2001-02-1 6 20:38:40’)20
func.date_part(text, interval)double precisionGet subfield (equivalent to extract)date_part (‘month’, interval ‘2 years 3 months’)3
func.date_trunc(text, timestamp)timestampTruncate to specified precisiondate_trunc(‘hour’, timestamp ‘2001-02-1 6 20:38:40’)36938.8333333333
func.extract(field from timestamp)double precisionGet subfieldextract(hour from timestamp ‘2001-02-1 6 20:38:40’)20
func.extract(field from interval)double precisionGet subfieldextract(month from interval ‘2 years 3 months’)3
func.isfinite(timestamp)booleanTest for finite time stamp (not equal to infinity)isfinite(timestamp ‘2001-02-1 6 21:28:30’)TRUE
func.isfinite(interval)booleanTest for finite intervalisfinite(interval ‘4 hours’)TRUE
func.justify_days(interval)intervalAdjust interval so 30-day time periods are represented as monthsjustify_days(interval ‘30 days’)1 month
func.justify_hours(interval)intervalAdjust interval so 24-hour time periods are represented as daysjustify_hours(interval ‘24 hours’)1 day
func.justify_interval(interval)intervalAdjust interval using justify_days and justify_hours, with additional sign adjustmentsjustify_interval(interval ‘1 mon -1 hour’)29 days 23:00:00
func.now()timestamp with time zoneCurrent date and time (start of current transaction)
func.statement_timestamp()timestamp with time zoneCurrent date and time (start of current statement)
func.timeofday()textCurrent date and time (like clock_timestamp, but as a text string)
func.transaction_timestamp()timestamp with time zoneCurrent date and time (start of current transaction)

Date and Time Formatting Directives

PatternDescription
HHhour of day (01-12)
HH12hour of day (01-12)
HH24hour of day (00-23)
MIminute (00-59)
SSsecond (00-59)
MSmillisecond (000-999)
USmicrosecond (000000-999999 )
SSSSseconds past midnight (0-86399)
AM or A.M. or PM or P.M.meridian indicator (uppercase)
am or a.m. or pm or p.m.meridian indicator (lowercase)
Y,YYYyear (4 and more digits) with comma
YYYYyear (4 and more digits)
YYYlast 3 digits of year
YYlast 2 digits of year
Ylast digit of year
IYYYISO year (4 and more digits)
IYYlast 3 digits of ISO year
IYlast 2 digits of ISO year
Ilast digits of ISO year
BC or B.C. or AD or A.D.era indicator (uppercase)
bc or b.c. or ad or a.d.era indicator (lowercase)
MONTHfull uppercase month name (blank-padded to 9 chars)
Monthfull mixed-case month name (blank-padded to 9 chars)
monthfull lowercase month name (blank-padded to 9 chars)
MONabbreviated uppercase month name (3 chars)
Monabbreviated mixed-case month name (3 chars)
monabbreviated lowercase month name (3 chars)
MMmonth number (01-12)
DAYfull uppercase day name (blank-padded to 9 chars)
Dayfull mixed-case day name (blank-padded to 9 chars)
dayfull lowercase day name (blank-padded to 9 chars)
DYabbreviated uppercase day name (3 chars)
Dyabbreviated mixed-case day name (3 chars)
dyabbreviated lowercase day name (3 chars)
DDDday of year (001-366)
DDday of month (01-31)
Dday of week (1-7; Sunday is 1)
Wweek in month (1-5) (The first week starts on the first day of the month.)
WWweek number in year (1-53) (The first week starts on the first day of the year.)
IWISO week number of year (The first Thursday of the new year is in week 1.)
CCcentury (2 digits)
JJulian Day (days since January 1, 4712 BC)
Qquarter
RMmonth in Roman numerals (I-XII; I=January) (uppercase)
rmmonth in Roman numerals (i-xii; i=January) (lowercase)
TZtime-zone name (uppercase)
tztime-zone name (lowercase)
Last modified November 27, 2023 at 12:56 PM EST: Restructured the file structure/a few changes (f6c58b8)