Date/Time Functions

The largest category, and the one where the databases agree least. KQL works with four temporal kinds — DATE, TIME, TIMESTAMP and INSTANT — described in docs/TEMPORAL.md; this page is about the functions over them.

Where the engines disagreed, the answer is pinned

Most functions here render straight through. Two do not, and they are worth knowing about:

dayofweek counts Monday as 1 and Sunday as 7, the ISO way. Left alone, the engines gave three different numbers for the same Sunday — 0, 1 and 7 — and several took the answer from a session setting (NLS_TERRITORY, DATEFIRST, WEEK_START), so the same query could differ between two connections to the same database. week is ISO-8601 for the same reason: week 1 is the one containing the first Thursday, which is why early January sometimes falls in week 52 or 53 of the previous year.

Where an engine's own answer depends on a session setting, the dialect computes it from a fixed anchor instead of asking the session.

Prefer the named boundaries to date_trunc

day_begin, week_begin, month_begin, quarter_begin, year_begin and their _end partners are the portable way to snap a value to a period. date_trunc(part, value) does the same for 'day', 'month', 'quarter' and 'year' — and only those four, because that is the set every dialect can express. Ask for 'hour' and SQLite used to answer with a blank column rather than an error; it now fails by name.

The _end functions return the last day of the period, always a date. That is deliberate: "the last instant" has no value independent of precision. For a timestamp range, filter half-open with begin and the next begin.

year_month is the month as a key, month_begin is the month as a date

Two ways to bucket by month, and they are not interchangeable. year_month packs the two parts into one number — 202401 — which sorts chronologically by itself, so a GROUP year_month(...) needs no second column and no tie-break. month_begin answers with the first of the month, a real date: use that one when the bucket has to join to a calendar table or take further date arithmetic.

What you should not do is group by year and month side by side. It needs two columns, and it sorts wrong the moment one of them is left out of the ordering.

Distances count whole units, toward zero

days_between, months_between and years_between follow java.time's ChronoUnit: complete units only, and the remainder is dropped in the direction of the sign.

months_between('2023-01-31', '2023-03-30')  →  1    -- the second month is not complete

Durations are literals, not function calls

2d4h        two days and four hours
1y2mo       one year and two months
-2d4h       *both* components negated — 52 hours back, not 2 days back and 4 hours forward

Units are ms s min h d w mo q y, and they must be written largest-first (1y2mo15d, not 15d2mo1y); the other order is a positioned error rather than a silent reordering. A leading minus applies to the whole literal, so mixed signs cannot be written — that is the one shape the notation does not have.

Oracle is the exception worth naming: it keeps YEAR TO MONTH and DAY TO SECOND in separate types, so a duration mixing the two (1y2mo3d) has no value there. Adding it to a date works — that expands into steps — but using it as a value does not.

now, clock_now and today

now is the statement's timestamp and does not move within it. clock_now reads the wall clock at the moment of the call, so it may advance mid-statement on the engines that distinguish the two (PostgreSQL clock_timestamp(), MariaDB SYSDATE()); elsewhere the two are equal. today is the current date.

Parsing, formatting and zones

parse_date, parse_time and parse_timestamp read text with a format mask, to_char writes it. The mask is written once in KQL's vocabulary and translated per dialect — the tokens are on the Data Type Formatting Functions page. SQLite has no mask-based parsing at all and SQL Server takes culture or style codes rather than masks, so both declare the three unsupported.

at_zone and to_utc cross named time zones. SQLite has no time-zone database and declares both unsupported; the other seven support them.

now

now() → TIMESTAMP

Statement timestamp — fixed for the whole statement (PostgreSQL statement_timestamp(), otherwise CURRENT_TIMESTAMP).

Sample query:

// now: the current timestamp.
FIND orders o
FETCH now() current_ts

Generated SQL

duckdb · oracle · snowflake · mssql · mariadb · sqlite · trino

-- now: the current timestamp.
SELECT
  CURRENT_TIMESTAMP AS current_ts
FROM
 orders o

The remaining dialects differ only in this expression:

clock_now

clock_now() → TIMESTAMP

Wall-clock timestamp read at the moment of the call. Unlike now, it may advance within a single statement on dialects that distinguish the two (PostgreSQL clock_timestamp(), MySQL/MariaDB SYSDATE()); elsewhere it equals now.

Sample query:

// clock_now: the current wall-clock timestamp.
FIND orders o
FETCH clock_now() wall_clock_ts

Generated SQL

duckdb · oracle · snowflake · mssql · sqlite · trino

-- clock_now: the current wall-clock timestamp.
SELECT
  CURRENT_TIMESTAMP AS wall_clock_ts
FROM
 orders o

The remaining dialects differ only in this expression:

today

today() → DATE

Current date.

Sample query:

// today: the current date.
FIND orders o
FETCH today() todays_date

Generated SQL

oracle · snowflake · postgresql · mariadb · sqlite · trino

-- today: the current date.
SELECT
  CURRENT_DATE AS todays_date
FROM
 orders o

The remaining dialects differ only in this expression:

year

year(value: date/time) → INTEGER

Extracts the year part of a date or timestamp.

Sample query:

// year: the year component of the order date.
FIND orders o
FETCH o.order_id ASC, year(o.order_date) order_year
LIMIT 20

Generated SQL

duckdb · snowflake · mariadb · trino

-- year: the year component of the order date.
SELECT
  o.order_id
, year(o.order_date) AS order_year
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

sqlite

-- year: the year component of the order date.
SELECT
  o.order_id
, CAST(strftime('%Y', o.order_date) AS INTEGER) AS order_year
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

month

month(value: date/time) → INTEGER

Extracts the month part of a date or timestamp.

Sample query:

// month: the month component of the order date.
FIND orders o
FETCH o.order_id ASC, month(o.order_date) order_month
LIMIT 20

Generated SQL

duckdb · snowflake · mariadb · trino

-- month: the month component of the order date.
SELECT
  o.order_id
, month(o.order_date) AS order_month
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

sqlite

-- month: the month component of the order date.
SELECT
  o.order_id
, CAST(strftime('%m', o.order_date) AS INTEGER) AS order_month
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

day

day(value: date/time) → INTEGER

Extracts the day part of a date or timestamp.

Sample query:

// day: the day-of-month of the order date.
FIND orders o
FETCH o.order_id ASC, day(o.order_date) order_day
LIMIT 20

Generated SQL

duckdb · snowflake · mariadb · trino

-- day: the day-of-month of the order date.
SELECT
  o.order_id
, day(o.order_date) AS order_day
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

sqlite

-- day: the day-of-month of the order date.
SELECT
  o.order_id
, CAST(strftime('%d', o.order_date) AS INTEGER) AS order_day
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

hour

hour(value: date/time) → INTEGER

Extracts the hour part of a date or timestamp.

Sample query:

// hour: the hour component of a TIMESTAMP.
FIND check_temporal c
FETCH hour(c.timestamp_timestamp) hour_of_day

Generated SQL

duckdb · snowflake · mariadb · trino

-- hour: the hour component of a TIMESTAMP.
SELECT
  hour(c.timestamp_timestamp) AS hour_of_day
FROM
 check_temporal c

The remaining dialects differ only in this expression:

minute

minute(value: date/time) → INTEGER

Extracts the minute part of a date or timestamp.

Sample query:

// minute: the minute component of a TIMESTAMP.
FIND check_temporal c
FETCH minute(c.timestamp_timestamp) minute_of_hour

Generated SQL

duckdb · snowflake · mariadb · trino

-- minute: the minute component of a TIMESTAMP.
SELECT
  minute(c.timestamp_timestamp) AS minute_of_hour
FROM
 check_temporal c

The remaining dialects differ only in this expression:

second

second(value: date/time) → INTEGER

Extracts the second part of a date or timestamp.

Sample query:

// second: the second component of a TIMESTAMP.
FIND check_temporal c
FETCH second(c.timestamp_timestamp) second_of_minute

Generated SQL

duckdb · snowflake · mariadb · trino

-- second: the second component of a TIMESTAMP.
SELECT
  second(c.timestamp_timestamp) AS second_of_minute
FROM
 check_temporal c

The remaining dialects differ only in this expression:

quarter

quarter(value: date/time) → INTEGER

Quarter of the year, 1-4.

Sample query:

// quarter: which quarter of the year the order falls in.
FIND orders o
FETCH o.order_id ASC, quarter(o.order_date) order_quarter
LIMIT 20

Generated SQL

duckdb · snowflake · mariadb · trino

-- quarter: which quarter of the year the order falls in.
SELECT
  o.order_id
, quarter(o.order_date) AS order_quarter
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- quarter: which quarter of the year the order falls in.
SELECT
  o.order_id
, DATEPART(quarter, o.order_date) AS order_quarter
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- quarter: which quarter of the year the order falls in.
SELECT
  o.order_id
, ((CAST(strftime('%m', o.order_date) AS INTEGER) + 2) / 3) AS order_quarter
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

week

week(value: date/time) → INTEGER

ISO-8601 week of the year, 1-53. Week 1 is the one containing the first Thursday, so early January can fall in week 52 or 53 of the previous year.

Sample query:

// week: the ISO-8601 week number of the order date.
FIND orders o
FETCH o.order_id ASC, week(o.order_date) order_week
LIMIT 20

Generated SQL

duckdb · trino

-- week: the ISO-8601 week number of the order date.
SELECT
  o.order_id
, week(o.order_date) AS order_week
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- week: the ISO-8601 week number of the order date.
SELECT
  o.order_id
, DATEPART(iso_week, o.order_date) AS order_week
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- week: the ISO-8601 week number of the order date.
SELECT
  o.order_id
, ((CAST(strftime('%j', date(o.order_date, '-3 days', 'weekday 4')) AS INTEGER) - 1) / 7 + 1) AS order_week
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

dayofweek

dayofweek(value: date/time) → INTEGER

Day of the week the ISO way: Monday is 1 and Sunday is 7. Engines disagree wildly here — some count Sunday as 0, some as 1 — so this is normalised, and the same query gives the same number on every database.

Sample query:

// dayofweek: which weekday the order was placed on, Monday = 1.
FIND orders o
FETCH o.order_id ASC, dayofweek(o.order_date) order_weekday
LIMIT 20

Generated SQL

duckdb

-- dayofweek: which weekday the order was placed on, Monday = 1.
SELECT
  o.order_id
, isodow(o.order_date) AS order_weekday
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- dayofweek: which weekday the order was placed on, Monday = 1.
SELECT
  o.order_id
, ((DATEDIFF(day, '19000101', o.order_date) % 7) + 1) AS order_weekday
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- dayofweek: which weekday the order was placed on, Monday = 1.
SELECT
  o.order_id
, (((CAST(strftime('%w', o.order_date) AS INTEGER) + 6) % 7) + 1) AS order_weekday
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

dayofyear

dayofyear(value: date/time) → INTEGER

Day of the year, 1-366.

Sample query:

// dayofyear: how far into the year the order date is.
FIND orders o
FETCH o.order_id ASC, dayofyear(o.order_date) order_dayofyear
LIMIT 20

Generated SQL

duckdb · snowflake · mariadb

-- dayofyear: how far into the year the order date is.
SELECT
  o.order_id
, dayofyear(o.order_date) AS order_dayofyear
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- dayofyear: how far into the year the order date is.
SELECT
  o.order_id
, DATEPART(dayofyear, o.order_date) AS order_dayofyear
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- dayofyear: how far into the year the order date is.
SELECT
  o.order_id
, CAST(strftime('%j', o.order_date) AS INTEGER) AS order_dayofyear
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

year_month

year_month(value: date/time) → INTEGER

Year and month packed into one number, YYYYMM: year_month('2024-01-15') = 202401.

A compact bucket key for monthly grouping: it sorts chronologically on its own, so GROUP year_month(o.order_date) needs neither a second column nor a tie-break. Use month_begin where the bucket has to stay a date — for joining to a calendar table, or for further date arithmetic.

Sample query:

// year_month: the order's month as a sortable YYYYMM key.
FIND orders o
FETCH o.order_id ASC, year_month(o.order_date) order_year_month
LIMIT 20

Generated SQL

duckdb · snowflake · mariadb · trino

-- year_month: the order's month as a sortable YYYYMM key.
SELECT
  o.order_id
, (year(o.order_date) * 100 + month(o.order_date)) AS order_year_month
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

sqlite

-- year_month: the order's month as a sortable YYYYMM key.
SELECT
  o.order_id
, CAST(strftime('%Y%m', o.order_date) AS INTEGER) AS order_year_month
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

parse_date

parse_date(value: TEXT, format: TEXT) → DATE

Parses value into a date using the format mask.

The format mask is written in KQL's own vocabulary and translated per dialect, like to_char's — see the Formatting Functions page for the tokens.

Generated SQL

Unsupported: mssql, sqlite

parse_time

parse_time(value: TEXT, format: TEXT) → TIME

Parses value into a time using the format mask.

The format mask is written in KQL's own vocabulary and translated per dialect, like to_char's — see the Formatting Functions page for the tokens.

Generated SQL

Unsupported: mssql, sqlite

parse_timestamp

parse_timestamp(value: TEXT, format: TEXT) → TIMESTAMP

Parses value into a timestamp using the format mask.

The format mask is written in KQL's own vocabulary and translated per dialect, like to_char's — see the Formatting Functions page for the tokens.

Generated SQL

Unsupported: mssql, sqlite

date_trunc

date_trunc(part: TEXT, value: date/time) → argument-dependent

Truncates value to the given precision, e.g. date_trunc('month', o.order_date).

Sample query:

// date_trunc: truncate the order date to the month.
FIND orders o
FETCH o.order_id ASC, date_trunc('month', o.order_date) month_start
LIMIT 20

Generated SQL

duckdb · snowflake · postgresql · trino

-- date_trunc: truncate the order date to the month.
SELECT
  o.order_id
, date_trunc('month', o.order_date) AS month_start
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- date_trunc: truncate the order date to the month.
SELECT
  o.order_id
, DATEADD(MONTH, DATEDIFF(MONTH, 0, o.order_date), 0) AS month_start
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- date_trunc: truncate the order date to the month.
SELECT
  o.order_id
, date(o.order_date, 'start of month') AS month_start
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

make_date

make_date(year: INTEGER, month: INTEGER, day: INTEGER) → DATE

Builds a date from year, month and day.

Sample query:

// make_date: build a DATE from year, month and day.
FIND orders o
FETCH o.order_id ASC, make_date(2024, 1, 31) built
LIMIT 20

Generated SQL

duckdb · postgresql

-- make_date: build a DATE from year, month and day.
SELECT
  o.order_id
, make_date(2024, 1, 31) AS built
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- make_date: build a DATE from year, month and day.
SELECT
  o.order_id
, DATEFROMPARTS(2024, 1, 31) AS built
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- make_date: build a DATE from year, month and day.
SELECT
  o.order_id
, date(printf('%04d-%02d-%02d', 2024, 1, 31)) AS built
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

make_time

make_time(hour: INTEGER, minute: INTEGER, second: INTEGER) → TIME

Builds a time from hour, minute and second.

Sample query:

// make_time: build a TIME from hour, minute and second.
FIND orders o
FETCH o.order_id ASC, make_time(14, 30, 0) built
LIMIT 20

Generated SQL

duckdb · postgresql

-- make_time: build a TIME from hour, minute and second.
SELECT
  o.order_id
, make_time(14, 30, 0) AS built
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- make_time: build a TIME from hour, minute and second.
SELECT
  o.order_id
, TIMEFROMPARTS(14, 30, 0, 0, 0) AS built
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

Unsupported: sqlite, trino

make_timestamp

make_timestamp(year: INTEGER, month: INTEGER, day: INTEGER, hour: INTEGER, minute: INTEGER, second: INTEGER) → TIMESTAMP

Builds a timestamp from its six components.

Sample query:

// make_timestamp: build a TIMESTAMP from its parts.
FIND orders o
FETCH o.order_id ASC, make_timestamp(2024, 1, 31, 14, 30, 0) built
LIMIT 20

Generated SQL

duckdb · postgresql

-- make_timestamp: build a TIMESTAMP from its parts.
SELECT
  o.order_id
, make_timestamp(2024, 1, 31, 14, 30, 0) AS built
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- make_timestamp: build a TIMESTAMP from its parts.
SELECT
  o.order_id
, DATETIME2FROMPARTS(2024, 1, 31, 14, 30, 0, 0, 0) AS built
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

Unsupported: sqlite, trino

days_between

days_between(start: date/time, end: date/time) → INTEGER

Signed number of complete days from start to end — whole 24-hour periods that fit inside the interval, not midnights crossed.

For two dates the two readings coincide, which is why the difference only shows with a time of day: days_between('2023-01-01 23:00', '2023-01-02 01:00') is 0, because two hours is not a day, even though a midnight lies between them.

Measured before this was settled, the engines split five to three: DuckDB, PostgreSQL, SQL Server, Snowflake and SQLite counted the boundary, MariaDB, Oracle and Trino the elapsed day. All eight now count the elapsed day, matching ChronoUnit.DAYS.

Sample query:

// days_between: signed day distance between two dates.
FIND check_temporal c
FETCH days_between("2023-03-01", "2023-02-01") signed_minus28

Generated SQL

duckdb

-- days_between: signed day distance between two dates.
SELECT
  CAST(trunc(date_diff('second', DATE '2023-03-01', DATE '2023-02-01') / 86400.0) AS INTEGER) AS signed_minus28
FROM
 check_temporal c

The remaining dialects differ only in this expression:

months_between

months_between(start: date/time, end: date/time) → INTEGER

Signed number of whole months completed from start to end; months_between('2023-01-31', '2023-03-30') = 1 — the second month is not complete.

Sample query:

// months_between: whole completed months between two dates.
FIND check_temporal c
FETCH months_between("2023-01-31", "2023-03-31") whole_two

Generated SQL

duckdb · postgresql

-- months_between: whole completed months between two dates.
SELECT
  (CAST(EXTRACT(YEAR FROM age(DATE '2023-03-31', DATE '2023-01-31')) * 12 + EXTRACT(MONTH FROM age(DATE '2023-03-31', DATE '2023-01-31')) AS INTEGER)) AS whole_two
FROM
 check_temporal c

The remaining dialects differ only in this expression:

years_between

years_between(start: date/time, end: date/time) → INTEGER

Signed number of whole years completed from start to end.

Sample query:

// years_between: whole years from order to shipment.
FIND orders o
FETCH o.order_id ASC, years_between(o.order_date, o.shipped_date) years
LIMIT 20

Generated SQL

duckdb · postgresql

-- years_between: whole years from order to shipment.
SELECT
  o.order_id
, CAST(EXTRACT(YEAR FROM age(o.shipped_date, o.order_date)) AS INTEGER) AS years
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- years_between: whole years from order to shipment.
SELECT
  o.order_id
, (DATEDIFF(YEAR, o.order_date, o.shipped_date) - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, o.order_date, o.shipped_date), o.order_date) > o.shipped_date THEN 1 ELSE 0 END) AS years
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- years_between: whole years from order to shipment.
SELECT
  o.order_id
, (CASE WHEN (CAST(strftime('%Y', o.shipped_date) AS INTEGER) - CAST(strftime('%Y', o.order_date) AS INTEGER)) > 0 AND strftime('%m-%d', o.shipped_date) < strftime('%m-%d', o.order_date) THEN (CAST(strftime('%Y', o.shipped_date) AS INTEGER) - CAST(strftime('%Y', o.order_date) AS INTEGER)) - 1 WHEN (CAST(strftime('%Y', o.shipped_date) AS INTEGER) - CAST(strftime('%Y', o.order_date) AS INTEGER)) < 0 AND strftime('%m-%d', o.shipped_date) > strftime('%m-%d', o.order_date) THEN (CAST(strftime('%Y', o.shipped_date) AS INTEGER) - CAST(strftime('%Y', o.order_date) AS INTEGER)) + 1 ELSE (CAST(strftime('%Y', o.shipped_date) AS INTEGER) - CAST(strftime('%Y', o.order_date) AS INTEGER)) END) AS years
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

calendar_distance

calendar_distance(start: date/time, end: date/time) → DURATION

Calendar (civil) distance from start to end as a mixed interval (years/months/days + clock): calendar_distance('2025-01-01', '2026-05-15 12:00') = 1y4mo14d12h. Use ts − ts for a fixed elapsed span.

Sample query:

// calendar_distance: calendar span from order to shipment (NULL when not yet shipped).
FIND orders o
FETCH o.order_id ASC, calendar_distance(o.order_date, o.shipped_date) span
LIMIT 20

Generated SQL

duckdb · postgresql

-- calendar_distance: calendar span from order to shipment (NULL when not yet shipped).
SELECT
  o.order_id
, (CAST(CAST(EXTRACT(EPOCH FROM (o.order_date)) AS BIGINT) AS VARCHAR) || ';' || CAST(CAST(EXTRACT(EPOCH FROM (o.shipped_date)) AS BIGINT) AS VARCHAR)) AS span
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- calendar_distance: calendar span from order to shipment (NULL when not yet shipped).
SELECT
  o.order_id
, CONCAT(DATEDIFF_BIG(SECOND, '1970-01-01', o.order_date), ';', DATEDIFF_BIG(SECOND, '1970-01-01', o.shipped_date)) AS span
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- calendar_distance: calendar span from order to shipment (NULL when not yet shipped).
SELECT
  o.order_id
, (CAST(CAST(strftime('%s', o.order_date) AS INTEGER) AS VARCHAR) || ';' || CAST(CAST(strftime('%s', o.shipped_date) AS INTEGER) AS VARCHAR)) AS span
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

day_add

day_add(value: date/time, n: INTEGER) → argument-dependent

value shifted by n days; n may be any expression.

Sample query:

// day_add: seven days after the order date.
FIND orders o
FETCH o.order_id ASC, day_add(o.order_date, 7) plus_week
LIMIT 20

Generated SQL

duckdb · mariadb

-- day_add: seven days after the order date.
SELECT
  o.order_id
, (o.order_date + INTERVAL (7) DAY) AS plus_week
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- day_add: seven days after the order date.
SELECT
  o.order_id
, DATEADD(DAY, 7, o.order_date) AS plus_week
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- day_add: seven days after the order date.
SELECT
  o.order_id
, date(o.order_date, printf('%+d days', 7)) AS plus_week
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

month_add

month_add(value: date/time, n: INTEGER) → argument-dependent

value shifted by n months, clamped to the end of the month: month_add('2023-01-31', 1) = 2023-02-28.

Sample query:

// month_add: one month after the order date.
FIND orders o
FETCH o.order_id ASC, month_add(o.order_date, 1) next_month
LIMIT 20

Generated SQL

duckdb · mariadb

-- month_add: one month after the order date.
SELECT
  o.order_id
, (o.order_date + INTERVAL (1) MONTH) AS next_month
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- month_add: one month after the order date.
SELECT
  o.order_id
, DATEADD(MONTH, 1, o.order_date) AS next_month
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- month_add: one month after the order date.
SELECT
  o.order_id
, min(date(o.order_date, printf('%+d months', 1)), date(o.order_date, 'start of month', printf('%+d months', 1 + 1), '-1 day')) AS next_month
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

year_add

year_add(value: date/time, n: INTEGER) → argument-dependent

value shifted by n years, clamped (Feb 29 + 1 year = Feb 28).

Sample query:

// year_add: one year after the order date.
FIND orders o
FETCH o.order_id ASC, year_add(o.order_date, 1) next_year
LIMIT 20

Generated SQL

duckdb · mariadb

-- year_add: one year after the order date.
SELECT
  o.order_id
, (o.order_date + INTERVAL (1) YEAR) AS next_year
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- year_add: one year after the order date.
SELECT
  o.order_id
, DATEADD(YEAR, 1, o.order_date) AS next_year
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- year_add: one year after the order date.
SELECT
  o.order_id
, min(date(o.order_date, printf('%+d years', 1)), date(o.order_date, 'start of month', printf('%+d months', 1 * 12 + 1), '-1 day')) AS next_year
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

minute_begin

minute_begin(value: date/time) → argument-dependent

Start of the minute (seconds become 00).

Sample query:

// minute_begin: the start of a TIMESTAMP's minute.
FIND check_temporal c
FETCH minute_begin(c.timestamp_timestamp) start_of_minute

Generated SQL

duckdb · snowflake · postgresql · trino

-- minute_begin: the start of a TIMESTAMP's minute.
SELECT
  date_trunc('minute', c.timestamp_timestamp) AS start_of_minute
FROM
 check_temporal c

The remaining dialects differ only in this expression:

hour_begin

hour_begin(value: date/time) → argument-dependent

Start of the hour (minutes and seconds become 00).

The bucket for sub-daily data, and the one the family was missing: with nothing between minute (the part, 0-59, which folds every hour of every day together) and day_begin, an hourly trend had no portable answer at all. date_trunc('hour', x) was not one -- four of the eight engines render that call by hand and knew only day, month, quarter and year.

Sample query:

// hour_begin: the start of a TIMESTAMP's hour -- the bucket an hourly trend groups by.
FIND check_temporal c
FETCH hour_begin(c.timestamp_timestamp) start_of_hour

Generated SQL

duckdb · snowflake · postgresql · trino

-- hour_begin: the start of a TIMESTAMP's hour -- the bucket an hourly trend groups by.
SELECT
  date_trunc('hour', c.timestamp_timestamp) AS start_of_hour
FROM
 check_temporal c

The remaining dialects differ only in this expression:

day_begin

day_begin(value: date/time) → argument-dependent

Start of the day (time becomes 00:00:00).

Sample query:

// day_begin: midnight at the start of a TIMESTAMP's day.
FIND check_temporal c
FETCH day_begin(c.timestamp_timestamp) start_of_day

Generated SQL

duckdb · snowflake · postgresql · trino

-- day_begin: midnight at the start of a TIMESTAMP's day.
SELECT
  date_trunc('day', c.timestamp_timestamp) AS start_of_day
FROM
 check_temporal c

The remaining dialects differ only in this expression:

week_begin

week_begin(value: date/time) → argument-dependent

Monday of the week (pairs with week_end). Grouping by this is the reliable way to bucket by week: it is a real date, so it sorts, and it has none of the year-boundary trouble of grouping by year and week together — 2026-12-28 is in week 1, but of 2027.

Sample query:

// week_begin: Monday of the order's week — the sortable weekly grouping key.
FIND orders o
FETCH o.order_id ASC, week_begin(o.order_date) week_start
LIMIT 20

Generated SQL

duckdb · postgresql · trino

-- week_begin: Monday of the order's week — the sortable weekly grouping key.
SELECT
  o.order_id
, date_trunc('week', o.order_date) AS week_start
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- week_begin: Monday of the order's week — the sortable weekly grouping key.
SELECT
  o.order_id
, DATEADD(day, -(DATEDIFF(day, 0, o.order_date) % 7), CAST(o.order_date AS DATE)) AS week_start
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- week_begin: Monday of the order's week — the sortable weekly grouping key.
SELECT
  o.order_id
, date(o.order_date, '-6 days', 'weekday 1') AS week_start
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

month_begin

month_begin(value: date/time) → argument-dependent

First day of the month (pairs with month_end).

Sample query:

// month_begin: first day of the order's month.
FIND orders o
FETCH o.order_id ASC, month_begin(o.order_date) m_begin
LIMIT 20

Generated SQL

duckdb · snowflake · postgresql · trino

-- month_begin: first day of the order's month.
SELECT
  o.order_id
, date_trunc('month', o.order_date) AS m_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- month_begin: first day of the order's month.
SELECT
  o.order_id
, DATEADD(MONTH, DATEDIFF(MONTH, 0, o.order_date), 0) AS m_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- month_begin: first day of the order's month.
SELECT
  o.order_id
, date(o.order_date, 'start of month') AS m_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

quarter_begin

quarter_begin(value: date/time) → argument-dependent

First day of the quarter.

Sample query:

// quarter_begin: first day of the order's quarter.
FIND orders o
FETCH o.order_id ASC, quarter_begin(o.order_date) q_begin
LIMIT 20

Generated SQL

duckdb · snowflake · postgresql · trino

-- quarter_begin: first day of the order's quarter.
SELECT
  o.order_id
, date_trunc('quarter', o.order_date) AS q_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- quarter_begin: first day of the order's quarter.
SELECT
  o.order_id
, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, o.order_date), 0) AS q_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- quarter_begin: first day of the order's quarter.
SELECT
  o.order_id
, date(o.order_date, 'start of month', printf('%+d months', -((CAST(strftime('%m', o.order_date) AS INTEGER) - 1) % 3))) AS q_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

year_begin

year_begin(value: date/time) → argument-dependent

First day of the year.

Sample query:

// year_begin: first day of the order's year.
FIND orders o
FETCH o.order_id ASC, year_begin(o.order_date) y_begin
LIMIT 20

Generated SQL

duckdb · snowflake · postgresql · trino

-- year_begin: first day of the order's year.
SELECT
  o.order_id
, date_trunc('year', o.order_date) AS y_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- year_begin: first day of the order's year.
SELECT
  o.order_id
, DATEADD(YEAR, DATEDIFF(YEAR, 0, o.order_date), 0) AS y_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- year_begin: first day of the order's year.
SELECT
  o.order_id
, date(o.order_date, 'start of year') AS y_begin
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

week_end

week_end(value: date/time) → DATE

Sunday of the week.

Sample query:

// week_end: Sunday of the order's week.
FIND orders o
FETCH o.order_id ASC, week_end(o.order_date) week_close
LIMIT 20

Generated SQL

duckdb

-- week_end: Sunday of the order's week.
SELECT
  o.order_id
, CAST(date_trunc('week', o.order_date) + INTERVAL 6 DAY AS DATE) AS week_close
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- week_end: Sunday of the order's week.
SELECT
  o.order_id
, DATEADD(day, 6 - (DATEDIFF(day, 0, o.order_date) % 7), CAST(o.order_date AS DATE)) AS week_close
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- week_end: Sunday of the order's week.
SELECT
  o.order_id
, date(o.order_date, 'weekday 0') AS week_close
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

month_end

month_end(value: date/time) → DATE

Last day of the month (Excel EOMONTH): month_end('2024-02-10') = 2024-02-29.

Sample query:

// month_end: last day of the order's month.
FIND orders o
FETCH o.order_id ASC, month_end(o.order_date) m_end
LIMIT 20

Generated SQL

duckdb · snowflake

-- month_end: last day of the order's month.
SELECT
  o.order_id
, last_day(o.order_date) AS m_end
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- month_end: last day of the order's month.
SELECT
  o.order_id
, EOMONTH(o.order_date) AS m_end
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- month_end: last day of the order's month.
SELECT
  o.order_id
, date(o.order_date, 'start of month', '+1 month', '-1 day') AS m_end
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

quarter_end

quarter_end(value: date/time) → DATE

Last day of the quarter.

Sample query:

// quarter_end: last day of the order's quarter.
FIND orders o
FETCH o.order_id ASC, quarter_end(o.order_date) q_end
LIMIT 20

Generated SQL

duckdb

-- quarter_end: last day of the order's quarter.
SELECT
  o.order_id
, last_day(date_trunc('quarter', o.order_date) + INTERVAL 2 MONTH) AS q_end
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- quarter_end: last day of the order's quarter.
SELECT
  o.order_id
, EOMONTH(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, o.order_date), 0), 2) AS q_end
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- quarter_end: last day of the order's quarter.
SELECT
  o.order_id
, date(o.order_date, 'start of month', printf('%+d months', 3 - ((CAST(strftime('%m', o.order_date) AS INTEGER) - 1) % 3)), '-1 day') AS q_end
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

year_end

year_end(value: date/time) → DATE

Last day of the year (December 31).

Sample query:

// year_end: last day of the order's year.
FIND orders o
FETCH o.order_id ASC, year_end(o.order_date) y_end
LIMIT 20

Generated SQL

duckdb

-- year_end: last day of the order's year.
SELECT
  o.order_id
, last_day(date_trunc('year', o.order_date) + INTERVAL 11 MONTH) AS y_end
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- year_end: last day of the order's year.
SELECT
  o.order_id
, DATEFROMPARTS(YEAR(o.order_date), 12, 31) AS y_end
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- year_end: last day of the order's year.
SELECT
  o.order_id
, date(o.order_date, 'start of year', '+1 year', '-1 day') AS y_end
FROM
 orders o
ORDER BY
  o.order_id ASC
LIMIT 20

at_zone

at_zone(value: date/time, zone: TEXT) → TIMESTAMP

Reads value as a model-zone wall-clock value and returns its wall-clock in the named zone (e.g. for day-bucketing: date(at_zone(o.ts, 'Europe/Berlin'))).

Sample query:

// at_zone: read a model-zone wall-clock value in another zone.
FIND check_temporal c
FETCH at_zone(c.ts_diff_base, 'America/New_York') in_new_york

Generated SQL

duckdb · postgresql

-- at_zone: read a model-zone wall-clock value in another zone.
SELECT
  ((c.ts_diff_base AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York') AS in_new_york
FROM
 check_temporal c

The remaining dialects differ only in this expression:

Unsupported: sqlite

to_utc

to_utc(value: date/time, zone: TEXT) → TIMESTAMP

Inverse of at_zone: reads value as a wall-clock in the named zone and returns the model-zone wall-clock value.

Sample query:

// to_utc: read a zoned wall-clock value and return it in UTC.
FIND check_temporal c
FETCH to_utc(c.ts_diff_base, 'America/New_York') as_utc

Generated SQL

duckdb · postgresql

-- to_utc: read a zoned wall-clock value and return it in UTC.
SELECT
  ((c.ts_diff_base AT TIME ZONE 'America/New_York') AT TIME ZONE 'UTC') AS as_utc
FROM
 check_temporal c

The remaining dialects differ only in this expression:

Unsupported: sqlite