Date Functions
Version dev
Version:
FunctionDescriptionExampleResultAliases

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

Search Shortcut cmd + k | ctrl + k