Date/Time Functions

Functions for manipulating and extracting information from date and time values. The examples use fixed dates so their meaning is easy to check. Date parts such as day, month, and hour are unquoted keywords.

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 current_date() as today;

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 current_datetime() as processing_time;

CURRENT_TIMESTAMP

Returns the current timestamp. Timezone behavior depends on the backend.

Inputs:

No inputs

Output:

TIMESTAMP

Purpose:

constant

Example:

select current_timestamp() as processing_time;

This function is not executable on the demo’s DuckDB backend. Use current_datetime() here, or run on a supported database.

With pytrilogy 0.3.404, this expression compiles but the emitted current_timestamp() call does not execute on DuckDB. Use current_datetime() for a current date/time value on DuckDB.

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('2025-03-15') as order_date;

DATETIME

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

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

DATETIME

Purpose:

property

Example:

select datetime('2025-03-15 10:30:00') 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('2025-03-15 10:30:00') as log_timestamp;

DATE_TRUNC {#date-truncate}

Truncates a date/time value to an unquoted precision such as year, month, or day. date_truncate is an alias. The result type depends on the input and backend; cast to date when you need a date-only key.

Inputs:

DATE/TIMESTAMP/DATETIME/STRINGDATE_PART

Output:

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

Purpose:

property

Example:

select date_trunc('2025-03-15'::date, month) as month_start;

DATE_PART

Extracts a subfield as an integer. Use an unquoted date part such as year, month, day, hour, or day_of_week.

Inputs:

DATE/TIMESTAMP/DATETIME/STRINGDATE_PART

Output:

INTEGER (with potential trait)

Purpose:

property

Example:

select date_part('2025-03-15 10:30:00'::datetime, 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('2025-03-01'::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('2025-03-15 10:30:00'::datetime, hour, 3) as reminder_time;

DATE_DIFF

Calculates the difference from the first date/time argument (start) to the second (end), in the units specified by the third argument.

Inputs:

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

Output:

INTEGER

Purpose:

property

Example:

select date_diff('2025-03-01'::date, '2025-03-15'::date, day) as duration_days;

Unix epoch seconds {#unix-to-timestamp}

Use date_add to add epoch seconds to January 1, 1970. The current parser does not expose a callable unix_to_timestamp function.

select date_add('1970-01-01 00:00:00'::datetime, second, 3600) as event_time;

This returns 1970-01-01 01:00:00. The result is a datetime; apply the UTC convention explicitly in your model. For epoch milliseconds, convert to seconds first, taking account of any subsecond precision you need to retain.

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('2025-03-15 10:30:45'::datetime) 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('2025-03-15 10:30:45'::datetime) 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('2025-03-15 10:30:45'::datetime) 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('2025-03-15'::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('2025-03-15'::date) as order_day;

WEEK

Extracts a week number. ISO week behavior and year boundaries depend on the backend.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `week` trait

Purpose:

property

Example:

select week('2025-03-15'::date) as order_week;

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('2025-03-15'::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('2025-03-15'::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('2025-03-15'::date) as calendar_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('2025-03-15'::date) as birth_year;

DAY_OF_WEEK

Extracts the day of the week as an integer. In the DuckDB backend, Sunday is 1 and Saturday is 7; check numbering when changing backends.

Inputs:

DATETIMESTAMPDATETIMESTRING

Output:

INTEGER with `day_of_week` trait

Purpose:

property

Example:

select day_of_week('2025-03-15'::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('2025-03-01'::date, '2025-03-03'::date) as calendar_date;