Date/Time Functions

Functions for manipulating and extracting information from date and time values.

CURRENT_DATE

Returns the current date when the query begins execution. This value remains constant throughout the query.

Inputs:

No inputs

Output:

DATE

Purpose:

constant

Example:

SELECT order_id WHERE order_date = CURRENT_DATE();

CURRENT_DATETIME

Returns the current date and time (timestamp) when the query begins execution. This value remains constant throughout the query.

Inputs:

No inputs

Output:

DATETIME

Purpose:

constant

Example:

SELECT event_id, CURRENT_DATETIME() AS processing_time;

CURRENT_TIMESTAMP

Returns the current date and time (timestamp with time zone) when the query begins execution. This value remains constant throughout the query.

Inputs:

No inputs

Output:

TIMESTAMP

Purpose:

constant

Example:

SELECT event_id, CURRENT_TIMESTAMP() AS processing_time;

DATE

Extracts the date part from a timestamp, datetime, or string, or casts a value to the DATE type. If the input is already a DATE, it is returned unchanged.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

DATE

Purpose:

property

Example:

SELECT DATE(user_signup_timestamp) AS signup_date;

DATETIME

Casts the input value (date, timestamp, or string) to the DATETIME type.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

DATETIME

Purpose:

property

Example:

SELECT DATETIME(event_date) AS event_datetime;

TIMESTAMP

Casts the input value (date, datetime, or string) to the TIMESTAMP type.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

TIMESTAMP

Purpose:

property

Example:

SELECT TIMESTAMP(log_entry_string) AS log_timestamp;

DATE_TRUNCATE

Truncates a date/time value to the specified precision ('year', 'month', 'day', etc.). Returns a value of the same type as the input (or DATE if truncating to day or higher from a timestamp/datetime).

Inputs:

DATE/TIMESTAMP/DATETIME/STRINGDATE_PART

Output:

Same as input type (usually DATE or DATETIME/TIMESTAMP)

Purpose:

property

Example:

SELECT DATE_TRUNCATE(created_at, month) AS creation_month;

DATE_PART

Extracts a specific subfield (e.g., 'year', 'month', 'day', 'hour', 'dow' for day of week) from a date/time value as an integer.

Inputs:

DATE/TIMESTAMP/DATETIME/STRINGDATE_PART

Output:

INTEGER (with potential trait)

Purpose:

property

Example:

SELECT DATE_PART(order_timestamp, hour) AS hour_of_order;

DATE_ADD

Adds a specified integer interval to a date/time value. The unit of the interval is specified by the date part argument (e.g., day, month, year).

Inputs:

DATE/TIMESTAMP/DATETIME/STRINGDATE_PARTINTEGER

Output:

Same as first input type (usually DATE or DATETIME/TIMESTAMP)

Purpose:

property

Example:

SELECT DATE_ADD(start_date, day, 14) AS end_date;

DATE_SUB

Subtracts a specified integer interval from a date/time value. The unit of the interval is specified by the date part argument.

Inputs:

DATE/TIMESTAMP/DATETIME/STRINGDATE_PARTINTEGER

Output:

Same as first input type (usually DATE or DATETIME/TIMESTAMP)

Purpose:

property

Example:

SELECT DATE_SUB(event_timestamp, hour, 3) AS reminder_time;

DATE_DIFF

Calculates the integer difference between two date/time values, expressed in the units specified by the date part argument.

Inputs:

DATE/TIMESTAMP/DATETIME/STRINGDATE/TIMESTAMP/DATETIME/STRINGDATE_PART

Output:

INTEGER

Purpose:

property

Example:

SELECT DATE_DIFF(end_date, start_date, day) AS duration_days;

UNIX_TO_TIMESTAMP

Converts an integer Unix epoch timestamp (seconds since 1970-01-01 00:00:00 UTC) to a TIMESTAMP value.

Inputs:

INTEGER

Output:

TIMESTAMP

Purpose:

property

Example:

SELECT UNIX_TO_TIMESTAMP(log_time_seconds) AS event_time;

SECOND

Extracts the second component (0-59) from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `second` trait

Purpose:

property

Example:

SELECT SECOND(event_time) AS event_second;

MINUTE

Extracts the minute component (0-59) from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `minute` trait

Purpose:

property

Example:

SELECT MINUTE(event_time) AS event_minute;

HOUR

Extracts the hour component (0-23) from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `hour` trait

Purpose:

property

Example:

SELECT HOUR(event_time) AS event_hour;

DAY

Extracts the day of the month component (1-31) from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `day` trait

Purpose:

property

Example:

SELECT DAY(event_date) AS event_day_of_month;

DAY_NAME

Extracts the name of the day of the week (e.g., 'Monday') from a date/time value.

Inputs:

DATETIMESTAMPDATETIME

Output:

STRING with `day_name` trait

Purpose:

property

Example:

SELECT DAY_NAME(order_date) AS order_day;

WEEK

Extracts the ISO week number (1-53) from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `week` trait

Purpose:

property

Example:

SELECT WEEK(order_date) AS order_week_iso;

MONTH

Extracts the month component (1-12) from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `month` trait

Purpose:

property

Example:

SELECT MONTH(signup_date) AS signup_month;

MONTH_NAME

Extracts the name of the month (e.g., 'January') from a date/time value.

Inputs:

DATETIMESTAMPDATETIME

Output:

STRING with `month_name` trait

Purpose:

property

Example:

SELECT MONTH_NAME(signup_date) AS signup_month_name;

QUARTER

Extracts the quarter component (1-4) from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `quarter` trait

Purpose:

property

Example:

SELECT QUARTER(fiscal_date) AS fiscal_quarter;

YEAR

Extracts the year component from a date/time value as an integer.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `year` trait

Purpose:

property

Example:

SELECT YEAR(birth_date) AS birth_year;

DAY_OF_WEEK

Extracts the day of the week as an integer (e.g., Sunday=0 or 1, Monday=1 or 2, depending on locale/implementation - typically Sunday=0).

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `day_of_week` trait

Purpose:

property

Example:

SELECT DAY_OF_WEEK(transaction_date) AS transaction_dow;

DATE_SPINE

Generates a continuous series of dates between a start and end date, inclusive. This is useful for creating date dimensions.

Inputs:

DATEDATE

Output:

DATE

Purpose:

key

Example:

SELECT DATE_SPINE(start_date, end_date) AS full_date_range;