Datetime Functions - SDF

`at_timezone`

Returns the timestamp specified in timestamp with the time zone converted from the session time zone to the time zone specified in zone with precision p. In the following example, the session time zone is set to America/New_York, which is three hours ahead of America/Los_Angeles.

Supported Signatures

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

🔗 Official Documentation

`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

`current_timezone`

Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08.35)

Supported Signatures

function current_timezone() returns varchar

Note: current_timezone() is stable, i.e. might return a different value for a different query execution.

🔗 Official Documentation

`date`

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

Examples:

examples.sql
SELECT date('2024-06-20') AS value; -- value '2024-06-20'

Supported Signatures

function date(timestamp(p)) returns date
function date(varchar) returns date

🔗 Official Documentation

`date_add`

Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value.

Supported Signatures

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

🔗 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 the date x truncated to unit.

Examples:

examples.sql
SELECT date_trunc('day' , TIMESTAMP '2022-10-20 05:10:00') AS value; -- value '2022-10-20T00:00:00'
SELECT date_trunc('month' , TIMESTAMP '2022-10-20 05:10:00') AS value; -- value '2022-10-01T00:00:00'
SELECT date_trunc('year', TIMESTAMP '2022-10-20 05:10:00'); -- value '2022-01-01T00:00:00'

Supported Signatures

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

🔗 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(interval day to second) 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(interval day to second) 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

`format_datetime`

Formats timestamp as a string using format.

Supported Signatures

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

🔗 Official Documentation

`from_iso8601_date`

Parses the ISO 8601 formatted date string into a date. The date can be a calendar date, a week date using ISO week numbering, or year and day of year combined.

Supported Signatures

function from_iso8601_date(varchar) returns date

🔗 Official Documentation

`from_iso8601_timestamp`

Parses the ISO 8601 formatted date string, optionally with time and time zone, into a timestamp(3) with time zone. The time defaults to 00.00.00.000, and the time zone defaults to the session time zone.

Supported Signatures

function from_iso8601_timestamp(varchar) returns timestamp

🔗 Official Documentation

`from_iso8601_timestamp_nanos`

Parses the ISO 8601 formatted date and time string. The time zone defaults to the session time zone.

Supported Signatures

function from_iso8601_timestamp_nanos(varchar) returns timestamp(9)

🔗 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
function from_unixtime(bigint, bigint, bigint) returns timestamp
function from_unixtime(bigint, varchar) returns timestamp

🔗 Official Documentation

`from_unixtime_nanos`

Returns the UNIX timestamp unixtime as a timestamp with time zone. unixtime is the number of nanoseconds since 1970-01-01 00.00.00.000000000 UTC.

Supported Signatures

function from_unixtime_nanos(bigint) returns timestamp(9)
function from_unixtime_nanos(decimal(p, s)) returns timestamp(9)

🔗 Official Documentation

`hour`

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

Supported Signatures

function hour(interval day to second) returns bigint
function hour(time(p)) returns bigint
function hour(timestamp(p)) returns bigint

🔗 Official Documentation

`human_readable_seconds`

Formats the double value of seconds into a human readable string containing weeks, days, hours, minutes, and seconds.

Supported Signatures

function human_readable_seconds(double) returns varchar

🔗 Official Documentation

`last_day_of_month`

Returns the last day of the month.

Supported Signatures

function last_day_of_month(date) returns date
function last_day_of_month(timestamp(p)) returns date

🔗 Official Documentation

`localtime`

Returns the current time as of the start of the query.

Supported Signatures

function localtime() returns time

Note: localtime() is stable, i.e. might return a different value for a different query execution.

🔗 Official Documentation

`localtimestamp`

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

Supported Signatures

function localtimestamp() returns timestamp
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(interval day to second) returns bigint
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(interval day to second) returns bigint
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(interval year to month) 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

`parse_datetime`

Parses string into a timestamp with time zone using format.

Supported Signatures

function parse_datetime(varchar, varchar) returns timestamp

🔗 Official Documentation

`parse_duration`

Parses string of format value unit into an interval, where value is fractional number of unit values.

Supported Signatures

function parse_duration(varchar) returns interval day to second

🔗 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(interval day to second) returns bigint
function second(time(p)) returns bigint
function second(timestamp(p)) returns bigint

🔗 Official Documentation

`timezone_hour`

Returns the hour of the time zone offset from timestamp.

Supported Signatures

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

🔗 Official Documentation

`timezone_minute`

Returns the minute of the time zone offset from timestamp.

Supported Signatures

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

🔗 Official Documentation

`to_iso8601`

Formats x as an ISO 8601 string. x can be date, timestamp, or timestamp with time zone.

Supported Signatures

function to_iso8601(date) returns varchar
function to_iso8601(timestamp(p)) returns varchar

🔗 Official Documentation

`to_milliseconds`

Returns the day-to-second interval as milliseconds.

Supported Signatures

function to_milliseconds(interval day to second) 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

`week`

Returns the ISO week of the year from x. The value ranges from 1 to 53.

Supported Signatures

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

🔗 Official Documentation

`week_of_year`

This is an alias for week().

Supported Signatures

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

🔗 Official Documentation

`with_timezone`

Returns the timestamp specified in timestamp with the time zone specified in zone with precision p.

Supported Signatures

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

🔗 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(interval year to month) returns bigint
function year(timestamp(p)) returns bigint

🔗 Official Documentation

`year_of_week`

Returns the year of the ISO week from x.

Supported Signatures

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

🔗 Official Documentation

`yow`

This is an alias for year_of_week().

Supported Signatures

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

🔗 Official Documentation

Conversion Functions Geospatial Functions

On this page