Date and time functions

145 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

current_date

Returns the UTC calendar day captured when the statement begins.

current_date() -> date

The result is the UTC date at statement start, so every invocation in that statement agrees even across midnight. This zero-argument function never returns NULL; continuous and derived plans reject its clock dependency with INVALID_ARGUMENT.

Example

current_date() = cast(current_timestamp() AS DATE)
-- → true

current_timestamp

Returns a single statement-start instant at a chosen fractional precision.

current_timestamp() -> datetime64(0,'UTC')
current_timestamp(precision:uint8) -> datetime64(precision,'UTC')

Captures one UTC instant for the whole statement and returns datetime64 with the constant precision requested, defaulting to zero. Precision outside 0 through 9 raises INVALID_ARGUMENT, and the no-argument form is never NULL. Continuous and derived plans reject this clock-dependent contract with INVALID_ARGUMENT.

Example

current_timestamp() = current_timestamp()
-- → true

date_add

Shifts a temporal value by a unit-count pair, clamping calendar month ends.

date_add(unit:string, amount:int64, value:date) -> date
date_add(unit:string, amount:int64, value:datetime64(p,tz)) -> datetime64(p,tz)
date_add(value:date, amount:tuple(int64,string)) -> date
date_add(value:datetime64(p,tz), amount:tuple(int64,string)) -> datetime64(p,tz)

Adds a signed amount in the selected unit, either as separate arguments or a tuple of count and unit; month and year arithmetic clamps impossible days to the destination month end. NULL propagates, and date inputs allow only day through year calendar units. An incompatible unit or a shift not exactly representable at the timestamp precision raises INVALID_ARGUMENT; a result outside the type range raises NUMERIC_OVERFLOW.

Example

date_add('month', 1, DATE '2024-01-31')
-- → DATE '2024-02-29'

date_add_days

Advances a temporal operand through daily calendar increments.

date_add_days(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_days(value:date, amount:int64) -> date

Adds the requested day count while preserving a date result or the timestamp descriptor and precision. Negative counts move backward, and a NULL operand produces NULL. Crossing the representable temporal range raises NUMERIC_OVERFLOW.

Example

date_add_days(DATE '2024-02-28', 2)
-- → DATE '2024-03-01'

date_add_hours

Moves a clock reading forward by hour-sized increments, promoting date inputs.

date_add_hours(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_hours(value:date, amount:int64) -> datetime64(0,UTC)

Applies a signed hour offset to the supplied temporal value; a date begins at midnight UTC and produces a precision-zero timestamp. Existing timestamps retain their zone and any fractional precision. NULL inputs remain NULL, and an unrepresentable shifted instant raises NUMERIC_OVERFLOW.

Example

date_add_hours(DATE '2024-01-01', 6)
-- → TIMESTAMP '2024-01-01 06:00:00+00:00'

date_add_microseconds

Produces a timestamp displaced by millionths of a second with at least six fractional digits.

date_add_microseconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,6),tz)
date_add_microseconds(value:date, amount:int64) -> datetime64(6,UTC)

Each count unit adds one microsecond, increasing timestamp precision to six when necessary so the displacement is exact. A date is interpreted as UTC midnight, while an existing timestamp keeps its timezone. NULL propagates through either operand; exceeding the resulting tick range raises NUMERIC_OVERFLOW.

Example

date_add_microseconds(DATE '1970-01-01', 7)
-- → TIMESTAMP '1970-01-01 00:00:00.000007+00:00'

date_add_milliseconds

Offsets a temporal input in thousandths of a second without losing millisecond detail.

date_add_milliseconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,3),tz)
date_add_milliseconds(value:date, amount:int64) -> datetime64(3,UTC)

Adds signed millisecond ticks and returns datetime64 at the greater of the input precision and three. Date operands are promoted from midnight UTC rather than rounded back to a date. Missing operands yield NULL, and arithmetic beyond the output descriptor range raises NUMERIC_OVERFLOW.

Example

date_add_milliseconds(DATE '1970-01-01', 250)
-- → TIMESTAMP '1970-01-01 00:00:00.250+00:00'

date_add_minutes

Returns a later or earlier timestamp after applying a signed number of minutes.

date_add_minutes(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_minutes(value:date, amount:int64) -> datetime64(0,UTC)

Shifts the value by minute increments, retaining fractional seconds when present. Supplying a date creates a UTC timestamp at midnight before the shift. A NULL value or amount returns NULL; an out-of-range result raises NUMERIC_OVERFLOW.

Example

date_add_minutes(DATE '2024-01-01', 90)
-- → TIMESTAMP '2024-01-01 01:30:00+00:00'

date_add_months

Finds the destination calendar month and clips an unavailable day to its final date.

date_add_months(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_months(value:date, amount:int64) -> date

Moves a temporal value by the signed number of months while keeping its civil day where possible; January 31 therefore becomes February 29 in a leap year. Dates stay dates, and timestamps keep their precision and timezone. NULL propagates, with NUMERIC_OVERFLOW raised when the destination cannot be represented.

Example

date_add_months(DATE '2024-01-31', 1)
-- → DATE '2024-02-29'

date_add_nanoseconds

Adds billionth-second ticks and returns nine-digit timestamp precision.

date_add_nanoseconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,9),tz)
date_add_nanoseconds(value:date, amount:int64) -> datetime64(9,UTC)

The amount is measured in nanoseconds, and the result uses datetime64 precision nine even when the input is a lower-precision timestamp. A date contributes midnight UTC; an existing timezone descriptor survives the operation. Either NULL operand yields NULL, and scaling or addition outside the signed tick range raises NUMERIC_OVERFLOW.

Example

date_add_nanoseconds(DATE '1970-01-01', 12)
-- → TIMESTAMP '1970-01-01 00:00:00.000000012+00:00'

date_add_quarters

Advances civil dates in three-month steps while retaining their nearest valid day.

date_add_quarters(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_quarters(value:date, amount:int64) -> date

Treats each signed quarter as three calendar months rather than a fixed number of seconds. A day absent from the target month is clamped to month end, with date versus timestamp type preserved. NULL operands return NULL, and unrepresentable calendar results raise NUMERIC_OVERFLOW.

Example

date_add_quarters(DATE '2024-01-31', 1)
-- → DATE '2024-04-30'

date_add_seconds

Applies signed one-second increments while preserving any existing fractional remainder.

date_add_seconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_seconds(value:date, amount:int64) -> datetime64(0,UTC)

Adds whole seconds without discarding finer timestamp ticks already present in the input. Dates start at UTC midnight and return datetime64 at precision zero. The operation propagates NULL and raises NUMERIC_OVERFLOW if either scaling or the resulting instant exceeds the output range.

Example

date_add_seconds(DATE '1970-01-01', 75)
-- → TIMESTAMP '1970-01-01 00:01:15+00:00'

date_add_weeks

Moves a temporal value by seven-day calendar blocks.

date_add_weeks(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_weeks(value:date, amount:int64) -> date

A signed week count advances or retreats the calendar by seven days per unit. Date inputs remain dates, and timestamp inputs retain their timezone and fractional precision. NULL yields NULL; a destination outside the supported temporal range raises NUMERIC_OVERFLOW.

Example

date_add_weeks(DATE '2024-02-26', 1)
-- → DATE '2024-03-04'

date_add_years

Computes a calendar anniversary, mapping leap-day anniversaries to valid month ends.

date_add_years(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_add_years(value:date, amount:int64) -> date

Adjusts the calendar year by the supplied signed count and preserves the month and day unless the destination year lacks that date. February 29 clamps to February 28 in a common year; timestamp zone and precision remain intact. NULL propagates, while an unrepresentable anniversary raises NUMERIC_OVERFLOW.

Example

date_add_years(DATE '2024-02-29', 1)
-- → DATE '2025-02-28'

date_age

Counts fully completed temporal units between two endpoints rather than crossed boundaries.

date_age(unit:string, start:date|datetime64(p,tz), end:date|datetime64(p,tz), tz:string=descriptor) -> int32

Returns the signed number of complete units from start to end, so a partial month does not count merely because a month boundary was crossed. The unit must be one of nanosecond through year, and the explicit or descriptor timezone controls calendar interpretation. Any NULL operand returns NULL; invalid units raise INVALID_ARGUMENT and an int32 result overflow raises NUMERIC_OVERFLOW.

Example

date_age('month', DATE '2024-01-31', DATE '2024-02-01')
-- → 0

date_as_blob

Serializes the signed days since 1970 as four portable little-endian bytes.

date_as_blob(value:date) -> bytes

Encodes the date as its signed 32-bit day distance from 1970-01-01, with the least significant byte first. The bytes are independent of host memory layout and contain no text or timezone information. NULL dates return NULL; a non-date operand raises TYPE_MISMATCH.

Example

hex_encode(date_as_blob(DATE '1970-01-02'))
-- → '01000000'

date_datetime_to_snowflake

Builds the signed legacy Snowflake timestamp payload from a date-time instant.

date_datetime_to_snowflake(value:datetime64(p,tz), epoch_millis:int64=1288834974657) -> int64

The legacy datetime conversion accepts datetime64 and stores elapsed milliseconds above the reserved worker and sequence positions in an int64 result. Its default epoch is 1288834974657 milliseconds after Unix zero, and fractions below a millisecond do not survive. NULL propagates; out-of-layout offsets raise NUMERIC_OVERFLOW and no uniqueness guarantee is implied.

Example

date_datetime_to_snowflake(TIMESTAMP '1970-01-01 00:00:00.002+00:00', 0)
-- → 8388608

date_datetime_to_snowflake_id

Returns a nonnegative Snowflake code with timestamp bits populated and identity bits cleared.

date_datetime_to_snowflake_id(value:datetime64(p,tz), epoch_millis:int64=1288834974657) -> uint64

Uses the datetime-compatible entry point to encode whole milliseconds since the selected epoch as a uint64 Snowflake value. The same timestamp and epoch always produce the same code because the worker and sequence fields are zero. NULL input produces NULL; the conversion raises NUMERIC_OVERFLOW when the offset cannot fit, and discards finer fractional ticks.

Example

date_datetime_to_snowflake_id(TIMESTAMP '1970-01-01 00:00:00.003+00:00', 0)
-- → 12582912

date_datetime_to_uuid_v7

Forms a UUID version 7 value from timestamp milliseconds and caller-supplied entropy.

date_datetime_to_uuid_v7(value:datetime64(p,tz), entropy:uint128=0) -> uuid

Places the timestamp millisecond count into a UUIDv7 layout and fills the remaining payload from the supplied entropy. Entropy defaults to zero, making this a deterministic conversion rather than a uniqueness generator. NULL propagates; timestamp or entropy values that cannot fit the required fields raise NUMERIC_OVERFLOW.

Example

date_datetime_to_uuid_v7(TIMESTAMP '1970-01-01 00:00:00+00:00')
-- → UUID '00000000-0000-7000-8000-000000000000'

date_datetime64_to_snowflake

Encodes fractional timestamps into signed Snowflake high bits with empty worker and sequence fields.

date_datetime64_to_snowflake(value:datetime64(p,tz), epoch_millis:int64=1288834974657) -> int64

Converts the instant to whole milliseconds relative to the supplied epoch, defaulting to 1288834974657, and shifts that count into the signed Snowflake timestamp field. Finer fractions are discarded and all worker and sequence bits are zero, so this conversion does not generate unique identifiers. NULL returns NULL; a relative time outside the signed layout raises NUMERIC_OVERFLOW.

Example

date_datetime64_to_snowflake(TIMESTAMP '1970-01-01 00:00:00+00:00', 0)
-- → 0

date_datetime64_to_snowflake_id

Packs an instant into an unsigned 64-bit Snowflake identifier relative to a configurable epoch.

date_datetime64_to_snowflake_id(value:datetime64(p,tz), epoch_millis:int64=1288834974657) -> uint64

Builds a uint64 identifier from the timestamp millisecond offset, leaving its lower worker and sequence payload clear. Submillisecond detail is discarded, so repeated instants can produce identical identifiers. Any NULL argument yields NULL, and a negative or oversized offset for the layout raises NUMERIC_OVERFLOW.

Example

date_datetime64_to_snowflake_id(TIMESTAMP '1970-01-01 00:00:00.001+00:00', 0)
-- → 4194304

date_diff

Counts requested unit boundaries crossed between temporal endpoints.

date_diff(start:datetime64(p,tz), end:datetime64(q,tz)) -> int64
date_diff(unit:string, start:date|datetime64(p,tz), end:date|datetime64(p,tz), tz:string=descriptor) -> int64

Returns end minus start as a boundary count; the two-argument overload specifically subtracts floor-rounded UTC epoch seconds. The unit-first form supports nanosecond through year boundaries, requires an explicit timezone for mixed-zone operands, and propagates NULL. Invalid units or unresolved mixed zones raise INVALID_ARGUMENT, while a count outside int64 raises NUMERIC_OVERFLOW.

Example

date_diff('month', DATE '2024-01-31', DATE '2024-02-01')
-- → 1

date_diff_within

Tests whether two instants lie within a nonnegative fixed-duration tolerance.

date_diff_within(start:datetime64(p,tz), end:datetime64(p,tz), tolerance:tuple(int64,string)) -> bool

Compares the absolute elapsed distance between timestamps with an inclusive tolerance encoded as a count-unit tuple. Only fixed units from nanosecond through week are allowed; negative tolerances and month, quarter, or year units raise INVALID_ARGUMENT. NULL timestamps or tolerance produce NULL rather than false.

Example

date_diff_within(
  TIMESTAMP '2024-01-01 00:00:00+00:00',
  TIMESTAMP '2024-01-01 00:00:05+00:00',
  (5, 'second')
)
-- → true

date_format_datetime

Renders temporal fields as text using percent directives and pinned English names.

date_format_datetime(value:date|datetime64(p,tz), pattern:string, tz:string=descriptor) -> string

Formats the value with the percent-pattern grammar after applying the explicit timezone or the timestamp descriptor zone. English month and weekday names are fixed rather than inherited from process locale. NULL arguments produce NULL; malformed patterns or unknown zones raise INVALID_ARGUMENT.

Example

date_format_datetime(DATE '2024-03-05', '%Y-%m-%d')
-- → '2024-03-05'

date_format_datetime_in_joda_syntax

Produces a date-time string from Joda pattern letters in a selected timezone.

date_format_datetime_in_joda_syntax(value:date|datetime64(p,tz), pattern:string, tz:string=descriptor) -> string

Interprets the format string using Joda syntax, including its distinction between quoted literals and repeated pattern letters. Calendar fields come from the supplied or descriptor timezone, with a pinned English locale. NULL returns NULL, and invalid pattern grammar or an incompatible zone raises INVALID_ARGUMENT.

Example

date_format_datetime_in_joda_syntax(DATE '2024-03-05', 'yyyy/MM/dd')
-- → '2024/03/05'

date_from_unix_timestamp

Converts signed Unix seconds to a UTC timestamp or explicitly formatted calendar text.

date_from_unix_timestamp(seconds:int64) -> datetime64(0,UTC)
date_from_unix_timestamp(seconds:int64,pattern:string) -> string
date_from_unix_timestamp(seconds:int64,pattern:string,tz:string) -> string

With one argument, interprets the signed integer as seconds since 1970-01-01 and returns datetime64 at precision zero in UTC. A second string is always a percent-format pattern, and an optional third argument selects the formatting timezone. NULL propagates; range violations raise NUMERIC_OVERFLOW and malformed patterns raise INVALID_ARGUMENT.

Example

date_from_unix_timestamp(0)
-- → TIMESTAMP '1970-01-01 00:00:00+00:00'

date_from_unix_timestamp64_micro

Reconstructs a six-place timestamp from signed epoch microticks.

date_from_unix_timestamp64_micro(epoch:int64, tz:string=UTC) -> datetime64(6,tz)

The integer denotes microseconds before or after Unix zero and becomes a datetime64 value with precision six. An optional timezone changes how the instant is displayed without changing its elapsed epoch count. NULL yields NULL, and an invalid timezone identifier raises INVALID_ARGUMENT.

Example

date_from_unix_timestamp64_micro(1000001)
-- → TIMESTAMP '1970-01-01 00:00:01.000001+00:00'

date_from_unix_timestamp64_milli

Decodes a millisecond epoch counter into a timezone-bearing temporal value.

date_from_unix_timestamp64_milli(epoch:int64, tz:string=UTC) -> datetime64(3,tz)

Maps each integer tick to one thousandth of a second from the Unix epoch, returning a precision-three timestamp. Negative counts represent pre-1970 instants; selecting another timezone only alters the descriptor. A NULL count or zone returns NULL, while an unrecognized zone raises INVALID_ARGUMENT.

Example

date_from_unix_timestamp64_milli(-1)
-- → TIMESTAMP '1969-12-31 23:59:59.999+00:00'

date_from_unix_timestamp64_nano

Interprets an int64 nanosecond counter as a precision-nine instant.

date_from_unix_timestamp64_nano(epoch:int64, tz:string=UTC) -> datetime64(9,tz)

Returns datetime64 with nine fractional digits from the signed count of billionth-second ticks since 1970-01-01. The input counter is preserved exactly, including negative values, and the optional timezone does not shift the instant. NULL inputs return NULL; invalid timezone names raise INVALID_ARGUMENT.

Example

date_from_unix_timestamp64_nano(1)
-- → TIMESTAMP '1970-01-01 00:00:00.000000001+00:00'

date_from_unix_timestamp64_second

Creates a whole-second date-time from a signed counter anchored at Unix zero.

date_from_unix_timestamp64_second(epoch:int64, tz:string=UTC) -> datetime64(0,tz)

Interprets the count directly as signed seconds since the UTC epoch and returns precision-zero datetime64 in the requested timezone. This overload offers a timezone descriptor argument instead of a formatting pattern. NULL propagates, with INVALID_ARGUMENT for an unknown timezone and NUMERIC_OVERFLOW for an unrepresentable instant.

Example

date_from_unix_timestamp64_second(-1)
-- → TIMESTAMP '1969-12-31 23:59:59+00:00'

date_from_utc_timestamp

Projects a UTC instant onto the civil clock of an explicit destination zone.

date_from_utc_timestamp(value:datetime64(p,tz), tz:string) -> datetime64(p,tz)

Expresses the UTC instant as wall-clock fields in the named timezone using the pinned timezone rules. The inverse local-to-UTC conversion is unambiguous only when those fields identify a unique offset. NULL arguments remain NULL, and an unknown destination zone raises INVALID_ARGUMENT.

Example

date_from_utc_timestamp(TIMESTAMP '2024-01-01 12:00:00+00:00', 'UTC')
-- → TIMESTAMP '2024-01-01 12:00:00+00:00'

date_make_date

Constructs a civil date from checked year, month, and day fields.

date_make_date(year:uint16, month:uint8, day:uint8) -> date

Builds a date from unsigned calendar components and validates the actual length of the selected month. Impossible dates such as February 30 raise INVALID_DATETIME instead of rolling into the next month. Any NULL component yields NULL, and a calendar value outside the date range raises NUMERIC_OVERFLOW.

Example

date_make_date(2024, 2, 29)
-- → DATE '2024-02-29'

date_make_datetime

Assembles a timezone-aware whole-second instant from six calendar components.

date_make_datetime(year:uint16, month:uint8, day:uint8, hour:uint8, minute:uint8, second:uint8, tz:string=UTC) -> datetime64(0,tz)

Combines year through second in the selected timezone, defaulting to UTC, and returns precision-zero datetime64. Impossible civil fields or an ambiguous or nonexistent local time without a resolving offset raise INVALID_DATETIME. NULL components propagate, while an incompatible zone raises INVALID_ARGUMENT.

Example

date_make_datetime(2024, 3, 5, 8, 9, 10)
-- → TIMESTAMP '2024-03-05 08:09:10+00:00'

date_make_datetime64

Builds a fractional timestamp from civil components and precision-scaled subsecond digits.

date_make_datetime64(year:uint16, month:uint8, day:uint8, hour:uint8, minute:uint8, second:uint8, fraction:uint32=0, precision:uint8=3, tz:string=UTC) -> datetime64(precision,tz)

The fraction is an integer count of ticks at the supplied precision, which defaults to three and must lie between zero and nine. Civil fields must form a valid local datetime; impossible or unresolved daylight-saving times raise INVALID_DATETIME, while an invalid precision or oversized fraction raises INVALID_ARGUMENT. NULL arguments yield NULL and a timestamp outside its tick range raises NUMERIC_OVERFLOW.

Example

date_make_datetime64(2024, 3, 5, 8, 9, 10, 25, 3)
-- → TIMESTAMP '2024-03-05 08:09:10.025+00:00'

date_name

Returns English month or weekday labels, or decimal text for other temporal components.

date_name(unit:string, value:date|datetime64(p,tz), tz:string=descriptor) -> string

Selects a component of the date or zoned timestamp and returns its textual representation. Month and weekday requests use pinned English names, while other supported numeric components use decimal digits. NULL arguments return NULL; incompatible component names or timezone identifiers raise INVALID_ARGUMENT.

Example

date_name('month', DATE '2024-03-05')
-- → 'March'

date_parse_datetime

Parses fully dated text with percent-format directives into a second-resolution timestamp.

date_parse_datetime(text:string, pattern:string, tz:string=UTC) -> datetime64(0,tz)

Consumes the text according to an explicit percent-format pattern and interprets civil fields in the supplied timezone, UTC by default. A complete year is required; no missing date field is inferred from the current clock. NULL input yields NULL, malformed calendar text raises INVALID_DATETIME, and invalid pattern syntax raises INVALID_ARGUMENT.

Example

date_parse_datetime('2024-03-05 08:09:10', '%Y-%m-%d %H:%M:%S')
-- → TIMESTAMP '2024-03-05 08:09:10+00:00'

date_parse_datetime_best_effort

Finds a matching known calendar format and returns a whole-second instant.

date_parse_datetime_best_effort(text:string, tz:string=UTC) -> datetime64(0,tz)

Attempts the supported best-effort date-time grammars and interprets otherwise unzoned fields in the selected timezone. The text must carry its full year, preventing the result from depending on when parsing runs. NULL is preserved; unmatched or impossible text raises INVALID_DATETIME and an unknown zone raises INVALID_ARGUMENT.

Example

date_parse_datetime_best_effort('2024-03-05 08:09:10')
-- → TIMESTAMP '2024-03-05 08:09:10+00:00'

date_parse_datetime_best_effort_or_null

Yields a seconds-resolution timestamp when recognition succeeds, otherwise a missing value.

date_parse_datetime_best_effort_or_null(text:string, tz:string=UTC) -> datetime64(0,tz)

Recognizes supported timestamp formats only when the source supplies a complete year. A failed calendar conversion returns NULL, and SQL NULL passes through unchanged, without inventing a date from the current clock. Invalid timezone configuration remains an INVALID_ARGUMENT error.

Example

date_parse_datetime_best_effort_or_null('2024-02-30')
-- → NULL

date_parse_datetime_best_effort_or_zero

Converts recognizable full dates to seconds and malformed dates to Unix epoch.

date_parse_datetime_best_effort_or_zero(text:string, tz:string=UTC) -> datetime64(0,tz)

Uses known date-time grammars with a mandatory full year to produce a precision-zero timestamp. Unconvertible text produces the zero epoch instant under the explicit default policy, while NULL input remains NULL. An invalid timezone raises INVALID_ARGUMENT and is not swallowed by the text-conversion default.

Example

date_parse_datetime_best_effort_or_zero('invalid')
-- → TIMESTAMP '1970-01-01 00:00:00+00:00'

date_parse_datetime_best_effort_us

Resolves month/day ordering in American date strings and returns second-granularity instants.

date_parse_datetime_best_effort_us(text:string, tz:string=UTC) -> datetime64(0,tz)

When numeric date fields are ambiguous, the parser chooses month before day while still accepting its supported timestamp formats. Every parsed date must include a complete year, and the result has zero fractional precision. NULL propagates; bad date text raises INVALID_DATETIME and an incompatible timezone raises INVALID_ARGUMENT.

Example

date_parse_datetime_best_effort_us('03/05/2024 08:09:10')
-- → TIMESTAMP '2024-03-05 08:09:10+00:00'

date_parse_datetime_best_effort_us_or_null

Makes failed American-style date recognition nullable without fractional-second storage.

date_parse_datetime_best_effort_us_or_null(text:string, tz:string=UTC) -> datetime64(0,tz)

Parses complete-year timestamps at whole-second resolution, preferring month/day order for ambiguous US-style inputs. Invalid calendar text and SQL NULL produce NULL rather than a fabricated instant. An unknown timezone is still INVALID_ARGUMENT because this variant suppresses date conversion failures only.

Example

date_parse_datetime_best_effort_us_or_null('02/30/2024')
-- → NULL

date_parse_datetime_best_effort_us_or_zero

Supplies a whole-second epoch default for unusable US-order calendar input.

date_parse_datetime_best_effort_us_or_zero(text:string, tz:string=UTC) -> datetime64(0,tz)

Accepts supported full-year formats with month-first precedence and returns a precision-zero datetime64 value. When the date text is invalid, it returns Unix epoch rather than raising INVALID_DATETIME; NULL input remains NULL. Invalid timezone arguments continue to raise INVALID_ARGUMENT.

Example

date_parse_datetime_best_effort_us_or_zero('invalid')
-- → TIMESTAMP '1970-01-01 00:00:00+00:00'

date_parse_datetime_in_joda_syntax

Matches Joda calendar patterns to produce instants without a fractional component.

date_parse_datetime_in_joda_syntax(text:string, pattern:string, tz:string=UTC) -> datetime64(0,tz)

Uses Joda pattern letters and literals to parse a full-year timestamp in the supplied timezone, defaulting to UTC. Missing years and impossible civil fields raise INVALID_DATETIME rather than borrowing the current date. NULL text yields NULL, and malformed pattern syntax or an unknown zone raises INVALID_ARGUMENT.

Example

date_parse_datetime_in_joda_syntax('2024/03/05 08:09:10', 'yyyy/MM/dd HH:mm:ss')
-- → TIMESTAMP '2024-03-05 08:09:10+00:00'

date_parse_datetime_in_joda_syntax_or_null

Preserves missing values when Joda calendar text fails whole-second conversion.

date_parse_datetime_in_joda_syntax_or_null(text:string, pattern:string, tz:string=UTC) -> datetime64(0,tz)

Parses a complete-year string with the selected Joda pattern into datetime64 at precision zero. A calendar conversion failure returns NULL, matching the behavior of a NULL source string. Pattern grammar and timezone options remain validated, with INVALID_ARGUMENT for invalid configuration.

Example

date_parse_datetime_in_joda_syntax_or_null('2024/02/30', 'yyyy/MM/dd')
-- → NULL

date_parse_datetime_in_joda_syntax_or_zero

Returns Unix zero for invalid Joda date text at whole-second resolution.

date_parse_datetime_in_joda_syntax_or_zero(text:string, pattern:string, tz:string=UTC) -> datetime64(0,tz)

The input must match the explicit Joda pattern and include its full year to produce a precision-zero timestamp. A failed date conversion is replaced with the Unix epoch, but SQL NULL is preserved as NULL. Invalid patterns and timezone settings raise INVALID_ARGUMENT and do not trigger the default.

Example

date_parse_datetime_in_joda_syntax_or_zero('invalid', 'yyyy/MM/dd')
-- → TIMESTAMP '1970-01-01 00:00:00+00:00'

date_parse_datetime_or_null

Turns unconvertible percent-formatted dates into NULL seconds-resolution values.

date_parse_datetime_or_null(text:string, pattern:string, tz:string=UTC) -> datetime64(0,tz)

Reads a complete-year date string using percent-format directives and the selected timezone. A date conversion error produces NULL instead of INVALID_DATETIME, while a NULL source also returns NULL. Configuration errors such as malformed directives or unknown timezones remain INVALID_ARGUMENT.

Example

date_parse_datetime_or_null('2024-02-30', '%Y-%m-%d')
-- → NULL

date_parse_datetime_or_zero

Substitutes a zero-second epoch when explicit percent-pattern parsing fails.

date_parse_datetime_or_zero(text:string, pattern:string, tz:string=UTC) -> datetime64(0,tz)

Matches text against percent directives to obtain a whole-second datetime64 value with a complete year. Invalid date text becomes Unix epoch under this explicit default policy, whereas SQL NULL stays NULL. Pattern and timezone configuration errors raise INVALID_ARGUMENT instead of being recoded as epoch.

Example

date_parse_datetime_or_zero('invalid', '%Y-%m-%d')
-- → TIMESTAMP '1970-01-01 00:00:00+00:00'

date_parse_datetime32_best_effort

Recognizes dated timestamp strings within the legacy unsigned 32-bit seconds range.

date_parse_datetime32_best_effort(text:string, tz:string=UTC) -> datetime64(0,tz)

Tries the documented timestamp formats and returns precision-zero datetime64 only when the instant fits the legacy UINT32 Unix-seconds range. The text must contain a full year, so partial dates never borrow the statement year. NULL input returns NULL; invalid text raises INVALID_DATETIME and out-of-range seconds raise NUMERIC_OVERFLOW.

Example

date_parse_datetime32_best_effort('1970-01-01 00:00:01')
-- → TIMESTAMP '1970-01-01 00:00:01+00:00'

date_parse_datetime32_best_effort_or_null

Returns a legacy-range timestamp for recognized text and NULL for failed date conversion.

date_parse_datetime32_best_effort_or_null(text:string, tz:string=UTC) -> datetime64(0,tz)

Attempts known complete-year formats, then checks that the parsed instant lies in the unsigned 32-bit Unix-seconds era. Text that cannot be converted, including an out-of-era value, yields NULL just as a NULL input does. Invalid timezone configuration remains INVALID_ARGUMENT rather than being hidden as a parse failure.

Example

date_parse_datetime32_best_effort_or_null('not-a-date')
-- → NULL

date_parse_datetime32_best_effort_or_zero

Substitutes Unix zero when a legacy seconds parser cannot convert a dated string.

date_parse_datetime32_best_effort_or_zero(text:string, tz:string=UTC) -> datetime64(0,tz)

Uses the best-effort complete-year parser with the UINT32-seconds compatibility range and a precision-zero result. A text conversion failure returns the Unix epoch, but SQL NULL still returns NULL and an invalid timezone raises INVALID_ARGUMENT. The epoch default is an explicit data-conversion policy, so it cannot distinguish failed text from a valid zero instant.

Example

date_parse_datetime32_best_effort_or_zero('not-a-date')
-- → TIMESTAMP '1970-01-01 00:00:00+00:00'

date_parse_datetime64

Decodes percent-pattern date-time text at a requested fractional-second scale.

date_parse_datetime64(text:string, precision:uint8=3, pattern:string, tz:string=UTC) -> datetime64(precision,tz)

Parses a complete-year string with the supplied percent pattern into datetime64 at precision zero through nine, defaulting to three. The explicit timezone governs local fields, and missing years or invalid calendar values raise INVALID_DATETIME. NULL text yields NULL; invalid precision, format grammar, or timezone settings raise INVALID_ARGUMENT.

Example

date_parse_datetime64('2024-03-05 08:09:10', 3, '%Y-%m-%d %H:%M:%S')
-- → TIMESTAMP '2024-03-05 08:09:10.000+00:00'

date_parse_datetime64_best_effort

Recognizes common full-year date formats and preserves a chosen number of fractional digits.

date_parse_datetime64_best_effort(text:string, precision:uint8=3, tz:string=UTC) -> datetime64(precision,tz)

Selects among the documented best-effort timestamp formats without using the current date to complete missing years. The output is datetime64 with the requested precision, normally three, and the configured timezone. NULL input propagates; unparseable text raises INVALID_DATETIME and invalid precision or zone options raise INVALID_ARGUMENT.

Example

date_parse_datetime64_best_effort('2024-03-05 08:09:10.125', 3)
-- → TIMESTAMP '2024-03-05 08:09:10.125+00:00'

date_parse_datetime64_best_effort_or_null

Produces a nullable fractional instant from recognized complete calendar strings.

date_parse_datetime64_best_effort_or_null(text:string, precision:uint8=3, tz:string=UTC) -> datetime64(precision,tz)

Attempts the supported timestamp formats at the chosen datetime64 precision, requiring an explicit year in the text. Unusable calendar text becomes NULL, and an already NULL input stays NULL. An incompatible precision or timezone is an INVALID_ARGUMENT error because option validation is separate from text conversion.

Example

date_parse_datetime64_best_effort_or_null('2024-02-30', 6)
-- → NULL

date_parse_datetime64_best_effort_or_zero

Replaces unrecognized timestamp text with an epoch value at the selected subsecond precision.

date_parse_datetime64_best_effort_or_zero(text:string, precision:uint8=3, tz:string=UTC) -> datetime64(precision,tz)

Known full-year formats are converted to datetime64 using the requested scale and timezone. Failed text conversion produces a zero epoch value at that scale; this differs from a NULL input, which remains NULL. Invalid precision or zone configuration raises INVALID_ARGUMENT rather than triggering the epoch default.

Example

date_parse_datetime64_best_effort_or_zero('invalid', 6)
-- → TIMESTAMP '1970-01-01 00:00:00.000000+00:00'

date_parse_datetime64_best_effort_us

Interprets ambiguous slash dates month-first while parsing fractional instants.

date_parse_datetime64_best_effort_us(text:string, precision:uint8=3, tz:string=UTC) -> datetime64(precision,tz)

Uses US month/day precedence when a recognized date format is ambiguous, with a complete year required. The precision parameter controls fractional seconds; the US suffix does not imply microseconds. NULL returns NULL, invalid text raises INVALID_DATETIME, and an incompatible scale or timezone raises INVALID_ARGUMENT.

Example

date_parse_datetime64_best_effort_us('03/05/2024 08:09:10', 3)
-- → TIMESTAMP '2024-03-05 08:09:10.000+00:00'

date_parse_datetime64_best_effort_us_or_null

Converts US-style dated strings to nullable timestamps with a caller-selected scale.

date_parse_datetime64_best_effort_us_or_null(text:string, precision:uint8=3, tz:string=UTC) -> datetime64(precision,tz)

Resolves ambiguous numeric dates using month before day and returns datetime64 at the supplied precision. Strings without a year or with impossible calendar fields yield NULL, as does a SQL NULL input. Bad precision or timezone options still raise INVALID_ARGUMENT; the US convention changes date interpretation rather than tick size.

Example

date_parse_datetime64_best_effort_us_or_null('02/30/2024', 3)
-- → NULL

date_parse_datetime64_best_effort_us_or_zero

Falls back to the epoch for invalid month-first timestamp text while keeping precision.

date_parse_datetime64_best_effort_us_or_zero(text:string, precision:uint8=3, tz:string=UTC) -> datetime64(precision,tz)

Parses supported complete-year strings with US month/day precedence and the requested fractional precision. A date conversion failure produces the Unix epoch, whereas a NULL argument remains NULL. Precision outside zero through nine or an invalid timezone raises INVALID_ARGUMENT and is not an error-to-epoch conversion.

Example

date_parse_datetime64_best_effort_us_or_zero('invalid', 3)
-- → TIMESTAMP '1970-01-01 00:00:00.000+00:00'

date_parse_datetime64_in_joda_syntax

Reads calendar strings with Joda field symbols into precision-controlled date-times.

date_parse_datetime64_in_joda_syntax(text:string, precision:uint8=3, pattern:string, tz:string=UTC) -> datetime64(precision,tz)

Matches text against the supplied Joda pattern, including quoted literals, and requires the parsed value to contain a full year. Civil fields are interpreted in the named timezone and stored at the requested datetime64 scale. NULL input yields NULL; invalid dates raise INVALID_DATETIME, while incompatible precision or malformed patterns raise INVALID_ARGUMENT.

Example

date_parse_datetime64_in_joda_syntax(
  '2024/03/05 08:09:10',
  3,
  'yyyy/MM/dd HH:mm:ss'
)
-- → TIMESTAMP '2024-03-05 08:09:10.000+00:00'

date_parse_datetime64_in_joda_syntax_or_null

Returns NULL instead of a date-conversion error for fractional Joda-pattern input.

date_parse_datetime64_in_joda_syntax_or_null(text:string, precision:uint8=3, pattern:string, tz:string=UTC) -> datetime64(precision,tz)

The Joda pattern and selected precision determine how a complete-year string becomes datetime64. Calendar conversion failures and SQL NULL inputs both return NULL, allowing invalid source dates to remain missing. Invalid pattern grammar, precision, or timezone settings raise INVALID_ARGUMENT rather than being treated as bad date text.

Example

date_parse_datetime64_in_joda_syntax_or_null('2024/02/30', 3, 'yyyy/MM/dd')
-- → NULL

date_parse_datetime64_in_joda_syntax_or_zero

Uses a scaled zero instant when Joda-formatted calendar text cannot be converted.

date_parse_datetime64_in_joda_syntax_or_zero(text:string, precision:uint8=3, pattern:string, tz:string=UTC) -> datetime64(precision,tz)

Parses complete-year text through Joda pattern rules and returns a datetime64 value at the explicit or default precision. Failed date conversion gives Unix epoch ticks of zero, while NULL input remains NULL. An invalid Joda pattern or precision setting raises INVALID_ARGUMENT, so the default only handles the source date conversion.

Example

date_parse_datetime64_in_joda_syntax_or_zero('invalid', 6, 'yyyy/MM/dd')
-- → TIMESTAMP '1970-01-01 00:00:00.000000+00:00'

date_parse_datetime64_or_null

Treats invalid percent-formatted civil text as missing at the requested timestamp precision.

date_parse_datetime64_or_null(text:string, precision:uint8=3, pattern:string, tz:string=UTC) -> datetime64(precision,tz)

Converts text using percent directives, an explicit year, and the specified datetime64 precision. A failed date conversion produces NULL instead of INVALID_DATETIME; an incoming NULL also stays NULL. Errors in the pattern, timezone, or precision configuration remain INVALID_ARGUMENT.

Example

date_parse_datetime64_or_null('2024-02-30', 3, '%Y-%m-%d')
-- → NULL

date_parse_datetime64_or_zero

Maps failed percent-pattern timestamp conversions to precision-scaled epoch ticks.

date_parse_datetime64_or_zero(text:string, precision:uint8=3, pattern:string, tz:string=UTC) -> datetime64(precision,tz)

Applies the percent-format pattern and timezone to a complete-year string, retaining the selected fractional scale in the result. If the source date cannot be converted, the result is the Unix epoch at that precision, not SQL NULL. NULL input still propagates, and malformed patterns or incompatible options raise INVALID_ARGUMENT.

Example

date_parse_datetime64_or_zero('invalid', 3, '%Y-%m-%d')
-- → TIMESTAMP '1970-01-01 00:00:00.000+00:00'

date_sub

Subtracts a calendar or fixed unit-count interval while retaining the input temporal precision.

date_sub(unit:string, amount:int64, value:date) -> date
date_sub(unit:string, amount:int64, value:datetime64(p,tz)) -> datetime64(p,tz)
date_sub(value:date, amount:tuple(int64,string)) -> date
date_sub(value:datetime64(p,tz), amount:tuple(int64,string)) -> datetime64(p,tz)

Subtracts the signed amount selected by separate unit and count arguments or by a count-unit tuple. Calendar subtraction clamps unavailable month-days, and date values permit only day, week, month, quarter, or year units. NULL propagates; incompatible units or inexact precision raise INVALID_ARGUMENT, and an unrepresentable result raises NUMERIC_OVERFLOW.

Example

date_sub('month', 1, DATE '2024-03-31')
-- → DATE '2024-02-29'

date_subtract_days

Retreats a calendar date by a signed daily displacement.

date_subtract_days(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_days(value:date, amount:int64) -> date

Removes the requested number of days from a date or timestamp, so a negative amount moves the value forward. Date results stay dates, and timestamp results keep their descriptor precision and timezone. Either NULL operand returns NULL; crossing the temporal type bounds raises NUMERIC_OVERFLOW.

Example

date_subtract_days(DATE '2024-03-01', 1)
-- → DATE '2024-02-29'

date_subtract_hours

Moves a temporal input backward in hour increments from its original clock reading.

date_subtract_hours(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_hours(value:date, amount:int64) -> datetime64(0,UTC)

Subtracts whole hours while retaining any finer timestamp fraction. A date is first promoted from midnight UTC to a precision-zero datetime64, allowing the result to fall on the preceding day. NULL input or count produces NULL, and an out-of-range instant raises NUMERIC_OVERFLOW.

Example

date_subtract_hours(DATE '2024-01-02', 3)
-- → TIMESTAMP '2024-01-01 21:00:00+00:00'

date_subtract_microseconds

Removes millionth-second offsets and widens coarse timestamps to six decimal places.

date_subtract_microseconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,6),tz)
date_subtract_microseconds(value:date, amount:int64) -> datetime64(6,UTC)

Subtracts the supplied signed microsecond count exactly, choosing at least precision six for the result. Existing timezone metadata is retained, while a date becomes a UTC-midnight timestamp before subtraction. NULL operands yield NULL; tick scaling or subtraction outside the output range raises NUMERIC_OVERFLOW.

Example

date_subtract_microseconds(DATE '1970-01-01', 1)
-- → TIMESTAMP '1969-12-31 23:59:59.999999+00:00'

date_subtract_milliseconds

Computes a millisecond-adjusted earlier instant without truncating thousandth-second detail.

date_subtract_milliseconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,3),tz)
date_subtract_milliseconds(value:date, amount:int64) -> datetime64(3,UTC)

The signed amount removes millisecond ticks, with negative amounts advancing the instant. Results use at least three fractional digits, and dates are promoted to timestamps anchored at midnight UTC. Missing inputs remain NULL, and NUMERIC_OVERFLOW reports a displacement that the result descriptor cannot represent.

Example

date_subtract_milliseconds(DATE '1970-01-01', 250)
-- → TIMESTAMP '1969-12-31 23:59:59.750+00:00'

date_subtract_minutes

Rewinds a timestamp by minute-sized steps, with negative counts reversing direction.

date_subtract_minutes(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_minutes(value:date, amount:int64) -> datetime64(0,UTC)

Subtracts a signed number of minutes without discarding a timestamp fraction or changing its zone. Date inputs begin at UTC midnight and return precision-zero datetime64 rather than a date. The result is NULL when either operand is NULL, and unrepresentable arithmetic raises NUMERIC_OVERFLOW.

Example

date_subtract_minutes(DATE '2024-01-02', 30)
-- → TIMESTAMP '2024-01-01 23:30:00+00:00'

date_subtract_months

Finds an earlier month-relative date with end-of-month clamping.

date_subtract_months(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_months(value:date, amount:int64) -> date

Subtracts calendar months instead of an estimated fixed duration, preserving the civil day when the target month has it. For example, March 31 becomes the last day of February when one month is removed. NULL inputs return NULL, timestamp descriptors are preserved, and a destination beyond the type range raises NUMERIC_OVERFLOW.

Example

date_subtract_months(DATE '2024-03-31', 1)
-- → DATE '2024-02-29'

date_subtract_nanoseconds

Rewinds an instant by nanosecond ticks using full nine-place fractional storage.

date_subtract_nanoseconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,9),tz)
date_subtract_nanoseconds(value:date, amount:int64) -> datetime64(9,UTC)

Calculates the signed nanosecond displacement at precision nine, promoting coarser timestamps so none of the requested tick detail is lost. Dates start at midnight UTC; timestamp timezone descriptors remain unchanged. NULL propagates, and an overflowing precision conversion or subtraction raises NUMERIC_OVERFLOW.

Example

date_subtract_nanoseconds(DATE '1970-01-01', 1)
-- → TIMESTAMP '1969-12-31 23:59:59.999999999+00:00'

date_subtract_quarters

Steps backward through three-month calendar periods and clips invalid destination days.

date_subtract_quarters(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_quarters(value:date, amount:int64) -> date

Subtracts three calendar months for each quarter in the signed amount. When the destination month is shorter, the day clamps to its final valid date; dates and timestamps otherwise preserve their type and precision. NULL operands yield NULL and an unrepresentable target raises NUMERIC_OVERFLOW.

Example

date_subtract_quarters(DATE '2024-05-31', 1)
-- → DATE '2024-02-29'

date_subtract_seconds

Returns an instant after removing signed whole-second ticks.

date_subtract_seconds(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_seconds(value:date, amount:int64) -> datetime64(0,UTC)

Subtracts seconds while leaving any original fractional remainder intact. Passing a date promotes UTC midnight to a precision-zero timestamp, which can land in the previous calendar day after subtraction. NULL yields NULL; a result outside the signed tick range raises NUMERIC_OVERFLOW.

Example

date_subtract_seconds(DATE '1970-01-01', 1)
-- → TIMESTAMP '1969-12-31 23:59:59+00:00'

date_subtract_weeks

Retreats through seven-day spans while preserving date or timestamp shape.

date_subtract_weeks(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_weeks(value:date, amount:int64) -> date

Removes seven calendar days per signed week from the temporal argument. Negative counts advance the date, and timestamp inputs keep their timezone and fractional digits. Any NULL operand propagates NULL, while a target outside the representable range raises NUMERIC_OVERFLOW.

Example

date_subtract_weeks(DATE '2024-03-04', 1)
-- → DATE '2024-02-26'

date_subtract_years

Computes an earlier yearly anniversary with leap-day correction.

date_subtract_years(value:datetime64(p,tz), amount:int64) -> datetime64(max(p,0),tz)
date_subtract_years(value:date, amount:int64) -> date

Subtracts the year count from the civil calendar year and retains the month and day wherever valid. A leap-day input maps to February 28 when the selected year is not leap, with date or timestamp precision preserved. NULL stays NULL; temporal range overflow raises NUMERIC_OVERFLOW.

Example

date_subtract_years(DATE '2024-02-29', 1)
-- → DATE '2023-02-28'

date_timestamp_as_blob

Exports signed timestamp ticks as eight little-endian bytes without descriptor metadata.

date_timestamp_as_blob(value:datetime64(p,tz)) -> bytes

Serializes the underlying signed 64-bit tick counter in a fixed least-significant-byte-first representation. The precision and timezone remain properties of the input type and are not encoded in the byte payload, so interpreting it requires that descriptor. NULL input returns NULL and a non-timestamp operand raises TYPE_MISMATCH.

Example

hex_encode(date_timestamp_as_blob(TIMESTAMP '1970-01-01 00:00:01+00:00'))
-- → '0100000000000000'

date_timezone

Reports the statement timezone configuration as a non-null string.

date_timezone() -> string

Returns the timezone setting visible to the current statement rather than inspecting any timestamp operand. The zero-argument result is never NULL. Continuous plans reject this session-dependent lookup with INVALID_ARGUMENT because configuration changes are not replay-stable.

Example

date_timezone() IS NULL
-- → false

date_timezone_of

Reads the timezone carried by a timestamp type, normalizing an absent zone to UTC.

date_timezone_of(value:datetime64(p,tz)) -> string

Inspects the datetime64 descriptor and returns its zone name without consulting the statement or session timezone. A descriptor with no explicit zone is normalized to UTC. NULL timestamps return NULL, and other operand types raise TYPE_MISMATCH.

Example

date_timezone_of(TIMESTAMP '2024-01-01 00:00:00+00:00')
-- → 'UTC'

date_timezone_offset

Returns the signed UTC offset in seconds at an instant, including daylight-saving adjustments.

date_timezone_offset(value:datetime64(p,tz), tz:string=descriptor) -> int32

Looks up the offset of the explicit timezone or timestamp descriptor zone at the supplied instant. The function revision pins the timezone database, so historical and daylight-saving offsets are reproducible. NULL propagates and an unknown zone raises INVALID_ARGUMENT.

Example

date_timezone_offset(TIMESTAMP '2024-01-01 00:00:00+00:00', 'UTC')
-- → 0

date_to_day_of_month

Extracts the one-based position of a civil date within its month.

date_to_day_of_month(value:date|datetime64(p,tz), tz:string=descriptor) -> uint8

Returns a value from 1 through 31 after interpreting a timestamp in the explicit or descriptor timezone. Date operands already denote a civil date and have no clock fraction to inspect. NULL input yields NULL; invalid timezone options raise INVALID_ARGUMENT.

Example

date_to_day_of_month(DATE '2024-02-29')
-- → 29

date_to_day_of_week

Numbers weekdays under selectable Monday-first or Sunday-first conventions.

date_to_day_of_week(value:date|datetime64(p,tz), mode:uint8=0, tz:string=descriptor) -> uint8

Mode zero returns Monday as 1 through Sunday as 7; mode one uses Monday as 0, mode two Sunday as 0, and mode three Sunday as 1. The explicit or descriptor timezone determines the civil weekday. NULL arguments yield NULL, and a mode outside zero through three or an unknown zone raises INVALID_ARGUMENT.

Example

date_to_day_of_week(DATE '2024-03-04')
-- → 1

date_to_day_of_year

Gives the ordinal day within a calendar year, including February 29 in leap years.

date_to_day_of_year(value:date|datetime64(p,tz), tz:string=descriptor) -> uint16

Counts January 1 as one and returns values through 365 or 366 according to the civil year. Timestamp fields are interpreted in the specified or descriptor timezone before computing the ordinal. NULL propagates, while incompatible timezone identifiers raise INVALID_ARGUMENT.

Example

date_to_day_of_year(DATE '2024-03-01')
-- → 61

date_to_hour

Selects the civil hour from a zoned temporal value on a 24-hour clock.

date_to_hour(value:date|datetime64(p,tz), tz:string=descriptor) -> uint8

Returns the hour component from zero through 23 after applying the explicit timezone or timestamp descriptor. A date contributes midnight and therefore has hour zero. NULL input produces NULL; an invalid timezone raises INVALID_ARGUMENT.

Example

date_to_hour(TIMESTAMP '2024-03-05 18:09:10+00:00')
-- → 18

date_to_interval

Creates a structural interval tuple from an integral count and a validated unit label.

date_to_interval(count:string|int*|uint*|float*|decimal(P,S),unit:string) -> tuple(int64,string)

Converts the count to signed int64 and pairs it with a constant unit from nanosecond through year. This is a tuple contract, not a separate INTERVAL logical type, and calendar units are preserved rather than reduced to seconds. NULL propagates; fractional counts or incompatible units raise INVALID_ARGUMENT, while an out-of-range count raises NUMERIC_OVERFLOW.

Example

date_to_interval(3, 'month')
-- → (3, 'month')

date_to_interval_day

Tags a signed whole-number count with the calendar-day unit.

date_to_interval_day(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)

Returns the structural pair of an int64 count and the string day, preserving the unit for later temporal arithmetic. Text and numeric counts must represent exact integers, and NULL returns NULL. Fractional or malformed counts raise INVALID_ARGUMENT; values outside int64 raise NUMERIC_OVERFLOW.

Example

date_to_interval_day('2')
-- → (2, 'day')

date_to_interval_hour

Packages an integer duration as an hour-count pair for temporal operations.

date_to_interval_hour(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)

Produces a tuple whose first element is the checked signed count and whose second element is hour. The constructor does not shift a timestamp or consult a timezone. NULL yields NULL, nonintegral input raises INVALID_ARGUMENT, and int64 range violations raise NUMERIC_OVERFLOW.

Example

date_to_interval_hour(6)
-- → (6, 'hour')

date_to_interval_microsecond

Labels a numeric quantity as millionth-second interval ticks.

date_to_interval_microsecond(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)

Builds a count-unit tuple using microsecond as the unit while requiring an exact signed int64 count. This representation retains microsecond intent without selecting a timestamp precision itself. NULL counts return NULL; fractions raise INVALID_ARGUMENT and oversized integers raise NUMERIC_OVERFLOW.

Example

date_to_interval_microsecond(125)
-- → (125, 'microsecond')

date_to_interval_millisecond

Wraps a whole-number amount with thousandth-second interval metadata.

date_to_interval_millisecond(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)

The returned tuple carries the integral count followed by millisecond, allowing subsequent arithmetic to choose the necessary timestamp scale. Numeric and text inputs are checked instead of silently truncated. NULL stays NULL, malformed or fractional counts raise INVALID_ARGUMENT, and out-of-range counts raise NUMERIC_OVERFLOW.

Example

date_to_interval_millisecond(250)
-- → (250, 'millisecond')

date_to_interval_minute

Constructs a minute-denominated pair from an exact signed count.

date_to_interval_minute(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)

Checks that the input is an integral int64 value and returns it together with the minute unit label. Negative amounts are retained for arithmetic that moves backward, with no timestamp evaluated by the constructor. NULL input yields NULL; invalid counts raise INVALID_ARGUMENT and integer overflow raises NUMERIC_OVERFLOW.

Example

date_to_interval_minute(-15)
-- → (-15, 'minute')

date_to_interval_month

Represents a calendar-month displacement without assuming a fixed number of seconds.

date_to_interval_month(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)

Returns the checked count together with the month unit, retaining calendar meaning for later month-end handling. It does not approximate a month using thirty days or a fixed duration. NULL produces NULL; a fractional count raises INVALID_ARGUMENT and a value outside int64 raises NUMERIC_OVERFLOW.

Example

date_to_interval_month(2)
-- → (2, 'month')

date_to_interval_nanosecond

Stores a signed billionth-second quantity in a count-unit tuple.

date_to_interval_nanosecond(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)

Creates a structural interval with nanosecond as its unit and an exact int64 count. No conversion to floating seconds occurs, so all accepted integer ticks remain exact. NULL propagates, while malformed or nonintegral inputs raise INVALID_ARGUMENT and count overflow raises NUMERIC_OVERFLOW.

Example

date_to_interval_nanosecond(7)
-- → (7, 'nanosecond')

date_to_interval_quarter

Encodes a number of three-month calendar periods for deferred arithmetic.

date_to_interval_quarter(count:string|int*|uint*|float*|decimal(P,S)) -> tuple(int64,string)