Date and time functions ​

Date and time functions construct values, extract calendar fields, truncate or bin timestamps, convert timezones and format values as text.

Use Date/Date32 for calendar days, DateTime/DateTime64 for instants and Time/Time64 for date-independent seconds. Calendar arithmetic and elapsed-second arithmetic have different timezone behavior; each function below states its accepted inputs and result type.

Example: Constructing a date and reading a field ​

SELECT make_date(2025, 3, 15), date_part('year', make_date(2025, 3, 15))
FORMAT csv;

"2025-03-15",2025.0

age ​

Subtract UTC calendar fields, retaining symbolic year/month/day and clock components in a named Tuple.

Syntax ​

age(later, earlier) or age(value)

Arguments ​

Native dates/timestamps or ISO text; one argument subtracts from current UTC midnight; partial-month days use the earlier month.

Returned value ​

Named Tuple(years,months,days,hours,minutes,seconds); seconds is exact Decimal(18,9); NULL propagates. This is not a PostgreSQL Interval.

Example ​

SELECT tuple_element(age('2004-06-01','2004-04-30'),'months') FORMAT csv;

1

clock_timestamp ​

Read the changing wall clock at the instant of this call.

Syntax ​

clock_timestamp()

Arguments ​

No arguments.

Returned value ​

Native UTC DateTime64(6), read when the function is called. Calls within one statement can return different instants.

Example ​

SELECT clock_timestamp() IS NOT NULL FORMAT csv;

1

date_add ​

Add calendar months/days and elapsed seconds to an instant.

Syntax ​

date_add(timestamp,interval[,timezone])

Arguments ​

Native timestamp or ISO calendar text; single-unit native interval/text or make_interval tuple. Month ends clamp; DST folds use later instants and gaps use pre-transition offsets.

Returned value ​

Native DateTime64 at least microsecond precision in the calculation zone; SQL NULL propagates.

Example ​

SELECT to_char(date_add('2024-01-31 10:00:00','1 month'),'YYYY-MM-DD HH24:MI:SS') FORMAT csv;

"2024-02-29 10:00:00"

date_bin ​

Return the beginning of a positive elapsed-time bin aligned to an origin, flooring values before that origin.

Syntax ​

date_bin(stride, source, origin)

Arguments ​

Positive native Interval or quantity-unit text without calendar months; source/origin native dates, timestamps, or ISO calendar text.

Returned value ​

Native timestamp in the source zone, retaining source/origin/stride precision within native ranges; NULL propagates.

Example ​

SELECT to_char(date_bin('15 minutes','2020-02-11 15:44:17','2001-01-01'),'HH24:MI:SS') FORMAT csv;

"15:30:00"

date_part ​

Extract a numeric field from a date, timestamp, time, or interval.

Syntax ​

date_part(field, value)

Arguments ​

Fields include year, month, day, hour, minute, second, epoch, quarter, ISO week/day/year, and native timezone fields.

Returned value ​

REAL field value, or NULL for NULL input.

Example ​

SELECT date_part('year','2026-10-12') FORMAT csv;

2026.0

date_subtract ​

Subtract a calendar span from an instant.

Syntax ​

date_subtract(timestamp,interval[,timezone])

Arguments ​

Same calendar/elapsed and zone rules as date_add; component negation is checked.

Returned value ​

Native DateTime64 in the calculation zone; SQL NULL propagates.

Example ​

SELECT to_char(date_subtract('2024-03-31 10:00:00','1 month'),'YYYY-MM-DD') FORMAT csv;

"2024-02-29"

date_trunc ​

Truncate a date or timestamp to a calendar unit, or truncate a native interval.

Syntax ​

date_trunc(unit, value[, timezone])

Arguments ​

Calendar text uses UTC; native timestamps retain their zone unless overridden. Supported units range from microseconds through millennium.

Returned value ​

Native timestamp retaining fractional precision and the selected zone; native interval for interval input; NULL propagates.

Example ​

SELECT to_char(date_trunc('month','2026-10-12'),'YYYY-MM-DD') FORMAT csv;

"2026-10-01"

jsonb_path_exists_tz ​

Test timezone-aware JSONPath selection.

Syntax ​

jsonb_path_exists_tz(target,path[,vars[,silent]])

Arguments ​

Same bounded paths, object vars, error suppression and completed-prefix handling as ordinary JSONPath functions. Honor explicit offsets; implicit timezone conversions use native UTC. Query is FROM-only; first validates subsequent candidates. NULL propagates.

Returned value ​

Bool or NULL

Example ​

SELECT jsonb_path_exists_tz('["2015-08-01 12:00:00-05"]','$[*] ? (@.datetime() < "2015-08-02".datetime())') FORMAT csv;

true

jsonb_path_query_first_tz ​

Retain the first timezone-aware JSONPath item.

Syntax ​

jsonb_path_query_first_tz(target,path[,vars[,silent]])

Arguments ​

Same bounded paths, object vars, error suppression and completed-prefix handling as ordinary JSONPath functions. Honor explicit offsets; implicit timezone conversions use native UTC. Query is FROM-only; first validates subsequent candidates. NULL propagates.

Returned value ​

JSON or NULL

Example ​

SELECT jsonb_path_query_first_tz('["2015-08-01 12:00:00-05"]','$[*] ? (@.datetime() < "2015-08-02".datetime())') FORMAT csv;

"""2015-08-01 12:00:00-05"""

jsonb_path_query_tz ​

Expand timezone-aware JSONPath selections in FROM.

Syntax ​

jsonb_path_query_tz(target,path[,vars[,silent]])

Arguments ​

Same bounded paths, object vars, error suppression and completed-prefix handling as ordinary JSONPath functions. Honor explicit offsets; implicit timezone conversions use native UTC. Query is FROM-only; first validates subsequent candidates. NULL input yields no rows.

Returned value ​

JSON rows

Example ​

SELECT * FROM jsonb_path_query_tz('["2015-08-01 12:00:00-05"]','$[*] ? (@.datetime() < "2015-08-02".datetime())') FORMAT csv;

"""2015-08-01 12:00:00-05"""

justify_days ​

Normalize fixed thirty-day portions into months.

Syntax ​

justify_days(interval)

Arguments ​

Accepts a native/text single-unit interval or make_interval mixed span. Thirty days make one month; month/day signs normalize. This conversion does not imply calendar-duration equivalence.

Returned value ​

Named native months/days/seconds Tuple, retaining microsecond or nanosecond precision; NULL propagates.

Example ​

SELECT tuple_element(justify_days('65 days'),'months') FORMAT csv;

2

justify_hours ​

Normalize fixed twenty-four-hour portions into days.

Syntax ​

justify_hours(interval)

Arguments ​

Same native interval/span inputs as justify_days. Twenty-four elapsed hours make one day; day/second signs normalize.

Returned value ​

Named native months/days/seconds Tuple or SQL NULL.

Example ​

SELECT tuple_element(justify_hours('50 hours'),'days') FORMAT csv;

2

justify_interval ​

Normalize hours, days and mixed signs across a span.

Syntax ​

justify_interval(interval)

Arguments ​

Combines fixed twenty-four-hour day and thirty-day month normalization, making component signs agree. Widened checked arithmetic protects native range boundaries.

Returned value ​

Named native months/days/seconds Tuple or SQL NULL.

Example ​

SELECT tuple_element(justify_interval(make_interval(0,1,0,0,-1)),'days') FORMAT csv;

29

make_date ​

Construct a native Date32 from validated year/month/day fields.

Syntax ​

make_date(year, month, day)

Arguments ​

Signed integer fields; invalid dates error; native Date32 range applies.

Returned value ​

Native Date32, or NULL for a NULL field.

Example ​

SELECT to_char(make_date(2024,2,29),'YYYY-MM-DD') FORMAT csv;

"2024-02-29"

make_interval ​

Construct a mixed calendar span without approximating months or days as seconds.

Syntax ​

make_interval([years[,months[,weeks[,days[,hours[,minutes[,seconds]]]]]]])

Arguments ​

Positional arguments default zero. Integer components are checked; finite seconds round to microseconds with ties to even. Native mixed-span representation is a named Tuple.

Returned value ​

Tuple(months Int64,days Int64,seconds Decimal(38,6)); any explicit SQL NULL gives NULL.

Example ​

SELECT to_json(make_interval(1,2,0,3,4,5,6.25)) FORMAT csv;

"{""months"":14,""days"":3,""seconds"":14706.250000}"

make_time ​

Construct a native clock from hour/minute/second fields.

Syntax ​

make_time(hour,minute,seconds)

Arguments ​

Hour 0..24, minute 0..59, seconds 0..60; hour 24 requires zero minute/seconds. Fractional seconds round independently to microseconds.

Returned value ​

Native Time64(6); SQL NULL propagates.

Example ​

SELECT CAST(make_time(8,15,23.5) AS TEXT) FORMAT csv;

"08:15:23.500000"

make_timestamp ​

Construct a UTC native DateTime64(6), rounding seconds independently of clock offsets with ties to even and calendar carry.

Syntax ​

make_timestamp(year, month, day, hour, minute, seconds)

Arguments ​

Integer date/hour/minute fields and numeric seconds 0 through 60; hour 24 requires minute/seconds zero; native range applies.

Returned value ​

Native UTC timestamp with microsecond precision, or NULL for any NULL field.

Example ​

SELECT to_char(make_timestamp(2024,2,29,12,30,0.5),'HH24:MI:SS.US') FORMAT csv;

"12:30:00.500000"

make_timestamptz ​

Construct a native timestamp in an explicit display zone.

Syntax ​

make_timestamptz(year,month,day,hour,minute,seconds[,timezone])

Arguments ​

Default zone is UTC; explicit zones use IANA names. Same clock/date validation and ties-to-even microsecond rounding as make_timestamp. Gaps use the preceding offset and folds choose the later instant. Native ranges apply.

Returned value ​

Native DateTime64(6,zone) or SQL NULL.

Example ​

SELECT to_char(make_timestamptz(2024,1,1,12,0,0,'Europe/Moscow'),'HH24:MI:SS OF') FORMAT csv;

"12:00:00 +03:00"

now ​

Read the explicit transaction start or autocommit statement start.

Syntax ​

now()

Arguments ​

No arguments. Equivalent to transaction_timestamp using native UTC clock scopes.

Returned value ​

Native UTC DateTime64(6).

Example ​

SELECT now() IS NOT NULL FORMAT csv;

1

statement_timestamp ​

Read the fixed start of the enclosing SQL statement.

Syntax ​

statement_timestamp()

Arguments ​

No arguments.

Returned value ​

Native UTC DateTime64(6), fixed for the enclosing SQL statement.

Example ​

SELECT statement_timestamp() IS NOT NULL FORMAT csv;

1

timezone ​

Convert a native instant to an IANA display zone, or interpret zone-free ISO local text/date in that zone and return UTC.

Syntax ​

timezone(zone, value)

Arguments ​

IANA zone and native timestamp/date or ISO text; offset-bearing text identifies an instant; ambiguous/nonexistent local times error.

Returned value ​

Native timestamp retaining an instant and explicit display zone; NULL propagates. Native timestamps have no zone-free runtime class.

Example ​

SELECT to_char(timezone('Europe/Moscow',to_timestamp(0)),'HH24:MI:SS') FORMAT csv;

"03:00:00"

to_char ​

Format a calendar value or an exact numeric value using supported PostgreSQL template fields.

Syntax ​

to_char(value, template)

Arguments ​

Calendar fields include YYYY, MM, DD, MON, MONTH, HH24, MI, SS, MS, US, and timezone fields; numeric fields include 9, 0, D, G, S, MI and FM. Unsupported fields raise INVALID_ARGUMENT.

Returned value ​

Formatted TEXT with English calendar names and locale-independent punctuation; NULL propagates.

Example ​

SELECT to_char(12.345,'FM9990.00') FORMAT csv;

"12.35"

to_date ​

Parse calendar text with a supported PostgreSQL date template.

Syntax ​

to_date(value, template)

Arguments ​

Supported input fields include YYYY, YY, MM, DD, DDD, MON, MONTH, DY and DAY. Separators match the supplied template.

Returned value ​

Native date, or NULL for NULL input. Invalid dates and unsupported template fields raise INVALID_ARGUMENT.

Example ​

SELECT to_char(to_date('05 Dec 2000','DD Mon YYYY'),'YYYY-MM-DD') FORMAT csv;

"2000-12-05"

to_timestamp ​

Convert Unix epoch seconds or parse calendar text into a UTC timestamp.

Syntax ​

to_timestamp(epoch), to_timestamp(value, template)

Arguments ​

One numeric argument uses epoch seconds. Two arguments parse supported calendar fields including HH24, MI, SS, MS and US.

Returned value ​

Native UTC timestamp with microsecond precision; NULL propagates. Invalid templates or dates raise INVALID_ARGUMENT.

Example ​

SELECT to_char(to_timestamp(0),'YYYY-MM-DD HH24:MI:SS') FORMAT csv;

"1970-01-01 00:00:00"

transaction_timestamp ​

Read the fixed explicit BEGIN/outer SAVEPOINT start, or the statement start in autocommit.

Syntax ​

transaction_timestamp()

Arguments ​

No arguments.

Returned value ​

Native UTC DateTime64(6), fixed for the explicit transaction. In autocommit, it uses the current statement start.

Example ​

SELECT transaction_timestamp() IS NOT NULL FORMAT csv;

1

uuid_extract_timestamp ​

Extract the UTC timestamp carried by an RFC 9562 UUID version 1 or 7.

Syntax ​

uuid_extract_timestamp(uuid)

Arguments ​

Native UUID or accepted UUID text. Version 1 drops submicrosecond digits; version 7 exposes its millisecond timestamp. Other versions/variants and SQL NULL return NULL.

Returned value ​

Native UTC DateTime64(6), or NULL when no supported timestamp is present.

Example ​

SELECT date_part('year',uuid_extract_timestamp('019535d9-3df7-79fb-b466-fa907fa17f9e')) FORMAT csv;

2025.0

Legacy calendar functions ​

CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP are SQL keywords without parentheses. They return UTC TEXT in YYYY-MM-DD, HH:MM:SS and YYYY-MM-DD HH:MM:SS form. Native timestamp functions have their own typed results and clock scopes.

SELECT typeof(CURRENT_DATE),length(CURRENT_TIME),length(CURRENT_TIMESTAMP) FORMAT csv;

"text",8,19

date, time, datetime, julianday and unixepoch accept an optional time value and modifiers. strftime(format,time_value,...) formats a date; timediff(later,earlier) returns a calendar span. ISO date/time text is supported, and the unixepoch modifier interprets numeric Unix seconds. These functions follow their SQLite-style contracts, separate from native DateTime declarations; invalid or out-of-range calendar results return NULL.

SELECT date('2024-03-05','+1 day'),datetime(0,'unixepoch'),strftime('%Y-%m','2024-03-05') FORMAT csv;

"2024-03-06","1970-01-01 00:00:00","2024-03"