Datetime Functions

`current_date`

Returns the current date as of the start of the query. Examples:

examples.sql

SELECT current_date() AS value; -- value '2024-06-20'

Supported Signatures

function current_date() returns date

Note: current_date() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`current_time`

Returns the current time with time zone as of the start of the query. Examples:

examples.sql

SELECT current_time() AS value; -- value '13:52:34.245364'

Supported Signatures

function current_time() returns time

Note: current_time() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`current_timestamp`

Returns the current timestamp with time zone as of the start of the query, with 3 digits of subsecond precision, Examples:

examples.sql

SELECT current_timestamp() AS value; -- value '2024-06-21T13:53:41.809016Z'

Supported Signatures

function current_timestamp() returns timestamp
function current_timestamp(0) returns timestamp(0)
function current_timestamp(3) returns timestamp(3)
function current_timestamp(6) returns timestamp(6)
function current_timestamp(9) returns timestamp(9)

Note: current_timestamp() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`date`

This is an alias for CAST(x AS date). Supported Signatures

function date(varchar) returns date

🔗 Official Documentation

`date_diff`

Takes a unit, timestamp1, and timestamp2. Returns timestamp2 - timestamp1 expressed in terms of unit. Examples:

examples.sql

SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00') AS value; -- value '86400'
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC') AS value; -- value '24'
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02') AS value; -- value '1'
SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789') AS value; -- value '86400'
SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789') AS value; -- value '86400123'

Supported Signatures

function date_diff(varchar, date, date) returns bigint
function date_diff(varchar, time(p), time(p)) returns bigint
function date_diff(varchar, timestamp(p), timestamp(p)) returns bigint

🔗 Official Documentation

`date_format`

Formats timestamp as a string using format. Examples:

examples.sql

SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H') AS value; -- value '10-20-2022 05'

Supported Signatures

function date_format(timestamp(p), varchar) returns varchar

🔗 Official Documentation

`date_parse`

Parses string into a timestamp using format. Supported Signatures

function date_parse(varchar, varchar) returns timestamp

🔗 Official Documentation

`date_trunc`

Returns x truncated to unit. Supported Signatures

function date_trunc(varchar, timestamp(p)) returns timestamp(p)

🔗 Official Documentation

`day`

Returns the day of the month from x. Examples:

examples.sql

SELECT day(date('2022-10-20')) AS value; -- value '20'

Supported Signatures

function day(date) returns bigint
function day(timestamp(p)) returns bigint

🔗 Official Documentation

`day_of_month`

This is an alias for day(). Supported Signatures

function day_of_month(date) returns bigint
function day_of_month(timestamp(p)) returns bigint

🔗 Official Documentation

`day_of_week`

Returns the ISO day of the week from x. The value ranges from 1 (Monday) to 7 (Sunday). Supported Signatures

function day_of_week(date) returns bigint
function day_of_week(timestamp(p)) returns bigint

🔗 Official Documentation

`day_of_year`

Returns the day of the year from x. The value ranges from 1 to 366. Supported Signatures

function day_of_year(date) returns bigint
function day_of_year(timestamp(p)) returns bigint

🔗 Official Documentation

`dow`

This is an alias for day_of_week(). Supported Signatures

function dow(date) returns bigint
function dow(timestamp(p)) returns bigint

🔗 Official Documentation

`doy`

This is an alias for day_of_year(). Supported Signatures

function doy(date) returns bigint
function doy(timestamp(p)) returns bigint

🔗 Official Documentation

`from_unixtime`

Returns the UNIX timestamp unixtime as a timestamp with time zone. unixtime is the number of seconds since 1970-01-01 00.00.00 UTC. Examples:

examples.sql

SELECT from_unixtime(999919900) AS value; -- value '2001-09-08T03:31:40'

Supported Signatures

function from_unixtime(bigint) returns timestamp

🔗 Official Documentation

`hour`

Returns the hour of the day from x. The value ranges from 0 to 23. Supported Signatures

function hour(time(p)) returns bigint
function hour(timestamp(p)) returns bigint

🔗 Official Documentation

`localtimestamp`

Returns the current timestamp as of the start of the query, with 3 digits of subsecond precision. Supported Signatures

function localtimestamp(0) returns timestamp(0)
function localtimestamp(3) returns timestamp(3)
function localtimestamp(6) returns timestamp(6)
function localtimestamp(9) returns timestamp(9)

Note: localtimestamp() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`millisecond`

Returns the millisecond of the second from x. Supported Signatures

function millisecond(time(p)) returns bigint
function millisecond(timestamp(p)) returns bigint

🔗 Official Documentation

`minute`

Returns the minute of the hour from x. Supported Signatures

function minute(time(p)) returns bigint
function minute(timestamp(p)) returns bigint

🔗 Official Documentation

`month`

Returns the month of the year from x. Examples:

examples.sql

SELECT month(cast ('2024-11-01' AS date )) AS value; -- value '11'

Supported Signatures

function month(date) returns bigint
function month(timestamp(p)) returns bigint

🔗 Official Documentation

`now`

This is an alias for current_timestamp. Examples:

examples.sql

select now() as value; -- value '2023-02-28 00:17:47.154040'

Supported Signatures

function now() returns timestamp

Note: now() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`quarter`

Returns the quarter of the year from x. The value ranges from 1 to 4. Supported Signatures

function quarter(date) returns bigint
function quarter(timestamp(p)) returns bigint

🔗 Official Documentation

`second`

Returns the second of the minute from x. Supported Signatures

function second(time(p)) returns bigint
function second(timestamp(p)) returns bigint

🔗 Official Documentation

`to_unixtime`

Returns timestamp as a UNIX timestamp. Examples:

examples.sql

SELECT to_unixtime(now()) AS value; -- value '1718974807'

Supported Signatures

function to_unixtime(timestamp(p)) returns double

🔗 Official Documentation

`year`

Returns the year from x. Examples:

examples.sql

SELECT year(cast('2024-01-01' as date)) as value; -- value '2024'

Supported Signatures

function year(date) returns bigint
function year(timestamp(p)) returns bigint

🔗 Official Documentation

SDF DB

Trino Supported Functions

Datetime Functions

`current_date`

`current_time`

`current_timestamp`

`date`

`date_diff`

`date_format`

`date_parse`

`date_trunc`

`day`

`day_of_month`

`day_of_week`

`day_of_year`

`dow`

`doy`

`from_unixtime`

`hour`

`localtimestamp`

`millisecond`

`minute`

`month`

`now`

`quarter`

`second`

`to_unixtime`

`year`

​current_date

​current_time

​current_timestamp

​date

​date_diff

​date_format

​date_parse

​date_trunc

​day

​day_of_month

​day_of_week

​day_of_year

​dow

​doy

​from_unixtime

​hour

​localtimestamp

​millisecond

​minute

​month

​now

​quarter

​second

​to_unixtime

​year

`current_date`

`current_time`

`current_timestamp`

`date`

`date_diff`

`date_format`

`date_parse`

`date_trunc`

`day`

`day_of_month`

`day_of_week`

`day_of_year`

`dow`

`doy`

`from_unixtime`

`hour`

`localtimestamp`

`millisecond`

`minute`

`month`

`now`

`quarter`

`second`

`to_unixtime`

`year`