age( timestamp , timestamp )
| Subtract arguments, resulting in the time difference between the two timestamps | age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20')
| 8 years 6 months 20 days
| |
century( ts )
| Extract the century component from a date or timestamp | century(timestamp '2021-08-03 11:59:44.123456')
| 21
| |
current_date( )
| Returns the current date | current_date()
| 2023-07-23
| today
|
date_diff( part , startdate , enddate )
| The number of partition boundaries between the timestamps | date_diff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
| 2
| datediff
|
date_part( ts )
| Get subfield (equivalent to extract) | date_part('minute', TIMESTAMP '1992-09-20 20:38:40')
| 38
| datepart
|
date_sub( part , startdate , enddate )
| The number of complete partitions between the timestamps | date_sub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
| 1
| datesub
|
date_trunc( part , timestamp )
| Truncate to specified precision | date_trunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')
| 1992-09-20 20:00:00+00
| datetrunc
|
day( ts )
| Extract the day component from a date or timestamp | day(timestamp '2021-08-03 11:59:44.123456')
| 3
| |
dayname( ts )
| The (English) name of the weekday | dayname(TIMESTAMP '1992-03-22')
| Sunday
| |
dayofmonth( ts )
| Extract the dayofmonth component from a date or timestamp | dayofmonth(timestamp '2021-08-03 11:59:44.123456')
| 3
| |
dayofweek( ts )
| Extract the dayofweek component from a date or timestamp | dayofweek(timestamp '2021-08-03 11:59:44.123456')
| 2
| |
dayofyear( ts )
| Extract the dayofyear component from a date or timestamp | dayofyear(timestamp '2021-08-03 11:59:44.123456')
| 215
| |
decade( ts )
| Extract the decade component from a date or timestamp | decade(timestamp '2021-08-03 11:59:44.123456')
| 202
| |
epoch( temporal )
| Extract the epoch component from a temporal type | epoch(timestamp '2021-08-03 11:59:44.123456')
| 1627991984.123456
| |
epoch_ms( temporal )
| Extract the epoch component in milliseconds from a temporal type | epoch_ms(timestamp '2021-08-03 11:59:44.123456')
| 1627991984123
| |
epoch_ns( temporal )
| Extract the epoch component in nanoseconds from a temporal type | epoch_ns(timestamp '2021-08-03 11:59:44.123456')
| 1627991984123456000
| |
epoch_us( temporal )
| Extract the epoch component in microseconds from a temporal type | epoch_us(timestamp '2021-08-03 11:59:44.123456')
| 1627991984123456
| |
era( ts )
| Extract the era component from a date or timestamp | era(timestamp '2021-08-03 11:59:44.123456')
| 1
| |
get_current_time( )
| Returns the current time | get_current_time()
| 14:04:22.524
| |
get_current_timestamp( )
| Returns the current timestamp | get_current_timestamp()
| 2023-07-23 14:04:22.538+00
| now , transaction_timestamp
|
hour( ts )
| Extract the hour component from a date or timestamp | hour(timestamp '2021-08-03 11:59:44.123456')
| 11
| |
isodow( ts )
| Extract the isodow component from a date or timestamp | isodow(timestamp '2021-08-03 11:59:44.123456')
| 2
| |
isoyear( ts )
| Extract the isoyear component from a date or timestamp | isoyear(timestamp '2021-08-03 11:59:44.123456')
| 2021
| |
julian( ts )
| Extract the Julian Day number from a date or timestamp | julian(timestamp '2006-01-01 12:00')
| | |
last_day( ts )
| Returns the last day of the month | last_day(TIMESTAMP '1992-03-22 01:02:03.1234')
| 1992-03-31
| |
make_date( year , month , day )
| The date for the given parts | make_date(1992, 9, 20)
| 1992-09-20
| |
make_time( hour , minute , seconds )
| The time for the given parts | make_time(13, 34, 27.123456)
| 13:34:27.123456
| |
make_timestamp( year , month , day , hour , minute , seconds )
| The timestamp for the given parts | make_timestamp(1992, 9, 20, 13, 34, 27.123456)
| 1992-09-20 13:34:27.123456
| |
microsecond( ts )
| Extract the microsecond component from a date or timestamp | microsecond(timestamp '2021-08-03 11:59:44.123456')
| 44123456
| |
millennium( ts )
| Extract the millennium component from a date or timestamp | millennium(timestamp '2021-08-03 11:59:44.123456')
| 3
| |
millisecond( ts )
| Extract the millisecond component from a date or timestamp | millisecond(timestamp '2021-08-03 11:59:44.123456')
| 44123
| |
minute( ts )
| Extract the minute component from a date or timestamp | minute(timestamp '2021-08-03 11:59:44.123456')
| 59
| |
month( ts )
| Extract the month component from a date or timestamp | month(timestamp '2021-08-03 11:59:44.123456')
| 8
| |
monthname( ts )
| The (English) name of the month | monthname(TIMESTAMP '1992-09-20')
| September
| |
quarter( ts )
| Extract the quarter component from a date or timestamp | quarter(timestamp '2021-08-03 11:59:44.123456')
| 3
| |
second( ts )
| Extract the second component from a date or timestamp | second(timestamp '2021-08-03 11:59:44.123456')
| 44
| |
strftime( text , format )
| Converts timestamp to string according to the format string | strftime(timestamp '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p')
| Wed, 1 January 1992 - 08:38:40 PM
| |
strptime( text , format )
| Converts string to timestamp with time zone according to the format string if %Z is specified | strptime('Wed, 1 January 1992 - 08:38:40 PST', '%a, %-d %B %Y - %H:%M:%S %Z')
| 1992-01-01 16:38:40+00
| |
time_bucket( bucket_width , timestamp , origin )
| Truncate TIMESTAMPTZ by the specified interval bucket_width. Buckets are aligned relative to origin TIMESTAMPTZ. The origin defaults to 2000-01-03 00:00:00+00 for buckets that do not include a month or year interval, and to 2000-01-01 00:00:00+00 for month and year buckets | time_bucket(INTERVAL '2 weeks', TIMESTAMP '1992-04-20 15:26:00-07', TIMESTAMP '1992-04-01 00:00:00-07')
| 1992-04-15 07:00:00
| |
timezone( ts )
| Extract the timezone component from a date or timestamp | timezone(timestamp '2021-08-03 11:59:44.123456')
| 0
| |
timezone_hour( ts )
| Extract the timezone_hour component from a date or timestamp | timezone_hour(timestamp '2021-08-03 11:59:44.123456')
| 0
| |
timezone_minute( ts )
| Extract the timezone_minute component from a date or timestamp | timezone_minute(timestamp '2021-08-03 11:59:44.123456')
| 0
| |
to_days( integer )
| Construct a day interval | to_days(5)
| 5 days
| |
to_hours( integer )
| Construct a hour interval | to_hours(5)
| 05:00:00
| |
to_microseconds( integer )
| Construct a microsecond interval | to_microseconds(5)
| 00:00:00.000005
| |
to_milliseconds( integer )
| Construct a millisecond interval | to_milliseconds(5)
| 00:00:00.005
| |
to_minutes( integer )
| Construct a minute interval | to_minutes(5)
| 00:05:00
| |
to_months( integer )
| Construct a month interval | to_months(5)
| 5 months
| |
to_seconds( integer )
| Construct a second interval | to_seconds(5)
| 00:00:05
| |
to_timestamp( sec )
| Converts secs since epoch to a timestamp with time zone | to_timestamp(1284352323.5)
| 2010-09-13 04:32:03.5+00
| |
to_years( integer )
| Construct a year interval | to_years(5)
| 5 years
| |
try_strptime( text , format )
| Converts string to timestamp using the format string (timestamp with time zone if %Z is specified). Returns NULL on failure | try_strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p')
| 1992-01-01 20:38:40
| |
week( ts )
| Extract the week component from a date or timestamp | week(timestamp '2021-08-03 11:59:44.123456')
| 31
| |
weekday( ts )
| Extract the weekday component from a date or timestamp | weekday(timestamp '2021-08-03 11:59:44.123456')
| 2
| |
weekofyear( ts )
| Extract the weekofyear component from a date or timestamp | weekofyear(timestamp '2021-08-03 11:59:44.123456')
| 31
| |
year( ts )
| Extract the year component from a date or timestamp | year(timestamp '2021-08-03 11:59:44.123456')
| 2021
| |
yearweek( ts )
| Extract the yearweek component from a date or timestamp | yearweek(timestamp '2021-08-03 11:59:44.123456')
| 202131
| |