Dates and Time Expressions

Home/Dates and Time Expressions

Dates and Time Expressions

Analyze ExpressionReturn TypeDescriptionExampleResult
func.age(timestamp, timestamp)intervalSubtract arguments, producing a “symbolic” result that uses years and monthsage (timest amp ‘2001-04-1 0’, timestamp ‘1957-06-1 3’)43 years 9 months 27 days
func.age(timestamp)intervalSubtract from current_dateage(timest amp ‘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_datedateCurrent date  
func.current_timetime with time zoneCurrent time of day  
func.current_timesta mptimestamp 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_trun c(‘hour’, timestamp ‘2001-02-1 6 20:38:40’)36938.833 3333333
func.extract(field from timestamp)double precisionGet subfieldextract(ho ur from timestamp ‘2001-02-1 6 20:38:40’)20
func.extract(field from interval)double precisionGet subfieldextract(mo nth from interval ‘2 years 3 months’)3
func.isfinite(timesta mp)booleanTest for finite time stamp (not equal to infinity)isfinite(t imestamp ‘2001-02-1 6 21:28:30’)TRUE
func.isfinite(interva l)booleanTest for finite intervalisfinite(i nterval ‘4 hours’)TRUE
func.justify_days(in terval)intervalAdjust interval so 30-day time periods are represented as monthsjustify_d ays(interv al ‘30 days’)1 month
func.justify_hours(i nterval)intervalAdjust interval so 24-hour time periods are represented as daysjustify_h ours(inter val ‘24 hours’)1 day
func.justify_interva l(interval)intervalAdjust interval using justify_days and justify_hours , with additional sign adjustmentsjustify_i nterval(in terval ‘1 mon -1 hour’)29 days 23:00:00
func.now()timestamp with time zoneCurrent date and time (start of current transaction)  
func.statement_times tamp()timestamp with time zoneCurrent date and time (start of current statement)  
func.timeofday()textCurrent date and time (like clock_timesta mp, but as a text string)  
func.transaction_tim estamp()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)

Go to Top