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