Date / Time / Datetime / Timestamp / Interval Functions

Navigation: Aggregate functions · Operator Precedence

Date/Time Functions

This page documents TEMPORAL functions.

Current Date/Time Functions

CURRENT_TIMESTAMP / NOW / CURRENT_DATETIME

Returns current datetime (ZonedDateTime) in UTC.

Syntax:

CURRENT_TIMESTAMP
NOW()
CURRENT_DATETIME

Inputs:

None

Output:

TIMESTAMP / DATETIME

Examples:

SELECT CURRENT_TIMESTAMP AS now;
-- Result: 2025-09-26T12:34:56Z

SELECT NOW() AS current_time;
-- Result: 2025-09-26T12:34:56Z

SELECT CURRENT_DATETIME AS dt;
-- Result: 2025-09-26T12:34:56Z

CURRENT_DATE / CURDATE / TODAY

Returns current date as DATE.

Syntax:

CURRENT_DATE
CURDATE()
TODAY()

Inputs:

None

Output:

DATE

Examples:

SELECT CURRENT_DATE AS today;
-- Result: 2025-09-26

SELECT CURDATE() AS today;
-- Result: 2025-09-26

SELECT TODAY() AS today;
-- Result: 2025-09-26

CURRENT_TIME / CURTIME

Returns current time-of-day.

Syntax:

CURRENT_TIME
CURTIME()

Inputs:

None

Output:

TIME

Examples:

SELECT CURRENT_TIME AS t;
-- Result: 12:34:56

SELECT CURTIME() AS current_time;
-- Result: 12:34:56

Date/Time Arithmetic Functions

+ and - on dates. Since 0.24.0 a date or a timestamp can also be moved and subtracted with the plain operators, by the rules of the SQL engines: DATE - DATE is the calendar days between the two dates (BIGINT, as in PostgreSQL, DuckDB, Oracle and Snowflake); a difference with a TIMESTAMP on either side is the time between them in fractional days (DOUBLE, as Oracle answers for its date with a time of day); DATE + n is the date n days later (DATE, as in PostgreSQL, DuckDB, Oracle and BigQuery) — a fractional n gives a TIMESTAMP whose fraction is a time of day, as in Oracle; TIMESTAMP + n is n days later; a number written as a string ('30') counts as that number, by elasticsql's own rule (PostgreSQL refuses DATE + '1' and reads TIMESTAMP + '1' as one second). *, / and % on a date, two dates added and a number minus a date are refused, as PostgreSQL, DuckDB, Oracle, Trino, Snowflake and SQL Server refuse them. See Date arithmetic.
SELECT ship_date - order_date AS days_to_ship FROM orders;    -- BIGINT
SELECT due_date + 30 AS reminder FROM invoices;               -- DATE
SELECT * FROM orders WHERE order_date > CURRENT_DATE - 7;

INTERVAL

Literal syntax for time intervals.

Syntax:

INTERVAL n UNIT

Inputs:

n - INT value
UNIT - One of: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND

Output:

INTERVAL

Note: INTERVAL is not a standalone type; it can only be used as part of date/datetime arithmetic functions.

Examples:

-- Used with DATE_ADD
SELECT DATE_ADD('2025-01-10'::DATE, INTERVAL 1 MONTH);
-- Result: 2025-02-10

-- Used with DATETIME_SUB
SELECT DATETIME_SUB(NOW(), INTERVAL 7 DAY);
-- Result: 7 days ago

-- Various intervals
INTERVAL 1 YEAR
INTERVAL 3 MONTH
INTERVAL 7 DAY
INTERVAL 2 HOUR
INTERVAL 30 MINUTE
INTERVAL 45 SECOND

DATE_ADD / DATEADD

Adds interval to DATE.

Syntax:

DATE_ADD(date_expr, INTERVAL n UNIT)
DATEADD(date_expr, INTERVAL n UNIT)

Inputs:

date_expr - DATE
INTERVAL n UNIT - where UNIT is one of: YEAR, QUARTER, MONTH, WEEK, DAY

Output:

DATE

Examples:

-- Add 1 month
SELECT DATE_ADD('2025-01-10'::DATE, INTERVAL 1 MONTH) AS next_month;
-- Result: 2025-02-10

-- Add 7 days
SELECT DATE_ADD('2025-01-10'::DATE, INTERVAL 7 DAY) AS next_week;
-- Result: 2025-01-17

-- Add 1 year
SELECT DATEADD('2025-01-10'::DATE, INTERVAL 1 YEAR) AS next_year;
-- Result: 2026-01-10

-- Add 2 weeks
SELECT DATE_ADD('2025-01-10'::DATE, INTERVAL 2 WEEK) AS two_weeks_later;
-- Result: 2025-01-24

-- Add 1 quarter
SELECT DATE_ADD('2025-01-10'::DATE, INTERVAL 1 QUARTER) AS next_quarter;
-- Result: 2025-04-10

DATE_SUB / DATESUB

Subtract interval from DATE.

Syntax:

DATE_SUB(date_expr, INTERVAL n UNIT)
DATESUB(date_expr, INTERVAL n UNIT)

Inputs:

date_expr - DATE
INTERVAL n UNIT - where UNIT is one of: YEAR, QUARTER, MONTH, WEEK, DAY

Output:

DATE

Examples:

-- Subtract 7 days
SELECT DATE_SUB('2025-01-10'::DATE, INTERVAL 7 DAY) AS week_before;
-- Result: 2025-01-03

-- Subtract 1 month
SELECT DATE_SUB('2025-01-10'::DATE, INTERVAL 1 MONTH) AS last_month;
-- Result: 2024-12-10

-- Subtract 1 year
SELECT DATESUB('2025-01-10'::DATE, INTERVAL 1 YEAR) AS last_year;
-- Result: 2024-01-10

-- Subtract 2 weeks
SELECT DATE_SUB('2025-01-10'::DATE, INTERVAL 2 WEEK) AS two_weeks_ago;
-- Result: 2024-12-27

-- Subtract 1 quarter
SELECT DATE_SUB('2025-01-10'::DATE, INTERVAL 1 QUARTER) AS last_quarter;
-- Result: 2024-10-10

DATETIME_ADD / DATETIMEADD

Adds interval to DATETIME / TIMESTAMP.

Syntax:

DATETIME_ADD(datetime_expr, INTERVAL n UNIT)
DATETIMEADD(datetime_expr, INTERVAL n UNIT)

Inputs:

datetime_expr - DATETIME or TIMESTAMP
INTERVAL n UNIT - where UNIT is one of: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

Output:

DATETIME

Examples:

-- Add 1 day
SELECT DATETIME_ADD('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 1 DAY) AS tomorrow;
-- Result: 2025-01-11T12:00:00Z

-- Add 2 hours
SELECT DATETIME_ADD('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 2 HOUR) AS later;
-- Result: 2025-01-10T14:00:00Z

-- Add 30 minutes
SELECT DATETIMEADD('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 30 MINUTE) AS half_hour_later;
-- Result: 2025-01-10T12:30:00Z

-- Add 45 seconds
SELECT DATETIME_ADD('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 45 SECOND) AS seconds_later;
-- Result: 2025-01-10T12:00:45Z

-- Add 1 month
SELECT DATETIME_ADD('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 1 MONTH) AS next_month;
-- Result: 2025-02-10T12:00:00Z

-- Add 1 year
SELECT DATETIME_ADD('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 1 YEAR) AS next_year;
-- Result: 2026-01-10T12:00:00Z

DATETIME_SUB / DATETIMESUB

Subtract interval from DATETIME / TIMESTAMP.

Syntax:

DATETIME_SUB(datetime_expr, INTERVAL n UNIT)
DATETIMESUB(datetime_expr, INTERVAL n UNIT)

Inputs:

datetime_expr - DATETIME or TIMESTAMP
INTERVAL n UNIT - where UNIT is one of: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

Output:

DATETIME

Examples:

-- Subtract 2 hours
SELECT DATETIME_SUB('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 2 HOUR) AS earlier;
-- Result: 2025-01-10T10:00:00Z

-- Subtract 1 day
SELECT DATETIME_SUB('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 1 DAY) AS yesterday;
-- Result: 2025-01-09T12:00:00Z

-- Subtract 30 minutes
SELECT DATETIMESUB('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 30 MINUTE) AS half_hour_ago;
-- Result: 2025-01-10T11:30:00Z

-- Subtract 7 days
SELECT DATETIME_SUB('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 7 DAY) AS week_ago;
-- Result: 2025-01-03T12:00:00Z

-- Subtract 1 month
SELECT DATETIME_SUB('2025-01-10T12:00:00Z'::TIMESTAMP, INTERVAL 1 MONTH) AS last_month;
-- Result: 2024-12-10T12:00:00Z

Date/Time Difference Functions

DATEDIFF / DATE_DIFF / TIMESTAMPDIFF

Difference between 2 dates in a time unit. These functions follow the definitions of the databases they come from: the layout of the arguments decides which date is subtracted from which, and the name decides what is counted.

Syntax:

-- the dates first: date1 - date2
DATEDIFF(date1, date2)              -- MySQL: in days
DATEDIFF(date1, date2, unit)
DATE_DIFF(date1, date2)             -- BigQuery: DATE_DIFF(end_date, start_date, unit)
DATE_DIFF(date1, date2, unit)

-- the unit first: date2 - date1
DATEDIFF(unit, date1, date2)        -- SQL Server, Snowflake, Redshift, DuckDB, Elasticsearch SQL
DATE_DIFF(unit, date1, date2)
TIMESTAMPDIFF(unit, date1, date2)   -- MySQL

Inputs:

date1 - DATE, DATETIME or TIMESTAMP
date2 - DATE, DATETIME or TIMESTAMP
unit - One of: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND (or their ODBC names, SQL_TSI_YEAR …)
- Default, when the dates come first: DAY

Output:

BIGINT

Direction, by layout: every one of these databases computes end − start; the layout decides where the end sits.

The dates first: date1 - date2. DATEDIFF('2025-01-10', '2025-01-01') is 9, as in MySQL; DATE_DIFF('2010-07-07', '2008-12-25', DAY) is 559, as in BigQuery.
The unit first: date2 - date1. DATEDIFF(DAY, '2025-01-01', '2025-01-10') is 9.
Between two DATEs, DATEDIFF(date1, date2) is the same number as the operator date1 - date2 (see Date arithmetic).

Counting, by name:

DATEDIFF and DATE_DIFF count the calendar boundaries crossed, as SQL Server, Snowflake, Redshift, BigQuery and DuckDB do: one second across a year end is 1 YEAR, 1992-09-15 to 1992-11-14 is 2 MONTHs, 10:59 to 11:00 is 1 HOUR. DAY counts calendar days: 2025-01-10T23:30:00Z and 2025-01-11T00:30:00Z are 1 day apart.
TIMESTAMPDIFF counts the whole units elapsed, truncated toward zero, as MySQL does: a month counts once the same day of month and time of day are reached, so 2003-02-01 to 2003-05-01 is 3 MONTHs and 1992-09-15 to 1992-11-14 is 1. A DATE operand is the datetime at 00:00:00.

Calendar:

Every boundary is in UTC.
A WEEK starts on Monday (ISO 8601): the WEEK boundaries are the Mondays crossed, so a Saturday and the next Sunday are 0 weeks apart. A QUARTER starts on January 1, April 1, July 1 and October 1.

Literals:

A string literal is read as the temporal it spells: '2025-01-10' (or '2025/01/10') is a DATE; a literal with a time of day ('2025-01-10 14:00:00', '2025-01-10T14:00:00Z') is a TIMESTAMP, in UTC unless it names a zone.
This holds in every clause, for each row and for each group, and in a computed column (SCRIPT AS): DATEDIFF(MAX(created_at), '2025-01-10 08:00:00', HOUR).
A NULL operand, or an aggregate over a group that has no value, gives NULL.

🔴 Changed in 0.24.0 — these functions follow their vendors' definitions. Statements written against the old behaviour return other values, without an error:

The dates first subtract the second date from the first. DATEDIFF(a, b), DATEDIFF(a, b, unit), DATE_DIFF(a, b) and DATE_DIFF(a, b, unit) returned b - a; they return a - b, as MySQL's DATEDIFF and BigQuery's DATE_DIFF do. The unit-first forms (DATEDIFF(unit, a, b), DATE_DIFF(unit, a, b)) keep b - a.

DATEDIFF and DATE_DIFF count the boundaries crossed. From WEEK up they counted the whole units elapsed between the two calendar dates (a week was 7 days, a month counted once its day of month was reached): DATE_DIFF(MONTH, '1992-09-15', '1992-11-14') was 1 and is 2, and a WEEK boundary is now a Monday. HOUR, MINUTE and SECOND count the boundaries crossed too: DATEDIFF(HOUR, '2025-01-10 10:59:00', '2025-01-10 11:00:00') is 1. DAY still counts calendar days.

A computed column answers what a query answers. In a SCRIPT AS column these functions counted the whole units elapsed between two instants whatever the unit (a DAY was 24 hours), and a string literal operand made the CREATE TABLE fail.

New in 0.24.0, where they failed or were refused before: TIMESTAMPDIFF, the QUARTER unit, HOUR / MINUTE / SECOND over a column or an aggregate, and a string literal operand in a query.

What was deployed before 0.24.0 keeps its 0.23 results until it is re-created. A computed column (SCRIPT AS), an ingest pipeline and a materialized view keep the script they were deployed with: adding a column with ALTER TABLE, SHOW CREATE and re-running the pipeline text that SHOW CREATE PIPELINE returns leave it as it was.

Re-creating it from the same SQL adopts the new definition, and these shapes then return other values: DATEDIFF(a, b[, unit]) and DATE_DIFF(a, b[, unit]) (the sign, and the count), DATE_DIFF(unit, a, b) (the count), DATE_TRUNC(x, WEEK) (the Monday), and DAY in a computed column (calendar days).

CREATE OR REPLACE MATERIALIZED VIEW with an unchanged text keeps the old results: to adopt the new definition, DROP the view and CREATE it.

SHOW CREATE shows the stored text, and that text now means the new definition.

To keep a 0.23 value, swap the dates: DATE_DIFF(a, b, unit) becomes DATE_DIFF(b, a, unit), which restores the sign. Where it counted whole units elapsed, use TIMESTAMPDIFF: TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE) is the age that DATE_DIFF(birthdate, CURRENT_DATE, YEAR) returned.

Examples:

-- MySQL's two-argument form, in days: date1 - date2
SELECT DATEDIFF('2025-01-10'::DATE, '2025-01-01'::DATE) AS diff;
-- Result: 9

-- The dates first, with a unit: still date1 - date2
SELECT DATEDIFF('2025-01-10'::DATE, '2025-01-01'::DATE, DAY) AS diff_days;
-- Result: 9

-- BigQuery's own example: the end date first
SELECT DATE_DIFF('2010-07-07'::DATE, '2008-12-25'::DATE, DAY) AS diff_days;
-- Result: 559

-- The unit first: date2 - date1
SELECT DATEDIFF(DAY, '2025-01-01'::DATE, '2025-01-10'::DATE) AS diff_days;
-- Result: 9

-- Difference in weeks: the Mondays crossed (January 6, 13, 20 and 27)
SELECT DATE_DIFF('2025-01-31'::DATE, '2025-01-01'::DATE, WEEK) AS diff_weeks;
-- Result: 4

-- A week starts on Monday: Saturday to Sunday crosses none, Sunday to Monday one
SELECT DATEDIFF(WEEK, '2025-01-11'::DATE, '2025-01-12'::DATE) AS sat_to_sun,
       DATEDIFF(WEEK, '2025-01-12'::DATE, '2025-01-13'::DATE) AS sun_to_mon;
-- Result: 0, 1

-- Difference in months
SELECT DATEDIFF('2025-06-01'::DATE, '2025-01-01'::DATE, MONTH) AS diff_months;
-- Result: 5

-- Two month boundaries are crossed, one whole month elapses
SELECT DATE_DIFF(MONTH, '1992-09-15'::DATE, '1992-11-14'::DATE) AS boundaries,
       TIMESTAMPDIFF(MONTH, '1992-09-15'::DATE, '1992-11-14'::DATE) AS elapsed;
-- Result: 2, 1

-- MySQL's own example
SELECT TIMESTAMPDIFF(MONTH, '2003-02-01', '2003-05-01') AS diff_months;
-- Result: 3

-- Difference in years
SELECT DATEDIFF('2027-01-01'::DATE, '2025-01-01'::DATE, YEAR) AS diff_years;
-- Result: 2

-- One second across a year end
SELECT DATEDIFF(YEAR, '2025-12-31 23:59:59', '2026-01-01 00:00:00') AS boundaries,
       TIMESTAMPDIFF(YEAR, '2025-12-31 23:59:59', '2026-01-01 00:00:00') AS elapsed;
-- Result: 1, 0

-- Difference in hours (with timestamps)
SELECT DATEDIFF('2025-01-10T14:00:00Z'::TIMESTAMP, '2025-01-10T12:00:00Z'::TIMESTAMP, HOUR) AS diff_hours;
-- Result: 2

-- One minute across an hour
SELECT DATEDIFF(HOUR, '2025-01-10 10:59:00', '2025-01-10 11:00:00') AS boundaries,
       TIMESTAMPDIFF(HOUR, '2025-01-10 10:59:00', '2025-01-10 11:00:00') AS elapsed;
-- Result: 1, 0

-- One hour across midnight
SELECT DATEDIFF(DAY, '2025-01-10 23:30:00', '2025-01-11 00:30:00') AS boundaries,
       TIMESTAMPDIFF(DAY, '2025-01-10 23:30:00', '2025-01-11 00:30:00') AS elapsed;
-- Result: 1, 0

-- Difference in minutes
SELECT DATEDIFF('2025-01-10T12:30:00Z'::TIMESTAMP, '2025-01-10T12:00:00Z'::TIMESTAMP, MINUTE) AS diff_minutes;
-- Result: 30

-- Difference in seconds
SELECT DATEDIFF('2025-01-10T12:00:45Z'::TIMESTAMP, '2025-01-10T12:00:00Z'::TIMESTAMP, SECOND) AS diff_seconds;
-- Result: 45

-- An age in whole years: the years elapsed, not the year boundaries crossed
SELECT TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE) AS age FROM users;

Date/Time Formatting Functions

DATE_FORMAT

Format DATE to VARCHAR.

Syntax:

DATE_FORMAT(date_expr, pattern)

Inputs:

date_expr - DATE
pattern - VARCHAR (MySQL-style pattern)

Output:

VARCHAR

Elasticsearch 6.8 — a bare date column is not supported:

Operand	6.8	7.x, 8.x, 9.x
A literal or cast (`'2025-01-10'::DATE`, `CAST(col AS DATE)`)	Works	Works
A column wrapped in a date function (`DATE_TRUNC(col, DAY)`, `LAST_DAY(col)`, `DATE_ADD(col, INTERVAL 1 DAY)`)	Works	Works
A bare date column (`DATE_FORMAT(created_at, '%Y-%m-%d')`)	Fails — the query is refused with a script error	Works

On Elasticsearch 6.8 a date doc-value reaches Painless as a JodaCompatibleZonedDateTime rather than a java.time.ZonedDateTime, and the formatter requires the latter. Wrap the column (CAST(created_at AS DATE) or DATE_TRUNC(created_at, DAY)) to get the same result on that release. Tracked as issue #371.

Examples:

-- Simple date formatting
SELECT DATE_FORMAT('2025-01-10'::DATE, '%Y-%m-%d') AS fmt;
-- Result: '2025-01-10'

-- Day of the week (full name)
SELECT DATE_FORMAT('2025-01-10'::DATE, '%W') AS weekday;
-- Result: 'Friday'

-- Month name (full)
SELECT DATE_FORMAT('2025-01-10'::DATE, '%M') AS month_name;
-- Result: 'January'

-- Custom format
SELECT DATE_FORMAT('2025-01-10'::DATE, '%W, %M %d, %Y') AS formatted;
-- Result: 'Friday, January 10, 2025'

-- Short format
SELECT DATE_FORMAT('2025-01-10'::DATE, '%m/%d/%y') AS short_date;
-- Result: '01/10/25'

-- Day of month without leading zero
SELECT DATE_FORMAT('2025-01-09'::DATE, '%e') AS day;
-- Result: '9'

-- Abbreviated weekday and month
SELECT DATE_FORMAT('2025-01-10'::DATE, '%a, %b %d') AS abbrev;
-- Result: 'Fri, Jan 10'

DATE_PARSE

Parse VARCHAR into DATE.

Syntax:

DATE_PARSE(string, pattern)

Inputs:

string - VARCHAR
pattern - VARCHAR (MySQL-style pattern)

Output:

DATE

Examples:

-- Parse ISO-style date
SELECT DATE_PARSE('2025-01-10', '%Y-%m-%d') AS d;
-- Result: 2025-01-10

-- Parse with day of week
SELECT DATE_PARSE('Friday 2025-01-10', '%W %Y-%m-%d') AS d;
-- Result: 2025-01-10

-- Parse US format
SELECT DATE_PARSE('01/10/2025', '%m/%d/%Y') AS d;
-- Result: 2025-01-10

-- Parse with month name
SELECT DATE_PARSE('January 10, 2025', '%M %d, %Y') AS d;
-- Result: 2025-01-10

-- Parse with abbreviated month
SELECT DATE_PARSE('Jan 10, 2025', '%b %d, %Y') AS d;
-- Result: 2025-01-10

-- Parse 2-digit year
SELECT DATE_PARSE('10/01/25', '%d/%m/%y') AS d;
-- Result: 2025-01-10

DATETIME_FORMAT

Format DATETIME / TIMESTAMP to VARCHAR with pattern.

Syntax:

DATETIME_FORMAT(datetime_expr, pattern)

Inputs:

datetime_expr - DATETIME or TIMESTAMP
pattern - VARCHAR (MySQL-style pattern)

Output:

VARCHAR

Elasticsearch 6.8 — a bare date column is not supported:

Operand	6.8	7.x, 8.x, 9.x
A literal or cast (`'2025-01-10'::DATE`, `CAST(col AS DATE)`)	Works	Works
A column wrapped in a date function (`DATE_TRUNC(col, DAY)`, `LAST_DAY(col)`, `DATE_ADD(col, INTERVAL 1 DAY)`)	Works	Works
A bare date column (`DATETIME_FORMAT(created_at, '%Y-%m-%d')`)	Fails — the query is refused with a script error	Works

Examples:

-- Format with seconds and microseconds
SELECT DATETIME_FORMAT('2025-01-10T12:00:00.123456Z'::TIMESTAMP, '%Y-%m-%d %H:%i:%s.%f') AS s;
-- Result: '2025-01-10 12:00:00.123456'

-- Format 12-hour clock with AM/PM
SELECT DATETIME_FORMAT('2025-01-10T13:45:30Z'::TIMESTAMP, '%Y-%m-%d %h:%i:%s %p') AS s;
-- Result: '2025-01-10 01:45:30 PM'

-- Format with full weekday name
SELECT DATETIME_FORMAT('2025-01-10T13:45:30Z'::TIMESTAMP, '%W, %Y-%m-%d') AS s;
-- Result: 'Friday, 2025-01-10'

-- Full datetime with day name and month name
SELECT DATETIME_FORMAT('2025-01-10T13:45:30Z'::TIMESTAMP, '%W, %M %d, %Y at %h:%i %p') AS s;
-- Result: 'Friday, January 10, 2025 at 01:45 PM'

-- ISO 8601 format
SELECT DATETIME_FORMAT('2025-01-10T13:45:30Z'::TIMESTAMP, '%Y-%m-%dT%H:%i:%s') AS s;
-- Result: '2025-01-10T13:45:30'

-- 24-hour format
SELECT DATETIME_FORMAT('2025-01-10T13:45:30Z'::TIMESTAMP, '%H:%i:%s') AS s;
-- Result: '13:45:30'

-- 12-hour format
SELECT DATETIME_FORMAT('2025-01-10T13:45:30Z'::TIMESTAMP, '%h:%i:%s %p') AS s;
-- Result: '01:45:30 PM'

DATETIME_PARSE

Parse VARCHAR into DATETIME / TIMESTAMP.

Syntax:

DATETIME_PARSE(string, pattern)

Inputs:

string - VARCHAR
pattern - VARCHAR (MySQL-style pattern)

Output:

DATETIME

Examples:

-- Parse full datetime with microseconds
SELECT DATETIME_PARSE('2025-01-10 12:00:00.123456', '%Y-%m-%d %H:%i:%s.%f') AS dt;
-- Result: 2025-01-10T12:00:00.123456Z

-- Parse 12-hour clock with AM/PM
SELECT DATETIME_PARSE('2025-01-10 01:45:30 PM', '%Y-%m-%d %h:%i:%s %p') AS dt;
-- Result: 2025-01-10T13:45:30Z

-- Parse ISO 8601 format
SELECT DATETIME_PARSE('2025-01-10T13:45:30', '%Y-%m-%dT%H:%i:%s') AS dt;
-- Result: 2025-01-10T13:45:30Z

-- Parse with full day and month names
SELECT DATETIME_PARSE('Friday, January 10, 2025 at 01:45 PM', '%W, %M %d, %Y at %h:%i %p') AS dt;
-- Result: 2025-01-10T13:45:00Z

-- Parse 24-hour format
SELECT DATETIME_PARSE('2025-01-10 13:45:30', '%Y-%m-%d %H:%i:%s') AS dt;
-- Result: 2025-01-10T13:45:30Z

-- Parse abbreviated names
SELECT DATETIME_PARSE('Fri, Jan 10, 2025 1:45 PM', '%a, %b %d, %Y %h:%i %p') AS dt;
-- Result: 2025-01-10T13:45:00Z

Date/Time Truncation Function

DATE_TRUNC

Truncate date/datetime to a unit.

Syntax:

DATE_TRUNC(date_or_datetime_expr, unit)

Inputs:

date_or_datetime_expr - DATE or DATETIME
unit - One of: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

Output:

DATE or DATETIME (same type as input)

Week:

WEEK truncates to the Monday that starts the ISO-8601 week, at 00:00 UTC, in every clause: a GROUP BY DATE_TRUNC(…, WEEK) buckets on that same Monday.

🔴 Changed in 0.24.0 — DATE_TRUNC(…, WEEK) is the Monday that starts the week. Before 0.24.0, a SELECT item, a WHERE condition or a computed column (SCRIPT AS) moved a date FORWARD to the Sunday that ends its week (2025-01-15 gave 2025-01-19), while a GROUP BY of the same expression bucketed on the Monday.

Examples:

-- Truncate to start of month
SELECT DATE_TRUNC('2025-01-15'::DATE, MONTH) AS start_month;
-- Result: 2025-01-01

-- Truncate to start of year
SELECT DATE_TRUNC('2025-06-15'::DATE, YEAR) AS start_year;
-- Result: 2025-01-01

-- Truncate to start of quarter
SELECT DATE_TRUNC('2025-05-15'::DATE, QUARTER) AS start_quarter;
-- Result: 2025-04-01

-- Truncate to start of week
SELECT DATE_TRUNC('2025-01-15'::DATE, WEEK) AS start_week;
-- Result: 2025-01-13 (Monday)

-- Truncate datetime to hour
SELECT DATE_TRUNC('2025-01-10T12:34:56Z'::TIMESTAMP, HOUR) AS start_hour;
-- Result: 2025-01-10T12:00:00Z

-- Truncate datetime to minute
SELECT DATE_TRUNC('2025-01-10T12:34:56Z'::TIMESTAMP, MINUTE) AS start_minute;
-- Result: 2025-01-10T12:34:00Z

-- Truncate datetime to day
SELECT DATE_TRUNC('2025-01-10T12:34:56Z'::TIMESTAMP, DAY) AS start_day;
-- Result: 2025-01-10T00:00:00Z

Date/Time Extraction Functions

EXTRACT

Extract field from date or datetime.

Syntax:

EXTRACT(unit FROM date_expr)

Inputs:

unit - One of: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
date_expr - DATE or DATETIME

Output:

INT / BIGINT

Examples:

-- Extract year
SELECT EXTRACT(YEAR FROM '2025-01-10T12:00:00Z'::TIMESTAMP) AS y;
-- Result: 2025

-- Extract month
SELECT EXTRACT(MONTH FROM '2025-01-10T12:00:00Z'::TIMESTAMP) AS m;
-- Result: 1

-- Extract day
SELECT EXTRACT(DAY FROM '2025-01-10T12:00:00Z'::TIMESTAMP) AS d;
-- Result: 10

-- Extract hour
SELECT EXTRACT(HOUR FROM '2025-01-10T12:34:56Z'::TIMESTAMP) AS h;
-- Result: 12

-- Extract minute
SELECT EXTRACT(MINUTE FROM '2025-01-10T12:34:56Z'::TIMESTAMP) AS min;
-- Result: 34

-- Extract second
SELECT EXTRACT(SECOND FROM '2025-01-10T12:34:56Z'::TIMESTAMP) AS sec;
-- Result: 56

-- Extract quarter
SELECT EXTRACT(QUARTER FROM '2025-05-10'::DATE) AS q;
-- Result: 2

-- Extract week
SELECT EXTRACT(WEEK FROM '2025-01-15'::DATE) AS w;
-- Result: 3

Individual Extraction Functions

YEAR / QUARTER / MONTH / WEEK / DAY

Syntax:

YEAR(date_expr)
QUARTER(date_expr)
MONTH(date_expr)
WEEK(date_expr)
DAY(date_expr)

Examples:

-- Extract year
SELECT YEAR('2025-01-10'::DATE) AS year;
-- Result: 2025

-- Extract quarter (1-4)
SELECT QUARTER('2025-05-10'::DATE) AS q;
-- Result: 2

-- Extract month (1-12)
SELECT MONTH('2025-01-10'::DATE) AS month;
-- Result: 1

-- Extract ISO week number (1-53)
SELECT WEEK('2025-01-01'::DATE) AS w;
-- Result: 1

-- Extract day of month (1-31)
SELECT DAY('2025-01-10'::DATE) AS day;
-- Result: 10

HOUR / MINUTE / SECOND

Syntax:

HOUR(timestamp)
MINUTE(timestamp)
SECOND(timestamp)

Examples:

-- Extract hour (0-23)
SELECT HOUR('2025-01-10T12:34:56Z'::TIMESTAMP) AS hour;
-- Result: 12

-- Extract minute (0-59)
SELECT MINUTE('2025-01-10T12:34:56Z'::TIMESTAMP) AS minute;
-- Result: 34

-- Extract second (0-59)
SELECT SECOND('2025-01-10T12:34:56Z'::TIMESTAMP) AS second;
-- Result: 56

Sub-Second Extraction Functions

NANOSECOND / MICROSECOND / MILLISECOND

Sub-second extraction from timestamps.

Syntax:

NANOSECOND(datetime_expr)
MICROSECOND(datetime_expr)
MILLISECOND(datetime_expr)

Inputs:

datetime_expr - DATETIME or TIMESTAMP

Output:

INT

Examples:

-- Extract milliseconds
SELECT MILLISECOND('2025-01-01T12:00:00.123Z'::TIMESTAMP) AS ms;
-- Result: 123

-- Extract microseconds
SELECT MICROSECOND('2025-01-01T12:00:00.123456Z'::TIMESTAMP) AS us;
-- Result: 123456

-- Extract nanoseconds
SELECT NANOSECOND('2025-01-01T12:00:00.123456789Z'::TIMESTAMP) AS ns;
-- Result: 123456789

Special Date/Time Functions

LAST_DAY

Last day of month for a date.

Syntax:

LAST_DAY(date_expr)

Inputs:

date_expr - DATE

Output:

DATE

Examples:

-- Last day of February (non-leap year)
SELECT LAST_DAY('2025-02-15'::DATE) AS ld;
-- Result: 2025-02-28

-- Last day of February (leap year)
SELECT LAST_DAY('2024-02-15'::DATE) AS ld;
-- Result: 2024-02-29

-- Last day of January
SELECT LAST_DAY('2025-01-10'::DATE) AS ld;
-- Result: 2025-01-31

-- Last day of current month
SELECT LAST_DAY(CURRENT_DATE) AS month_end;

EPOCHDAY

Days since epoch (1970-01-01).

Syntax:

EPOCHDAY(date_expr)

Inputs:

date_expr - DATE

Output:

BIGINT

Examples:

-- Day after epoch
SELECT EPOCHDAY('1970-01-02'::DATE) AS d;
-- Result: 1

-- Epoch day
SELECT EPOCHDAY('1970-01-01'::DATE) AS d;
-- Result: 0

-- Days since epoch for a recent date
SELECT EPOCHDAY('2025-01-10'::DATE) AS d;
-- Result: 20098

OFFSET_SECONDS

Timezone offset in seconds.

Syntax:

OFFSET_SECONDS(timestamp_expr)

Inputs:

timestamp_expr - TIMESTAMP with timezone

Output:

INT

Examples:

-- UTC+2 (7200 seconds = 2 hours)
SELECT OFFSET_SECONDS('2025-01-01T12:00:00+02:00'::TIMESTAMP) AS off;
-- Result: 7200

-- UTC (0 seconds)
SELECT OFFSET_SECONDS('2025-01-01T12:00:00Z'::TIMESTAMP) AS off;
-- Result: 0

-- UTC-5 (-18000 seconds = -5 hours)
SELECT OFFSET_SECONDS('2025-01-01T12:00:00-05:00'::TIMESTAMP) AS off;
-- Result: -18000

Supported MySQL-style Date/Time Patterns

The following patterns are supported in DATE_FORMAT, DATE_PARSE, DATETIME_FORMAT, and DATETIME_PARSE functions:

Patternc	Descriptionc	Example Outputc
`%Y`	Year (4 digits)	`2025`
`%y`	Year (2 digits)	`25`
`%m`	Month (2 digits, 01-12)	`01`
`%c`	Month (1-12, no leading zero)	`1`
`%M`	Month name (full)	`January`
`%b`	Month name (abbreviated)	`Jan`
`%d`	Day of month (2 digits, 01-31)	`10`
`%e`	Day of month (1-31, no leading zero)	`9`
`%W`	Weekday name (full)	`Friday`
`%a`	Weekday name (abbreviated)	`Fri`
`%H`	Hour (00-23, 24-hour format)	`13`
`%h`	Hour (01-12, 12-hour format)	`01`
`%I`	Hour (01-12, synonym for %h)	`01`
`%i`	Minutes (00-59)	`45`
`%s`	Seconds (00-59)	`30`
`%f`	Fractional seconds, any precision	`123456`
`%p`	AM/PM marker	`AM` / `PM`

Pattern Combination Examples:

-- Full date and time
'%Y-%m-%d %H:%i:%s'          -- 2025-01-10 13:45:30

-- US format with 12-hour time
'%m/%d/%Y %h:%i %p'           -- 01/10/2025 01:45 PM

-- Long format with names
'%W, %M %d, %Y'               -- Friday, January 10, 2025

-- ISO 8601 with microseconds
'%Y-%m-%dT%H:%i:%s.%f'        -- 2025-01-10T13:45:30.123456

%f is variable width. It formats a value at its actual precision — .123456 for microseconds, .123 for milliseconds — and when parsing it accepts any number of fractional digits, or none at all. A decimal point written immediately before it belongs to the fraction, so a value with no fractional part formats as 12:00:00 rather than 12:00:00..


-- Short format
'%d-%b-%y'                    -- 10-Jan-25

-- Time only (24-hour)
'%H:%i:%s'                    -- 13:45:30

-- Time only (12-hour)
'%h:%i:%s %p'                 -- 01:45:30 PM

-- European format
'%d/%m/%Y'                    -- 10/01/2025

-- Year and month
'%Y-%m'                       -- 2025-01

-- Month and day with names
'%b %d'                       -- Jan 10

Back to index

FilesExpand file tree

functions_date_time.md

Latest commit

History

functions_date_time.md

File metadata and controls

Date / Time / Datetime / Timestamp / Interval Functions

Date/Time Functions

Current Date/Time Functions

CURRENT_TIMESTAMP / NOW / CURRENT_DATETIME

CURRENT_DATE / CURDATE / TODAY

CURRENT_TIME / CURTIME

Date/Time Arithmetic Functions

INTERVAL

DATE_ADD / DATEADD

DATE_SUB / DATESUB

DATETIME_ADD / DATETIMEADD

DATETIME_SUB / DATETIMESUB

Date/Time Difference Functions

DATEDIFF / DATE_DIFF / TIMESTAMPDIFF

Date/Time Formatting Functions

DATE_FORMAT

DATE_PARSE

DATETIME_FORMAT

DATETIME_PARSE

Date/Time Truncation Function

DATE_TRUNC

Date/Time Extraction Functions

EXTRACT

Individual Extraction Functions

Sub-Second Extraction Functions

Special Date/Time Functions

LAST_DAY

EPOCHDAY

OFFSET_SECONDS

Supported MySQL-style Date/Time Patterns