# Date and time functions

> 145 Date and time functions in NYXDB: names, aliases, signatures and behaviour.

{/* AUTO-GENERATED from the function catalog. DO NOT EDIT — overwritten on every build. */}

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.

| Function | Summary |
| --- | --- |
| [`current_date`](#current_date) | Returns the UTC calendar day captured when the statement begins. |
| [`current_timestamp`](#current_timestamp) | Returns a single statement-start instant at a chosen fractional precision. |
| [`date_add`](#date_add) | Shifts a temporal value by a unit-count pair, clamping calendar month ends. |
| [`date_add_days`](#date_add_days) | Advances a temporal operand through daily calendar increments. |
| [`date_add_hours`](#date_add_hours) | Moves a clock reading forward by hour-sized increments, promoting date inputs. |
| [`date_add_microseconds`](#date_add_microseconds) | Produces a timestamp displaced by millionths of a second with at least six fractional digits. |
| [`date_add_milliseconds`](#date_add_milliseconds) | Offsets a temporal input in thousandths of a second without losing millisecond detail. |
| [`date_add_minutes`](#date_add_minutes) | Returns a later or earlier timestamp after applying a signed number of minutes. |
| [`date_add_months`](#date_add_months) | Finds the destination calendar month and clips an unavailable day to its final date. |
| [`date_add_nanoseconds`](#date_add_nanoseconds) | Adds billionth-second ticks and returns nine-digit timestamp precision. |
| [`date_add_quarters`](#date_add_quarters) | Advances civil dates in three-month steps while retaining their nearest valid day. |
| [`date_add_seconds`](#date_add_seconds) | Applies signed one-second increments while preserving any existing fractional remainder. |
| [`date_add_weeks`](#date_add_weeks) | Moves a temporal value by seven-day calendar blocks. |
| [`date_add_years`](#date_add_years) | Computes a calendar anniversary, mapping leap-day anniversaries to valid month ends. |
| [`date_age`](#date_age) | Counts fully completed temporal units between two endpoints rather than crossed boundaries. |
| [`date_as_blob`](#date_as_blob) | Serializes the signed days since 1970 as four portable little-endian bytes. |
| [`date_datetime_to_snowflake`](#date_datetime_to_snowflake) | Builds the signed legacy Snowflake timestamp payload from a date-time instant. |
| [`date_datetime_to_snowflake_id`](#date_datetime_to_snowflake_id) | Returns a nonnegative Snowflake code with timestamp bits populated and identity bits cleared. |
| [`date_datetime_to_uuid_v7`](#date_datetime_to_uuid_v7) | Forms a UUID version 7 value from timestamp milliseconds and caller-supplied entropy. |
| [`date_datetime64_to_snowflake`](#date_datetime64_to_snowflake) | Encodes fractional timestamps into signed Snowflake high bits with empty worker and sequence fields. |
| [`date_datetime64_to_snowflake_id`](#date_datetime64_to_snowflake_id) | Packs an instant into an unsigned 64-bit Snowflake identifier relative to a configurable epoch. |
| [`date_diff`](#date_diff) | Counts requested unit boundaries crossed between temporal endpoints. |
| [`date_diff_within`](#date_diff_within) | Tests whether two instants lie within a nonnegative fixed-duration tolerance. |
| [`date_format_datetime`](#date_format_datetime) | Renders temporal fields as text using percent directives and pinned English names. |
| [`date_format_datetime_in_joda_syntax`](#date_format_datetime_in_joda_syntax) | Produces a date-time string from Joda pattern letters in a selected timezone. |
| [`date_from_unix_timestamp`](#date_from_unix_timestamp) | Converts signed Unix seconds to a UTC timestamp or explicitly formatted calendar text. |
| [`date_from_unix_timestamp64_micro`](#date_from_unix_timestamp64_micro) | Reconstructs a six-place timestamp from signed epoch microticks. |
| [`date_from_unix_timestamp64_milli`](#date_from_unix_timestamp64_milli) | Decodes a millisecond epoch counter into a timezone-bearing temporal value. |
| [`date_from_unix_timestamp64_nano`](#date_from_unix_timestamp64_nano) | Interprets an int64 nanosecond counter as a precision-nine instant. |
| [`date_from_unix_timestamp64_second`](#date_from_unix_timestamp64_second) | Creates a whole-second date-time from a signed counter anchored at Unix zero. |
| [`date_from_utc_timestamp`](#date_from_utc_timestamp) | Projects a UTC instant onto the civil clock of an explicit destination zone. |
| [`date_make_date`](#date_make_date) | Constructs a civil date from checked year, month, and day fields. |
| [`date_make_datetime`](#date_make_datetime) | Assembles a timezone-aware whole-second instant from six calendar components. |
| [`date_make_datetime64`](#date_make_datetime64) | Builds a fractional timestamp from civil components and precision-scaled subsecond digits. |
| [`date_name`](#date_name) | Returns English month or weekday labels, or decimal text for other temporal components. |
| [`date_parse_datetime`](#date_parse_datetime) | Parses fully dated text with percent-format directives into a second-resolution timestamp. |
| [`date_parse_datetime_best_effort`](#date_parse_datetime_best_effort) | Finds a matching known calendar format and returns a whole-second instant. |
| [`date_parse_datetime_best_effort_or_null`](#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_zero`](#date_parse_datetime_best_effort_or_zero) | Converts recognizable full dates to seconds and malformed dates to Unix epoch. |
| [`date_parse_datetime_best_effort_us`](#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_or_null`](#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_zero`](#date_parse_datetime_best_effort_us_or_zero) | Supplies a whole-second epoch default for unusable US-order calendar input. |
| [`date_parse_datetime_in_joda_syntax`](#date_parse_datetime_in_joda_syntax) | Matches Joda calendar patterns to produce instants without a fractional component. |
| [`date_parse_datetime_in_joda_syntax_or_null`](#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_zero`](#date_parse_datetime_in_joda_syntax_or_zero) | Returns Unix zero for invalid Joda date text at whole-second resolution. |
| [`date_parse_datetime_or_null`](#date_parse_datetime_or_null) | Turns unconvertible percent-formatted dates into NULL seconds-resolution values. |
| [`date_parse_datetime_or_zero`](#date_parse_datetime_or_zero) | Substitutes a zero-second epoch when explicit percent-pattern parsing fails. |
| [`date_parse_datetime32_best_effort`](#date_parse_datetime32_best_effort) | Recognizes dated timestamp strings within the legacy unsigned 32-bit seconds range. |
| [`date_parse_datetime32_best_effort_or_null`](#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_zero`](#date_parse_datetime32_best_effort_or_zero) | Substitutes Unix zero when a legacy seconds parser cannot convert a dated string. |
| [`date_parse_datetime64`](#date_parse_datetime64) | Decodes percent-pattern date-time text at a requested fractional-second scale. |
| [`date_parse_datetime64_best_effort`](#date_parse_datetime64_best_effort) | Recognizes common full-year date formats and preserves a chosen number of fractional digits. |
| [`date_parse_datetime64_best_effort_or_null`](#date_parse_datetime64_best_effort_or_null) | Produces a nullable fractional instant from recognized complete calendar strings. |
| [`date_parse_datetime64_best_effort_or_zero`](#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_us`](#date_parse_datetime64_best_effort_us) | Interprets ambiguous slash dates month-first while parsing fractional instants. |
| [`date_parse_datetime64_best_effort_us_or_null`](#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_zero`](#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_in_joda_syntax`](#date_parse_datetime64_in_joda_syntax) | Reads calendar strings with Joda field symbols into precision-controlled date-times. |
| [`date_parse_datetime64_in_joda_syntax_or_null`](#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_zero`](#date_parse_datetime64_in_joda_syntax_or_zero) | Uses a scaled zero instant when Joda-formatted calendar text cannot be converted. |
| [`date_parse_datetime64_or_null`](#date_parse_datetime64_or_null) | Treats invalid percent-formatted civil text as missing at the requested timestamp precision. |
| [`date_parse_datetime64_or_zero`](#date_parse_datetime64_or_zero) | Maps failed percent-pattern timestamp conversions to precision-scaled epoch ticks. |
| [`date_sub`](#date_sub) | Subtracts a calendar or fixed unit-count interval while retaining the input temporal precision. |
| [`date_subtract_days`](#date_subtract_days) | Retreats a calendar date by a signed daily displacement. |
| [`date_subtract_hours`](#date_subtract_hours) | Moves a temporal input backward in hour increments from its original clock reading. |
| [`date_subtract_microseconds`](#date_subtract_microseconds) | Removes millionth-second offsets and widens coarse timestamps to six decimal places. |
| [`date_subtract_milliseconds`](#date_subtract_milliseconds) | Computes a millisecond-adjusted earlier instant without truncating thousandth-second detail. |
| [`date_subtract_minutes`](#date_subtract_minutes) | Rewinds a timestamp by minute-sized steps, with negative counts reversing direction. |
| [`date_subtract_months`](#date_subtract_months) | Finds an earlier month-relative date with end-of-month clamping. |
| [`date_subtract_nanoseconds`](#date_subtract_nanoseconds) | Rewinds an instant by nanosecond ticks using full nine-place fractional storage. |
| [`date_subtract_quarters`](#date_subtract_quarters) | Steps backward through three-month calendar periods and clips invalid destination days. |
| [`date_subtract_seconds`](#date_subtract_seconds) | Returns an instant after removing signed whole-second ticks. |
| [`date_subtract_weeks`](#date_subtract_weeks) | Retreats through seven-day spans while preserving date or timestamp shape. |
| [`date_subtract_years`](#date_subtract_years) | Computes an earlier yearly anniversary with leap-day correction. |
| [`date_timestamp_as_blob`](#date_timestamp_as_blob) | Exports signed timestamp ticks as eight little-endian bytes without descriptor metadata. |
| [`date_timezone`](#date_timezone) | Reports the statement timezone configuration as a non-null string. |
| [`date_timezone_of`](#date_timezone_of) | Reads the timezone carried by a timestamp type, normalizing an absent zone to UTC. |
| [`date_timezone_offset`](#date_timezone_offset) | Returns the signed UTC offset in seconds at an instant, including daylight-saving adjustments. |
| [`date_to_day_of_month`](#date_to_day_of_month) | Extracts the one-based position of a civil date within its month. |
| [`date_to_day_of_week`](#date_to_day_of_week) | Numbers weekdays under selectable Monday-first or Sunday-first conventions. |
| [`date_to_day_of_year`](#date_to_day_of_year) | Gives the ordinal day within a calendar year, including February 29 in leap years. |
| [`date_to_hour`](#date_to_hour) | Selects the civil hour from a zoned temporal value on a 24-hour clock. |
| [`date_to_interval`](#date_to_interval) | Creates a structural interval tuple from an integral count and a validated unit label. |
| [`date_to_interval_day`](#date_to_interval_day) | Tags a signed whole-number count with the calendar-day unit. |
| [`date_to_interval_hour`](#date_to_interval_hour) | Packages an integer duration as an hour-count pair for temporal operations. |
| [`date_to_interval_microsecond`](#date_to_interval_microsecond) | Labels a numeric quantity as millionth-second interval ticks. |
| [`date_to_interval_millisecond`](#date_to_interval_millisecond) | Wraps a whole-number amount with thousandth-second interval metadata. |
| [`date_to_interval_minute`](#date_to_interval_minute) | Constructs a minute-denominated pair from an exact signed count. |
| [`date_to_interval_month`](#date_to_interval_month) | Represents a calendar-month displacement without assuming a fixed number of seconds. |
| [`date_to_interval_nanosecond`](#date_to_interval_nanosecond) | Stores a signed billionth-second quantity in a count-unit tuple. |
| [`date_to_interval_quarter`](#date_to_interval_quarter) | Encodes a number of three-month calendar periods for deferred arithmetic. |
| [`date_to_interval_second`](#date_to_interval_second) | Forms a seconds-denominated structural interval from integral text or numbers. |
| [`date_to_interval_week`](#date_to_interval_week) | Preserves seven-day interval intent in a checked count-and-week pair. |
| [`date_to_interval_year`](#date_to_interval_year) | Carries a whole calendar-year displacement without flattening leap-year differences. |
| [`date_to_iso_week`](#date_to_iso_week) | Returns ISO 8601 week numbers with Monday starts and the first-Thursday rule. |
| [`date_to_iso_year`](#date_to_iso_year) | Identifies the ISO week-based year, which may differ near January and December boundaries. |
| [`date_to_last_day_of_month`](#date_to_last_day_of_month) | Finds the final civil date of the month containing a date or timestamp. |
| [`date_to_minute`](#date_to_minute) | Reads the minute-of-hour field after applying the chosen timezone. |
| [`date_to_modified_julian_day`](#date_to_modified_julian_day) | Counts civil days from the Modified Julian origin of 1858-11-17. |
| [`date_to_modified_julian_day_or_null`](#date_to_modified_julian_day_or_null) | Computes a nullable Modified Julian day index when date conversion succeeds. |
| [`date_to_monday`](#date_to_monday) | Returns the Monday date at or before the input civil day. |
| [`date_to_month`](#date_to_month) | Selects the calendar month number, January through December as 1 through 12. |
| [`date_to_quarter`](#date_to_quarter) | Classifies a civil month into one of four calendar quarters. |
| [`date_to_relative_day_num`](#date_to_relative_day_num) | Indexes daily calendar boundaries from the pinned 1970 origin as an unsigned count. |
| [`date_to_relative_hour_num`](#date_to_relative_hour_num) | Numbers elapsed hour positions relative to the versioned Unix-era anchor. |
| [`date_to_relative_minute_num`](#date_to_relative_minute_num) | Returns a nonnegative minute index under the pinned 1970 temporal coordinate system. |
| [`date_to_relative_month_num`](#date_to_relative_month_num) | Counts calendar-month positions after January 1970 rather than extracting month-of-year. |
| [`date_to_relative_quarter_num`](#date_to_relative_quarter_num) | Maps calendar quarters to an unsigned index beginning with the first quarter of 1970. |
| [`date_to_relative_second_num`](#date_to_relative_second_num) | Supplies an unsigned second-position counter anchored to the versioned 1970 epoch. |
| [`date_to_relative_week_num`](#date_to_relative_week_num) | Indexes whole Monday-based weeks after the anchor Monday of 1970-01-05. |
| [`date_to_relative_year_num`](#date_to_relative_year_num) | Measures nonnegative calendar-year displacement from 1970 in a uint16 result. |
| [`date_to_second`](#date_to_second) | Extracts the integer second within a minute, leaving fractional ticks out of the result. |
| [`date_to_start_of_day`](#date_to_start_of_day) | Rounds a temporal value down to its local midnight boundary. |
| [`date_to_start_of_fifteen_minutes`](#date_to_start_of_fifteen_minutes) | Finds the quarter-hour bucket beginning at minute 0, 15, 30, or 45. |
| [`date_to_start_of_five_minutes`](#date_to_start_of_five_minutes) | Floors a clock reading to the preceding five-minute mark. |
| [`date_to_start_of_hour`](#date_to_start_of_hour) | Clears minute, second, and fractional fields to reach the beginning of the current hour. |
| [`date_to_start_of_iso_year`](#date_to_start_of_iso_year) | Finds the Monday opening the ISO week-year that contains a temporal value. |
| [`date_to_start_of_microsecond`](#date_to_start_of_microsecond) | Truncates submicrosecond digits and keeps at least six fractional places. |
| [`date_to_start_of_millisecond`](#date_to_start_of_millisecond) | Drops submillisecond detail to obtain the preceding thousandth-second boundary. |
| [`date_to_start_of_minute`](#date_to_start_of_minute) | Returns the beginning of the minute containing a timestamp. |
| [`date_to_start_of_month`](#date_to_start_of_month) | Finds the first calendar day of the input month, with timestamps set to midnight. |
| [`date_to_start_of_nanosecond`](#date_to_start_of_nanosecond) | Expresses an instant on the nanosecond grid with nine fractional digits. |
| [`date_to_start_of_quarter`](#date_to_start_of_quarter) | Returns January, April, July, or October first for the containing calendar quarter. |
| [`date_to_start_of_second`](#date_to_start_of_second) | Removes a timestamp fractional remainder by flooring to a whole-second boundary. |
| [`date_to_start_of_ten_minutes`](#date_to_start_of_ten_minutes) | Places a timestamp in the preceding ten-minute slot within its civil hour. |
| [`date_to_start_of_week`](#date_to_start_of_week) | Finds a week-opening boundary using Monday by default or Sunday in mode zero. |
| [`date_to_start_of_year`](#date_to_start_of_year) | Rounds civil time down to January 1 of its containing calendar year. |
| [`date_to_timezone`](#date_to_timezone) | Changes a timestamp timezone descriptor while preserving its absolute instant. |
| [`date_to_unix_timestamp`](#date_to_unix_timestamp) | Returns signed Unix epoch seconds, flooring fractional instants toward negative infinity. |
| [`date_to_unix_timestamp64_micro`](#date_to_unix_timestamp64_micro) | Measures an instant as signed microseconds since Unix epoch with downward rounding of finer ticks. |
| [`date_to_unix_timestamp64_milli`](#date_to_unix_timestamp64_milli) | Converts a temporal instant to a signed millisecond epoch counter. |
| [`date_to_unix_timestamp64_nano`](#date_to_unix_timestamp64_nano) | Computes a checked int64 count of nanoseconds before or after 1970-01-01 UTC. |
| [`date_to_unix_timestamp64_second`](#date_to_unix_timestamp64_second) | Extracts the floor-rounded signed epoch counter at whole-second scale from datetime64. |
| [`date_to_utc_timestamp`](#date_to_utc_timestamp) | Resolves local wall-clock fields in a named source timezone into a UTC instant. |
| [`date_to_week`](#date_to_week) | Computes a calendar week number under one of ten numbering modes. |
| [`date_to_year`](#date_to_year) | Reads the unsigned civil year from a date or timezone-adjusted timestamp. |
| [`date_to_year_week`](#date_to_year_week) | Combines a week-based year and week number into a numeric YYYYWW key. |
| [`date_to_yyyymm`](#date_to_yyyymm) | Encodes year and month as a compact six-digit numeric calendar key. |
| [`date_to_yyyymmdd`](#date_to_yyyymmdd) | Packs the civil year, month, and day into an eight-digit YYYYMMDD number. |
| [`date_to_yyyymmddhhmmss`](#date_to_yyyymmddhhmmss) | Builds a sortable numeric YYYYMMDDHHMMSS key from calendar and clock fields. |
| [`date_trunc`](#date_trunc) | Truncates temporal values to a named calendar or fractional boundary. |
| [`extract`](#extract) | Selects an ANSI temporal field, including decimal fractional seconds in SECOND FROM syntax. |
| [`now64`](#now64) | Captures the statement clock with millisecond precision by default and an explicit display zone. |
| [`to_start_of_interval`](#to_start_of_interval) | Buckets timestamps into positive fixed or calendar intervals aligned to an optional origin. |
| [`yesterday`](#yesterday) | Identifies the preceding civil date using the query-start UTC clock. |

## current_date

Returns the UTC calendar day captured when the statement begins.

```text
current_date() -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/nullary` |
| Returns | `date` |
| Aliases | `today` |
| Streaming | One-shot only. |
| Determinism | Stable — constant within one statement. |
| NULL handling | no arguments; never NULL |

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

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

## current_timestamp

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/0..1-ary` |
| Returns | `datetime64(precision,UTC); default precision=0` |
| Aliases | `now` |
| Streaming | One-shot only. |
| Determinism | Stable — constant within one statement. |
| NULL handling | no nullable arguments; never NULL |

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

```sql
current_timestamp() = current_timestamp()
-- → true
```

## date_add

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

```text
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)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `input temporal type; subday operations on date raise INVALID_ARGUMENT` |
| Aliases | `dateAdd` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_add_days

Advances a temporal operand through daily calendar increments.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_days` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_hours` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_microseconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_milliseconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_minutes` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_months` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_nanoseconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_quarters` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_seconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_weeks` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `add_years` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/4-ary+defaults+timezone` |
| Returns | `int32` |
| Aliases | `age` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

```text
date_as_blob(value:date) -> bytes
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `bytes` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults` |
| Returns | `int64` |
| Aliases | `datetime_to_snowflake` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults` |
| Returns | `uint64` |
| Aliases | `datetime_to_snowflake_id` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults` |
| Returns | `uuid` |
| Aliases | `datetime_to_uuid_v7` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults` |
| Returns | `int64` |
| Aliases | `datetime64_to_snowflake` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults` |
| Returns | `uint64` |
| Aliases | `datetime64_to_snowflake_id` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..4-ary+defaults+timezone` |
| Returns | `int64` |
| Aliases | `dateDiff` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `bool` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `string` |
| Aliases | `format_datetime`, `formatDateTime` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `string` |
| Aliases | `format_datetime_in_joda_syntax` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..3-ary+timezone` |
| Returns | `datetime64(0,UTC) without a pattern; string with an explicit pattern` |
| Aliases | `from_unix_timestamp` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(6,tz)` |
| Aliases | `from_unix_timestamp64_micro` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(3,tz)` |
| Aliases | `from_unix_timestamp64_milli` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(9,tz)` |
| Aliases | `from_unix_timestamp64_nano` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `from_unix_timestamp64_second` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+timezone` |
| Returns | `datetime64(p,tz)` |
| Aliases | `from_utc_timestamp` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `date` |
| Aliases | `make_date` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_make_datetime

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/7-ary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `make_datetime` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

```text
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)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/9-ary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `make_datetime64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_parse_datetime

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_best_effort`, `parseDateTimeBestEffort` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_best_effort_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_best_effort_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_best_effort_us` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_best_effort_us_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_best_effort_us_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_in_joda_syntax` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_in_joda_syntax_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_in_joda_syntax_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime32_best_effort` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime32_best_effort_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(0,tz)` |
| Aliases | `parse_datetime32_best_effort_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/4-ary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_best_effort` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_best_effort_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_best_effort_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_best_effort_us` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_best_effort_us_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_best_effort_us_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/4-ary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_in_joda_syntax` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields typed error |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/4-ary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_in_joda_syntax_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/4-ary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_in_joda_syntax_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/4-ary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields NULL |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/4-ary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Aliases | `parse_datetime64_or_zero` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input yields NULL; parse failure yields Unix epoch at target precision |

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

```sql
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.

```text
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)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `input temporal type; subday operations on date raise INVALID_ARGUMENT` |
| Aliases | `dateSub` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_subtract_days

Retreats a calendar date by a signed daily displacement.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_days` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_hours` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_microseconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_milliseconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_minutes` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_months` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_nanoseconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_quarters` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_subtract_seconds

Returns an instant after removing signed whole-second ticks.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_seconds` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_weeks` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_subtract_years

Computes an earlier yearly anniversary with leap-day correction.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64 input retains p for calendar units; subsecond precision=max(input p,unit p); date remains date for day or coarser` |
| Aliases | `subtract_years` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `bytes` |
| Aliases | `timestamp_as_blob` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

```text
date_timezone() -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/nullary` |
| Returns | `string` |
| Aliases | `timezone` |
| Streaming | One-shot only. |
| Determinism | Stable — constant within one statement. |
| NULL handling | no arguments; never NULL |

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

```sql
date_timezone() IS NULL
-- → false
```

## date_timezone_of

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Aliases | `timezone_of` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `int32` |
| Aliases | `timezone_offset` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_day_of_month` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_day_of_week` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint16` |
| Aliases | `to_day_of_year` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_hour` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

```sql
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.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_day

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_day` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_hour

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_hour` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_microsecond

Labels a numeric quantity as millionth-second interval ticks.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_microsecond` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_millisecond

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_millisecond` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_minute

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_minute` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_month

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_month` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_nanosecond

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_nanosecond` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

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

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

## date_to_interval_quarter

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

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_quarter` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Pairs the signed integral count with quarter so consumers can apply three-month calendar steps and their month-end rules. The constructor preserves this unit instead of converting it to a fixed day count. NULL input gives NULL; fractional values raise INVALID_ARGUMENT and int64 overflow raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_interval_quarter(4)
-- → (4, 'quarter')
```

## date_to_interval_second

Forms a seconds-denominated structural interval from integral text or numbers.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_second` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Converts the supplied amount exactly to int64 and returns a tuple with second as the unit string. The result is an interval value for later arithmetic rather than a timestamp or elapsed-clock reading. NULL returns NULL, invalid or fractional text raises INVALID_ARGUMENT, and a count outside int64 raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_interval_second('30')
-- → (30, 'second')
```

## date_to_interval_week

Preserves seven-day interval intent in a checked count-and-week pair.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_week` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns a signed int64 count with the week unit label so temporal arithmetic can apply whole weekly steps. Inputs are checked for integrality and are never rounded to a nearby week. NULL yields NULL, an invalid count raises INVALID_ARGUMENT, and a value exceeding int64 raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_interval_week(3)
-- → (3, 'week')
```

## date_to_interval_year

Carries a whole calendar-year displacement without flattening leap-year differences.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `tuple(int64,string)` |
| Aliases | `to_interval_year` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Constructs a tuple containing the signed count and year, leaving leap-day and anniversary handling to the consuming arithmetic operation. A year is not replaced by a constant number of seconds. NULL input is preserved; fractional counts raise INVALID_ARGUMENT and integer range excess raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_interval_year(1)
-- → (1, 'year')
```

## date_to_iso_week

Returns ISO 8601 week numbers with Monday starts and the first-Thursday rule.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_iso_week` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Computes the ISO week from 1 through 53 after applying the explicit or descriptor timezone. Early January and late December can belong to the adjacent ISO year, so the result may differ from ordinary calendar-week numbering. NULL propagates, and an unknown timezone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_iso_week(DATE '2021-01-01')
-- → 53
```

## date_to_iso_year

Identifies the ISO week-based year, which may differ near January and December boundaries.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint16` |
| Aliases | `to_iso_year` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the year to which the input ISO week belongs, using the timezone-selected civil date. Thus January 1 can still belong to the previous ISO year, and late December can already belong to the next one. NULL yields NULL; an unknown zone raises INVALID_ARGUMENT and a year outside uint16 raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_iso_year(DATE '2021-01-01')
-- → 2020
```

## date_to_last_day_of_month

Finds the final civil date of the month containing a date or timestamp.

```text
date_to_last_day_of_month(x:date|datetime64(p,tz)) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `date` |
| Aliases | `to_last_day_of_month` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns a date for the last day of the input calendar month, accounting for leap years and differing month lengths. Timestamp inputs use their descriptor timezone before choosing that month; date inputs need no zone conversion. NULL returns NULL, and an unrepresentable result raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_last_day_of_month(DATE '2024-02-10')
-- → DATE '2024-02-29'
```

## date_to_minute

Reads the minute-of-hour field after applying the chosen timezone.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_minute` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Extracts an integer from zero through 59 from the civil clock fields of the temporal input. Dates represent midnight for this component and return zero. NULL inputs yield NULL, and a timezone identifier not present in the pinned rules raises INVALID_ARGUMENT.

**Example**

```sql
date_to_minute(TIMESTAMP '2024-03-05 18:42:10+00:00')
-- → 42
```

## date_to_modified_julian_day

Counts civil days from the Modified Julian origin of 1858-11-17.

```text
date_to_modified_julian_day(x:string|date|datetime64(p,tz)) -> int32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `int32` |
| Aliases | `to_modified_julian_day` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns a signed int32 day distance from 1858-11-17, strictly parsing ISO text inputs and using descriptor-local dates for timestamps. The count concerns whole civil days rather than fractional astronomical days. NULL propagates; invalid date text raises INVALID_DATETIME and an out-of-range count raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_modified_julian_day(DATE '1970-01-01')
-- → 40587
```

## date_to_modified_julian_day_or_null

Computes a nullable Modified Julian day index when date conversion succeeds.

```text
date_to_modified_julian_day_or_null(x:string|date|datetime64(p,tz)) -> int32 nullable
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `int32 nullable` |
| Aliases | `to_modified_julian_day_or_null` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL_input_or_invalid_date_returns_NULL |

Converts ISO text, dates, or descriptor-local timestamp dates to a signed day count relative to 1858-11-17. A source date-conversion failure returns NULL, as does an incoming NULL. Other failures remain errors, including TYPE_MISMATCH for incompatible operands and NUMERIC_OVERFLOW for a count that cannot fit int32.

**Example**

```sql
date_to_modified_julian_day_or_null('not-a-date')
-- → NULL
```

## date_to_monday

Returns the Monday date at or before the input civil day.

```text
date_to_monday(x:date|datetime64(p,tz)) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `date` |
| Aliases | `to_monday` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Finds the start of the input ISO week and returns it as a date even when the operand is a timestamp. Timestamp civil days are read in their descriptor timezone, so the result is not necessarily the UTC calendar day. NULL produces NULL; crossing the date range boundary raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_monday(DATE '2024-03-07')
-- → DATE '2024-03-04'
```

## date_to_month

Selects the calendar month number, January through December as 1 through 12.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_month` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Reads the month field from the input date or from a timestamp interpreted in the explicit or descriptor timezone. The result is a uint8 calendar component rather than an elapsed-month count. NULL arguments yield NULL and incompatible timezone names raise INVALID_ARGUMENT.

**Example**

```sql
date_to_month(DATE '2024-09-15')
-- → 9
```

## date_to_quarter

Classifies a civil month into one of four calendar quarters.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_quarter` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns 1 for January through March, 2 for April through June, 3 for July through September, and 4 for October through December. Timestamp inputs use the explicit or descriptor timezone before the month is classified. NULL returns NULL, while an invalid timezone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_quarter(DATE '2024-09-15')
-- → 3
```

## date_to_relative_day_num

Indexes daily calendar boundaries from the pinned 1970 origin as an unsigned count.

```text
date_to_relative_day_num(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_relative_day_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the relative day number anchored at the versioned 1970 calendar epoch rather than a formatted date. Calendar interpretation follows the explicit or descriptor timezone. NULL propagates; negative or uint32-overflowing counts raise NUMERIC_OVERFLOW, and unknown zones raise INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_day_num(DATE '1970-01-02')
-- → 1
```

## date_to_relative_hour_num

Numbers elapsed hour positions relative to the versioned Unix-era anchor.

```text
date_to_relative_hour_num(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_relative_hour_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Produces a uint32 relative hour index under the pinned 1970 epoch convention and the selected timezone rules. The result is intended for relative arithmetic, not display as a calendar date. NULL yields NULL, pre-origin or oversized indexes raise NUMERIC_OVERFLOW, and an invalid zone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_hour_num(TIMESTAMP '1970-01-01 02:30:00+00:00')
-- → 2
```

## date_to_relative_minute_num

Returns a nonnegative minute index under the pinned 1970 temporal coordinate system.

```text
date_to_relative_minute_num(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_relative_minute_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Maps the timestamp or date to its relative minute number with the explicit or descriptor timezone. The epoch belongs to the function revision, so consumers must not interpret this counter as an independently formatted date. NULL inputs remain NULL; negative or uint32-exceeding counts raise NUMERIC_OVERFLOW and unknown zones raise INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_minute_num(TIMESTAMP '1970-01-01 00:03:45+00:00')
-- → 3
```

## date_to_relative_month_num

Counts calendar-month positions after January 1970 rather than extracting month-of-year.

```text
date_to_relative_month_num(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_relative_month_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Computes a relative month index from the versioned 1970 calendar anchor using the timezone-selected civil fields. January 1970 is zero and each subsequent month increases the index by one. NULL returns NULL; a negative or overflowing uint32 index raises NUMERIC_OVERFLOW and an unknown zone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_month_num(DATE '1971-02-01')
-- → 13
```

## date_to_relative_quarter_num

Maps calendar quarters to an unsigned index beginning with the first quarter of 1970.

```text
date_to_relative_quarter_num(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_relative_quarter_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the relative quarter count from the pinned 1970 calendar epoch, combining year displacement with the quarter within that year. The explicit or descriptor timezone selects the civil fields, and the epoch is part of the function revision. NULL propagates, while negative or out-of-range counts raise NUMERIC_OVERFLOW and invalid zones raise INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_quarter_num(DATE '1971-04-01')
-- → 5
```

## date_to_relative_second_num

Supplies an unsigned second-position counter anchored to the versioned 1970 epoch.

```text
date_to_relative_second_num(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_relative_second_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the relative second number as uint32 rather than the signed epoch representation used by general Unix timestamp conversion. Inputs before the pinned origin or beyond the unsigned count range raise NUMERIC_OVERFLOW. NULL remains NULL, and invalid explicit timezone options raise INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_second_num(TIMESTAMP '1970-01-01 00:00:12+00:00')
-- → 12
```

## date_to_relative_week_num

Indexes whole Monday-based weeks after the anchor Monday of 1970-01-05.

```text
date_to_relative_week_num(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_relative_week_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses the pinned Monday 1970-01-05 origin to produce a uint32 relative week count in the chosen calendar timezone. The origin is versioned and differs from the January 1 epoch used for several other relative units. NULL gives NULL; negative or oversized counts raise NUMERIC_OVERFLOW, and unknown zones raise INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_week_num(DATE '1970-01-12')
-- → 1
```

## date_to_relative_year_num

Measures nonnegative calendar-year displacement from 1970 in a uint16 result.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint16` |
| Aliases | `to_relative_year_num` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Subtracts the pinned epoch year from the civil year selected by the explicit or descriptor timezone. This relative index is distinct from the full calendar year and must be interpreted using its versioned origin. NULL propagates; years before the origin or counts exceeding uint16 raise NUMERIC_OVERFLOW, and unknown zones raise INVALID_ARGUMENT.

**Example**

```sql
date_to_relative_year_num(DATE '2024-06-01')
-- → 54
```

## date_to_second

Extracts the integer second within a minute, leaving fractional ticks out of the result.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_second` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the civil second field as uint8 after applying the explicit or descriptor timezone. Fractional timestamp digits do not contribute to this integer component, and date operands contribute zero. NULL returns NULL, while an incompatible timezone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_second(TIMESTAMP '2024-03-05 18:42:10.125+00:00')
-- → 10
```

## date_to_start_of_day

Rounds a temporal value down to its local midnight boundary.

```text
date_to_start_of_day(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_day(value:date, tz:string=descriptor) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_day`, `toStartOfDay` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

For timestamps, clears the clock fields to the beginning of the civil day in the explicit or descriptor timezone while retaining fractional precision. Date inputs already identify a full day and return that same date. NULL propagates, invalid zones raise INVALID_ARGUMENT, and an unrepresentable boundary raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_day(TIMESTAMP '2024-03-05 18:42:10+00:00')
-- → TIMESTAMP '2024-03-05 00:00:00+00:00'
```

## date_to_start_of_fifteen_minutes

Finds the quarter-hour bucket beginning at minute 0, 15, 30, or 45.

```text
date_to_start_of_fifteen_minutes(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_fifteen_minutes(value:date, tz:string=descriptor) -> datetime64(0,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_fifteen_minutes` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Floors the timestamp within its timezone-selected hour to a fifteen-minute boundary and zeros smaller clock fields. Existing timestamp precision is preserved, while dates promote to midnight at precision zero in the selected zone. NULL produces NULL; unknown zones raise INVALID_ARGUMENT and an unrepresentable bucket start raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_fifteen_minutes(TIMESTAMP '2024-03-05 18:42:10+00:00')
-- → TIMESTAMP '2024-03-05 18:30:00+00:00'
```

## date_to_start_of_five_minutes

Floors a clock reading to the preceding five-minute mark.

```text
date_to_start_of_five_minutes(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_five_minutes(value:date, tz:string=descriptor) -> datetime64(0,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_five_minutes` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Chooses the latest minute divisible by five in the timezone-selected hour and clears the second and fractional remainder. The output retains input timestamp precision, and date operands become precision-zero timestamps at midnight. NULL remains NULL; invalid zones raise INVALID_ARGUMENT and range failures raise NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_five_minutes(TIMESTAMP '2024-03-05 18:42:10+00:00')
-- → TIMESTAMP '2024-03-05 18:40:00+00:00'
```

## date_to_start_of_hour

Clears minute, second, and fractional fields to reach the beginning of the current hour.

```text
date_to_start_of_hour(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_hour(value:date, tz:string=descriptor) -> datetime64(0,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_hour` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the hour boundary in the explicit or descriptor timezone without increasing an existing timestamp precision requirement. A date is promoted to a midnight datetime64 value in that zone. NULL inputs return NULL, an invalid zone raises INVALID_ARGUMENT, and a boundary outside the result range raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_hour(TIMESTAMP '2024-03-05 18:42:10+00:00')
-- → TIMESTAMP '2024-03-05 18:00:00+00:00'
```

## date_to_start_of_iso_year

Finds the Monday opening the ISO week-year that contains a temporal value.

```text
date_to_start_of_iso_year(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_iso_year(value:date, tz:string=descriptor) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_iso_year` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the first day of the input ISO year, which can lie in late December of the preceding calendar year. Dates remain dates; timestamps retain precision and use midnight in the explicit or descriptor timezone. NULL yields NULL, invalid zones raise INVALID_ARGUMENT, and an out-of-range result raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_iso_year(DATE '2021-01-01')
-- → DATE '2019-12-30'
```

## date_to_start_of_microsecond

Truncates submicrosecond digits and keeps at least six fractional places.

```text
date_to_start_of_microsecond(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,6),tz)
date_to_start_of_microsecond(value:date, tz:string=descriptor) -> datetime64(6,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_microsecond` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Floors a timestamp to its microsecond boundary, retaining higher declared precision while zeroing digits below the sixth place. Lower-precision values are widened to precision six, and dates become midnight timestamps in the selected zone. NULL propagates; invalid zones raise INVALID_ARGUMENT and scaling outside the output range raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_microsecond(TIMESTAMP '1970-01-01 00:00:00.123456789+00:00')
-- → TIMESTAMP '1970-01-01 00:00:00.123456000+00:00'
```

## date_to_start_of_millisecond

Drops submillisecond detail to obtain the preceding thousandth-second boundary.

```text
date_to_start_of_millisecond(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,3),tz)
date_to_start_of_millisecond(value:date, tz:string=descriptor) -> datetime64(3,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_millisecond` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Retains the first three fractional digits and clears any finer ticks, with result precision at least three and otherwise equal to the input precision. Date operands are promoted to midnight datetime64 using the explicit or descriptor timezone. NULL returns NULL; invalid zones raise INVALID_ARGUMENT and unrepresentable tick scaling raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_millisecond(TIMESTAMP '1970-01-01 00:00:00.123456+00:00')
-- → TIMESTAMP '1970-01-01 00:00:00.123000+00:00'
```

## date_to_start_of_minute

Returns the beginning of the minute containing a timestamp.

```text
date_to_start_of_minute(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_minute(value:date, tz:string=descriptor) -> datetime64(0,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_minute` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Removes the seconds and fractional remainder in the selected timezone, keeping an existing timestamp precision. Supplying a date yields midnight as a precision-zero datetime64 in that timezone. NULL inputs produce NULL, an unknown zone raises INVALID_ARGUMENT, and a result outside the tick range raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_minute(TIMESTAMP '2024-03-05 18:42:10+00:00')
-- → TIMESTAMP '2024-03-05 18:42:00+00:00'
```

## date_to_start_of_month

Finds the first calendar day of the input month, with timestamps set to midnight.

```text
date_to_start_of_month(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_month(value:date, tz:string=descriptor) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_month`, `toStartOfMonth` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Replaces the day field with one and clears the timestamp clock fields using the explicit or descriptor timezone. Date operands return a date, and timestamp operands retain their declared precision. NULL propagates; incompatible zones raise INVALID_ARGUMENT and an unrepresentable month boundary raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_month(DATE '2024-03-25')
-- → DATE '2024-03-01'
```

## date_to_start_of_nanosecond

Expresses an instant on the nanosecond grid with nine fractional digits.

```text
date_to_start_of_nanosecond(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,9),tz)
date_to_start_of_nanosecond(value:date, tz:string=descriptor) -> datetime64(9,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_nanosecond` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns a precision-nine timestamp on its nanosecond boundary; supported inputs have no finer precision, so no fractional information needs discarding. Coarser timestamps are scaled exactly, and dates are promoted to midnight in the requested zone. NULL remains NULL, unknown zones raise INVALID_ARGUMENT, and precision expansion outside int64 ticks raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_nanosecond(TIMESTAMP '1970-01-01 00:00:00.125+00:00')
-- → TIMESTAMP '1970-01-01 00:00:00.125000000+00:00'
```

## date_to_start_of_quarter

Returns January, April, July, or October first for the containing calendar quarter.

```text
date_to_start_of_quarter(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_quarter(value:date, tz:string=descriptor) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_quarter` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Floors the input to the first day of its quarter according to the explicit or descriptor timezone. A date stays a date, while a timestamp keeps its precision and receives midnight clock fields. NULL gives NULL; invalid zones raise INVALID_ARGUMENT and a quarter boundary outside the result range raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_quarter(DATE '2024-05-20')
-- → DATE '2024-04-01'
```

## date_to_start_of_second

Removes a timestamp fractional remainder by flooring to a whole-second boundary.

```text
date_to_start_of_second(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_second(value:date, tz:string=descriptor) -> datetime64(0,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_second` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Zeros subsecond ticks while preserving the declared datetime64 precision and the selected timezone. A date promotes to a precision-zero timestamp at midnight rather than returning a date. NULL inputs return NULL, invalid zones raise INVALID_ARGUMENT, and an unrepresentable boundary raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_second(TIMESTAMP '1970-01-01 00:00:01.125+00:00')
-- → TIMESTAMP '1970-01-01 00:00:01.000+00:00'
```

## date_to_start_of_ten_minutes

Places a timestamp in the preceding ten-minute slot within its civil hour.

```text
date_to_start_of_ten_minutes(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_ten_minutes(value:date, tz:string=descriptor) -> datetime64(0,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_ten_minutes` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Selects a minute boundary from 0, 10, 20, 30, 40, or 50 and clears seconds and fractional ticks. Timestamp precision survives, while dates are promoted to midnight datetime64 in the explicit or descriptor zone. NULL propagates; an invalid timezone raises INVALID_ARGUMENT and a result range failure raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_ten_minutes(TIMESTAMP '2024-03-05 18:47:10+00:00')
-- → TIMESTAMP '2024-03-05 18:40:00+00:00'
```

## date_to_start_of_week

Finds a week-opening boundary using Monday by default or Sunday in mode zero.

```text
date_to_start_of_week(value:datetime64(p,tz), mode:uint8=1, tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_week(value:date, mode:uint8=1, tz:string=descriptor) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_week` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Floors the temporal input to the first day of its selected week, with mode one choosing ISO Monday and mode zero choosing Sunday. Dates return dates, and timestamps retain precision at midnight in the explicit or descriptor timezone. NULL yields NULL; incompatible modes or zones raise INVALID_ARGUMENT and a boundary beyond the type range raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_week(DATE '2024-03-07')
-- → DATE '2024-03-04'
```

## date_to_start_of_year

Rounds civil time down to January 1 of its containing calendar year.

```text
date_to_start_of_year(value:datetime64(p,tz), tz:string=descriptor) -> datetime64(max(p,0),tz)
date_to_start_of_year(value:date, tz:string=descriptor) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64 preserves/promotes precision; date preserved for day and coarser units` |
| Aliases | `to_start_of_year`, `toStartOfYear` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Clears the month and day displacement to produce the first calendar day of the year, using the explicit or descriptor timezone for timestamps. A date result remains a date, and a timestamp preserves precision with midnight clock fields. NULL propagates, unknown zones raise INVALID_ARGUMENT, and unrepresentable year boundaries raise NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_year(DATE '2024-09-15')
-- → DATE '2024-01-01'
```

## date_to_timezone

Changes a timestamp timezone descriptor while preserving its absolute instant.

```text
date_to_timezone(value:datetime64(p,tz), tz:string) -> datetime64(p,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+timezone` |
| Returns | `datetime64(p,tz)` |
| Aliases | `to_timezone` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Reinterprets display and calendar extraction through the requested timezone without altering the stored instant or fractional precision. The zone must be a bind-time constant so the result descriptor is fixed. NULL returns NULL; a nonconstant or invalid timezone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_timezone(TIMESTAMP '2024-01-01 12:00:00+00:00', 'Etc/GMT-2')
-- → TIMESTAMP '2024-01-01 14:00:00+02:00'
```

## date_to_unix_timestamp

Returns signed Unix epoch seconds, flooring fractional instants toward negative infinity.

```text
date_to_unix_timestamp(value:date|datetime64(p,tz)) -> int64
date_to_unix_timestamp(value:string, tz:string=UTC) -> int64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary+defaults+timezone` |
| Returns | `int64` |
| Aliases | `to_unix_timestamp`, `toUnixTimestamp` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Converts dates, timestamps, or parsed text to an int64 count of seconds since Unix zero, including negative pre-1970 values. Text uses an explicit timezone or UTC; invalid dates and leap-second text raise INVALID_DATETIME. NULL propagates, fractions floor rather than truncate toward zero, and an unrepresentable count raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_unix_timestamp(TIMESTAMP '1969-12-31 23:59:59.500+00:00')
-- → -1
```

## date_to_unix_timestamp64_micro

Measures an instant as signed microseconds since Unix epoch with downward rounding of finer ticks.

```text
date_to_unix_timestamp64_micro(value:datetime64(p,tz)) -> int64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `int64` |
| Aliases | `to_unix_timestamp64_micro` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns an int64 microsecond count independent of the display timezone. Higher-precision timestamps floor to microseconds, while lower-precision values scale exactly with checked arithmetic. NULL returns NULL; scaling beyond int64 raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_unix_timestamp64_micro(TIMESTAMP '1970-01-01 00:00:01.000001+00:00')
-- → 1000001
```

## date_to_unix_timestamp64_milli

Converts a temporal instant to a signed millisecond epoch counter.

```text
date_to_unix_timestamp64_milli(value:datetime64(p,tz)) -> int64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `int64` |
| Aliases | `to_unix_timestamp64_milli` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Expresses the timestamp as elapsed thousandths of a second from 1970-01-01 UTC without using its display zone to shift the instant. Finer fractions floor toward negative infinity; coarser ticks are multiplied exactly. NULL input remains NULL and a count outside int64 raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_unix_timestamp64_milli(TIMESTAMP '1969-12-31 23:59:59.999500+00:00')
-- → -1
```

## date_to_unix_timestamp64_nano

Computes a checked int64 count of nanoseconds before or after 1970-01-01 UTC.

```text
date_to_unix_timestamp64_nano(value:datetime64(p,tz)) -> int64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `int64` |
| Aliases | `to_unix_timestamp64_nano` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Scales the timestamp to billionth-second epoch ticks, preserving every supported fractional digit. The narrower range of a nanosecond counter can make an otherwise valid coarse timestamp unrepresentable, producing NUMERIC_OVERFLOW. NULL returns NULL, and the timestamp display timezone does not affect the count.

**Example**

```sql
date_to_unix_timestamp64_nano(TIMESTAMP '1970-01-01 00:00:00.000000007+00:00')
-- → 7
```

## date_to_unix_timestamp64_second

Extracts the floor-rounded signed epoch counter at whole-second scale from datetime64.

```text
date_to_unix_timestamp64_second(value:datetime64(p,tz)) -> int64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `int64` |
| Aliases | `to_unix_timestamp64_second` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Accepts a timestamp and returns int64 seconds since the Unix epoch, ignoring the display timezone. Any fractional remainder is floored, including for negative instants, rather than rounded to the nearest second. NULL propagates, and a result that cannot fit the output counter raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_unix_timestamp64_second(TIMESTAMP '1970-01-01 00:00:01.999+00:00')
-- → 1
```

## date_to_utc_timestamp

Resolves local wall-clock fields in a named source timezone into a UTC instant.

```text
date_to_utc_timestamp(local_value:datetime64(p,tz), source_tz:string) -> datetime64(p,'UTC')
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `datetime64(p,'UTC')` |
| Aliases | `to_utc_timestamp` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Interprets the supplied civil fields in source_tz and returns the corresponding datetime64 instant with a UTC descriptor and the original precision. This performs local-time resolution rather than simply relabeling an existing instant. NULL propagates; ambiguous or nonexistent daylight-saving times without a resolving offset raise INVALID_DATETIME, and unknown zones raise INVALID_ARGUMENT.

**Example**

```sql
date_to_utc_timestamp(TIMESTAMP '2024-01-01 12:00:00+00:00', 'Etc/GMT-2')
-- → TIMESTAMP '2024-01-01 10:00:00+00:00'
```

## date_to_week

Computes a calendar week number under one of ten numbering modes.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `uint8` |
| Aliases | `to_week` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

The mode selects the week-start and first-week convention from zero through nine; mode three gives ISO week numbering. Civil fields come from the explicit or descriptor timezone, and NULL arguments yield NULL. Incompatible modes or zones raise INVALID_ARGUMENT, while an unrepresentable unsigned component raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_week(DATE '2021-01-01', 3)
-- → 53
```

## date_to_year

Reads the unsigned civil year from a date or timezone-adjusted timestamp.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint16` |
| Aliases | `to_year` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the calendar year as uint16 after applying any explicit timezone to a timestamp, otherwise using its descriptor zone. The value is the civil year, independent of ISO week-year boundaries. NULL produces NULL, invalid zones raise INVALID_ARGUMENT, and years outside uint16 raise NUMERIC_OVERFLOW instead of wrapping.

**Example**

```sql
date_to_year(DATE '2024-09-15')
-- → 2024
```

## date_to_year_week

Combines a week-based year and week number into a numeric YYYYWW key.

```text
date_to_year_week(value:date|datetime64(p,tz), mode:uint8=0, tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_year_week` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses a mode from zero through nine to select week numbering and returns the corresponding year multiplied by 100 plus the week. Mode three follows ISO weeks, so dates near New Year can carry the adjacent week-year. NULL propagates; invalid modes or zones raise INVALID_ARGUMENT and unsigned range excess raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_year_week(DATE '2021-01-01', 3)
-- → 202053
```

## date_to_yyyymm

Encodes year and month as a compact six-digit numeric calendar key.

```text
date_to_yyyymm(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_yyyymm` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns year multiplied by 100 plus month using the explicit or descriptor timezone for calendar decomposition. Supported years are zero through 9999; values outside that contract raise INVALID_DATETIME instead of creating an ambiguous key. NULL arguments yield NULL and an unknown zone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_yyyymm(DATE '2024-03-05')
-- → 202403
```

## date_to_yyyymmdd

Packs the civil year, month, and day into an eight-digit YYYYMMDD number.

```text
date_to_yyyymmdd(value:date|datetime64(p,tz), tz:string=descriptor) -> uint32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint32` |
| Aliases | `to_yyyymmdd` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Forms a uint32 calendar key by concatenating the decimal year, month, and day positions, independent of any formatted-string locale. Timestamp fields are selected in the explicit or descriptor timezone, with only years zero through 9999 supported. NULL yields NULL; incompatible years raise INVALID_DATETIME and invalid zones raise INVALID_ARGUMENT.

**Example**

```sql
date_to_yyyymmdd(DATE '2024-03-05')
-- → 20240305
```

## date_to_yyyymmddhhmmss

Builds a sortable numeric YYYYMMDDHHMMSS key from calendar and clock fields.

```text
date_to_yyyymmddhhmmss(value:date|datetime64(p,tz), tz:string=descriptor) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `uint64` |
| Aliases | `to_yyyymmddhhmmss` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Combines the timezone-selected civil year through second into a uint64 decimal key, discarding any fractional seconds. Dates contribute a midnight clock, and the accepted year range is zero through 9999. NULL propagates; an incompatible year raises INVALID_DATETIME and an unknown timezone raises INVALID_ARGUMENT.

**Example**

```sql
date_to_yyyymmddhhmmss(TIMESTAMP '2024-03-05 08:09:10+00:00')
-- → 20240305080910
```

## date_trunc

Truncates temporal values to a named calendar or fractional boundary.

```text
date_trunc(unit:string, value:datetime64(p,tz), tz:string=descriptor) -> datetime64(p,tz)
date_trunc(unit:string, value:date) -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+defaults+timezone` |
| Returns | `input temporal type` |
| Aliases | `to_start_of_day`, `to_start_of_month`, `to_start_of_year`, `dateTrunc` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Floors to the requested unit from nanosecond through year, including ISO Monday weeks and quarters, using the explicit or descriptor timezone. Timestamp precision is retained, while the date overload takes day units and larger. NULL returns NULL; invalid unit or zone choices raise INVALID_ARGUMENT and an unrepresentable boundary raises NUMERIC_OVERFLOW.

**Example**

```sql
date_trunc('quarter', DATE '2024-05-20')
-- → DATE '2024-04-01'
```

## extract

Selects an ANSI temporal field, including decimal fractional seconds in SECOND FROM syntax.

```text
extract(YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|DAY_OF_WEEK|DAY_OF_YEAR|WEEK|ISO_YEAR|EPOCH FROM value:date|datetime64(p,tz)) -> int64
extract(SECOND FROM value:datetime64(p,tz)) -> decimal(11,9)
extract(field:string, value:date|datetime64(p,tz)) -> int64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary+syntax_form` |
| Returns | `int64 except ANSI SECOND uses decimal(11,9)` |
| Aliases | `to_year`, `to_month`, `to_day_of_month`, `date_part` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Extracts the chosen calendar or epoch component from a date or datetime64 value; the ANSI SECOND FROM form preserves fractional seconds as decimal(11,9). Other fields and the callable field-string form return integers, while NULL temporal inputs propagate. Incompatible field names raise INVALID_ARGUMENT, and incompatible operands raise TYPE_MISMATCH.

**Example**

```sql
extract(YEAR FROM DATE '2024-03-05')
-- → 2024
```

## now64

Captures the statement clock with millisecond precision by default and an explicit display zone.

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

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+defaults+timezone` |
| Returns | `datetime64(precision,tz)` |
| Streaming | One-shot only. |
| Determinism | Stable — constant within one statement. |
| NULL handling | NULL explicit argument yields NULL |

Evaluates to one statement-start instant with a constant precision from zero through nine, defaulting to three. An explicit NULL argument yields NULL, and invalid precision or timezone values raise INVALID_ARGUMENT. Continuous and derived plans reject this clock-dependent function with INVALID_ARGUMENT to keep replay deterministic.

**Example**

```sql
NOW64(3) = NOW64(3)
-- → true
```

## to_start_of_interval

Buckets timestamps into positive fixed or calendar intervals aligned to an optional origin.

```text
date_to_start_of_interval(value:datetime64(p,tz), seconds:int64) -> datetime64(p,tz)
date_to_start_of_interval(value:datetime64(p,tz), width:tuple(int64,string), origin:datetime64(p,tz)=unit_epoch, tz:string=descriptor) -> datetime64(p,tz)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..4-ary+defaults+timezone` |
| Returns | `datetime64(p,tz)` |
| Aliases | `toStartOfInterval` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Finds the greatest bucket start at or before the input, using whole seconds or a count-unit tuple and retaining timestamp precision. The optional origin anchors the buckets; otherwise the unit-specific epoch is used, with the explicit or descriptor timezone governing calendar units. NULL propagates, nonpositive widths or invalid units raise INVALID_ARGUMENT, and an unrepresentable boundary raises NUMERIC_OVERFLOW.

**Example**

```sql
date_to_start_of_interval(TIMESTAMP '1970-01-01 00:00:17+00:00', 10)
-- → TIMESTAMP '1970-01-01 00:00:10+00:00'
```

## yesterday

Identifies the preceding civil date using the query-start UTC clock.

```text
yesterday() -> date
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/nullary` |
| Returns | `date` |
| Streaming | One-shot only. |
| Determinism | Stable — constant within one statement. |
| NULL handling | no arguments; never NULL |

Captures the statement-start UTC calendar day and subtracts one day, keeping every call in the statement consistent across midnight. This zero-argument function never returns NULL. Continuous and derived plans reject the clock dependency with INVALID_ARGUMENT; a date-range underflow raises NUMERIC_OVERFLOW.

**Example**

```sql
yesterday() = date_subtract_days(current_date(), 1)
-- → true
```

---

Source: https://nyxdb.io/docs/functions/date-time/