Date and time functions

ReferenceApplies to: Unified engine deployments

Temporal operations depend on the input type and session time zone. DATE is a calendar value; TIMESTAMP and TIMESTAMP_NTZ have different zone semantics. The examples use date_dim and UTC.

Extracting date fields

Form	Returns	Meaning
`date_part('year', value)`	Numeric	Calendar year
`date_part('month', value)`	Numeric	Month number
`date_part('day', value)`	Numeric	Day of month
`date_part(field, value)`	Numeric	A named temporal field

SELECT d_date,
       DATE_PART('year', d_date) AS year_number,
       DATE_PART('month', d_date) AS month_number,
       DATE_PART('day', d_date) AS day_number
FROM demo.tpcds.date_dim
ORDER BY d_date_sk
LIMIT 5;

EXTRACT currently lowers to an unregistered function. Use DATE_PART for these fields.

Truncation and conversion

The runnable truncation example uses TIMESTAMP_NTZ. Zone-aware DATE_TRUNC can require the unavailable ICU extension in Native execution. date_trunc(unit, timestamp) returns a timestamp truncated to the named unit. CAST changes a supported temporal representation. Parsing or formatting text requires a function and format accepted by the current engine; format-token languages differ across SQL systems.

SELECT d_date,
       CAST(d_date AS TIMESTAMP_NTZ) AS midnight,
       DATE_TRUNC('month', CAST(d_date AS TIMESTAMP_NTZ)) AS month_start
FROM demo.tpcds.date_dim
ORDER BY d_date_sk
LIMIT 5;

Current date and time

CURRENT_DATE and CURRENT_TIMESTAMP use session semantics. Their values change over time, so examples should check their types and relationship to the session rather than a fixed date.

SELECT CURRENT_DATE AS today, CURRENT_TIMESTAMP AS now;

Intervals

An interval represents a duration or calendar offset. Calendar months differ in length, and daylight-saving transitions can affect zone-aware arithmetic. Validate the supported interval form and unit for the expression and backend. Use explicit casts to avoid mixing text with timestamps implicitly.

SELECT DATE '2024-01-15' AS original_day,
       DATE '2024-01-15' + INTERVAL '1' DAY AS following_day;

Changing spark.sql.session.timeZone affects subsequent operations in the same session. A zone can be recognized by the session yet need an installed backend time-zone extension for execution beyond UTC.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Date and time functions

Extracting date fields

Truncation and conversion

Current date and time

Intervals

​Extracting date fields

​Truncation and conversion

​Current date and time

​Intervals

Extracting date fields

Truncation and conversion

Current date and time

Intervals