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
| |
