Datetime Functions

CURRENT_DATETIME

CURRENT_DATETIME([timezone]) Returns current date and time in the timezone time zone (default UTC). Return value is of datetime type.

SELECT CURRENT_DATETIME()

2019-08-19T18:19:06.705792

SELECT CURRENT_DATETIME('America/Los_Angeles')

2019-08-19T11:19:06.705792

SELECT TYPEOF(CURRENT_DATETIME('America/Los_Angeles'))

'datetime'

SELECT CAST(CURRENT_DATETIME() AS string)

'2019-08-19T18:19:06.705792'

SELECT FORMAT_DATETIME('%Y-%h-%d %H:%M:%S', CURRENT_DATETIME())

'2019-Aug-19 18:19:06'

Here are some examples of comparison and interval arithmetic operations on the datetime type:

SELECT CURRENT_DATETIME() > PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 10:10')

true

SELECT CURRENT_DATETIME() > CURRENT_DATETIME() - INTERVAL 1 SECOND

true

SELECT
    CURRENT_DATETIME() AS today,
    (CURRENT_DATETIME() - INTERVAL 7 DAY) AS last_week

+----------------------------+------------------------------+ | today | last_week | |----------------------------+------------------------------+ | 2019-08-19T18:19:06.705792 | 2019-08-12T18:19:06.705792 | +----------------------------+------------------------------+

DATETIME constructor

DATETIME(year, month, day, hour, min, sec[, microsecond])

Constructs value of type datetime based on year, month, day, hour, min, sec, and, optionally, microsecond.

SELECT DATETIME(2019,8,19,18,19,6)

2019-08-19T18:19:06

SELECT TYPEOF(DATETIME(2019,8,19,18,19,6))

'datetime'

Here are some examples of comparison and interval arithmetic operations on the datetime type:

SELECT DATETIME(2019,8,19,18,19,6) > DATETIME(2019,8,17,18,19,6)

true

SELECT DATETIME(2019,8,19,18,19,6) > DATETIME(2019,8,19,18,19,6) - INTERVAL 1 SECOND

true

SELECT
    DATETIME(2019,8,19,18,19,6) AS today,
    (DATETIME(2019,8,19,18,19,6) - INTERVAL 7 DAY) AS last_week

+----------------------+---------------------+ | today | last_week | |----------------------+---------------------+ | 2019-08-19T18:19:06 | 2019-08-12T18:19:06 | +----------------------+---------------------+

DATETIME(timestamp[, timezone])

Returns the date and time at timestamp in timezone (default UTC).

SELECT DATETIME(TIMESTAMP '2019-08-19T18:19:06.705792Z')

2019-08-19T18:19:06.705792

SELECT DATETIME(TIMESTAMP '2019-08-19T18:19:06.705792Z', 'America/Los_Angeles')

2019-08-19T11:19:06.705792

SELECT TYPEOF(DATETIME(TIMESTAMP '2019-08-19T18:19:06.705792Z'))

'datetime'

Here are some examples of comparison and interval arithmetic operations on the datetime type:

SELECT DATETIME(TIMESTAMP '2019-08-19T18:19:06Z') > DATETIME '2019-8-17 18:19:6'

true

SELECT DATETIME(TIMESTAMP '2019-08-19T18:19:06Z') > DATETIME(TIMESTAMP '2019-08-19T18:19:06Z') - INTERVAL 1 SECOND

true

SELECT
    DATETIME(TIMESTAMP '2019-08-19T18:19:06Z') AS today,
    (DATETIME(TIMESTAMP '2019-08-19T18:19:06Z') - INTERVAL 7 DAY) AS last_week

CURRENT_DATETIME([timezone]) Returns current date and time in the timezone time zone (default UTC). Return value is of datetime type.

SELECT DATETIME(DATE(2019,8,19), TIME(18,19,6))

2019-08-19T18:19:06

SELECT TYPEOF(DATETIME(DATE(2019,8,19), TIME(18,19,6)))

'datetime'

Here are some examples of comparison and interval arithmetic operations on the datetime type:

SELECT DATETIME(DATE(2019,8,19), TIME(18,19,6)) > DATETIME(DATE(2019,8,17), TIME(18,19,6))

true

SELECT DATETIME(DATE(2019,8,19), TIME(18,19,6)) > DATETIME(DATE(2019,8,19), TIME(18,19,6)) - INTERVAL 1 SECOND

true

SELECT
    DATETIME(DATE(2019,8,19), TIME(18,19,6)) AS today,
    (DATETIME(DATE(2019,8,19), TIME(18,19,6)) - INTERVAL 7 DAY) AS last_week

FORMAT_DATETIME

FORMAT_DATETIME(format, datetime) Converts datetime to string using the given format.

The format specification is the standard strftime with CCTZ extensions.

Note that FORMAT_DATETIME also works for arguments of date, time, and timestamp types, and thus can be used as a generic "format any datetime-like type" function.

SELECT FORMAT_DATETIME('%Y/%m/%d %H:%M:%S', DATETIME '2019-08-19 18:19:06')

'2019/08/19 18:19:06'

SELECT FORMAT_DATETIME('%Y/%m/%d %H:%M:%S', TIMESTAMP '2019-08-19T18:19:06Z')

'2019/08/19 18:19:06'

SELECT FORMAT_DATETIME('%Y/%m/%d %H:%M:%S', DATE '2019-08-19')

'2019/08/19 00:00:00'

SELECT FORMAT_DATETIME('%Y/%m/%d %H:%M:%S', TIME '18:19:06')

'1970/01/01 18:19:06'

FORMAT_ISO8601 datetime

FORMAT_ISO8601(datetime)

Converts a datetime value to string using the ISO 8601 extended format. Returns date and time values joined by a literal T character.

SELECT FORMAT_ISO8601(DATETIME '2019-10-10 11:12:30')

'2019-10-10T11:12:30'

No timezone is added to the output string.

PARSE_DATETIME

PARSE_DATETIME(format, string) Parses the date string (formatted using the given format) into a datetime value.

The format specification is the standard strptime with CCTZ extensions.

SELECT CURRENT_DATETIME()

2019-08-19T18:19:06.705792

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%E*S', '2019-08-19 18:19:06.705792')

2019-08-19T18:19:06.705792

SELECT TYPEOF(PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 18:19'))

'datetime'

Here are some examples of comparison and interval arithmetic operations on the datetime type:

SELECT CURRENT_DATETIME() > PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 10:10')

true

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 10:10') > PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 10:10') - INTERVAL 1 SECOND

true

SELECT
    PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 18:19') AS today,
    (PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 18:19') - INTERVAL 7 DAY) AS last_week

+----------------------+----------------------+ | today | last_week | |----------------------+----------------------+ | 2019-08-19T18:19:00 | 2019-08-12T18:19:00 | +----------------------+----------------------+

PARSE_DATETIME_ISO8601

PARSE_DATETIME_ISO8601(string) Parse a datetime from an ISO 8601 string without a timezone.

The accepted format is more lenient than ISO 8601; Rockset also accepts dates and times separated by a space rather than T, which is common (and more readable) than ISO 8601: 2018-08-22 16:26:05.123.

SELECT CURRENT_DATETIME()

2019-08-19T18:19:06.705792

SELECT PARSE_DATETIME_ISO8601('2019-08-19 18:19:06.705792')

2019-08-19T18:19:06.705792

SELECT TYPEOF(PARSE_DATETIME('%Y-%m-%d %H:%M', '2019-08-19 18:19'))

'datetime'

Here are some examples of comparison and interval arithmetic operations on the datetime type:

SELECT CURRENT_DATETIME() > PARSE_DATETIME_ISO8601('2019-08-19 10:10:00')

true

SELECT PARSE_DATETIME_ISO8601('2019-08-19 10:10:00') > PARSE_DATETIME_ISO8601('2019-08-19 10:10:00') - INTERVAL 1 SECOND

true

SELECT
    PARSE_DATETIME_ISO8601('2019-08-19 18:19:00') AS today,
    (PARSE_DATETIME_ISO8601('2019-08-19 18:19:00') - INTERVAL 7 DAY) AS last_week

+--------------------+--------------------+ | today | last_week | |--------------------+--------------------+ | 2019-08-19T18:19 | 2019-08-12T18:19 | +--------------------+--------------------+

AT TIME ZONE (datetime)

This is a SQL expression, and not a SQL function.

datetime AT TIME ZONE timezone

This returns the timestamp made by associating a time zone with a datetime.

This can be easily used to convert timezones:

SELECT CURRENT_DATETIME() AT TIME ZONE 'America/Los_Angeles'

2019-08-19T11:19:06.705792

SELECT TYPEOF(CURRENT_DATETIME() AT TIME ZONE 'America/Los_Angeles')

'timestamp'

Timezones can also be chained to obtain a timestamp in a different timezone as follows:

SELECT
    CURRENT_DATETIME() AT TIME ZONE 'America/Los_Angeles'
    AT TIME ZONE 'America/New_York'

'2019-08-19T14:19:06.705792'

EXTRACT datetime

EXTRACT(part FROM datetime)

Extracts a component specified by part from datetime.

Here are supported values for part for datetime along with example queries.

Type	Part	Query	Result
DATETIME	MICROSECOND	`SELECT EXTRACT(MICROSECOND FROM DATETIME '2018-05-26 04:30:20.345')`	`345000`
DATETIME	MILLISECOND	`SELECT EXTRACT(MILLISECOND FROM DATETIME '2018-05-26 04:30:20.345')`	`345`
DATETIME	SECOND	`SELECT EXTRACT(SECOND FROM DATETIME '2018-05-26 04:30:20.345')`	`20`
DATETIME	MINUTE	`SELECT EXTRACT(MINUTE FROM DATETIME '2018-05-26 04:30:20.345')`	`30`
DATETIME	HOUR	`SELECT EXTRACT(HOUR FROM DATETIME '2018-05-26 04:30:20.345')`	`4`
DATETIME	DAY	`SELECT EXTRACT(DAY FROM DATETIME '2018-05-26 04:30:20.345')`	`26`
DATETIME	DAYOFWEEK	`SELECT EXTRACT(DAYOFWEEK FROM DATETIME '2018-05-26 04:30:20.345')`	`7`
DATETIME	DOW	`SELECT EXTRACT(DOW FROM DATETIME '2018-05-26 04:30:20.345')`	`7`
DATETIME	DAYOFYEAR	`SELECT EXTRACT(DAYOFYEAR FROM DATETIME '2018-05-26 04:30:20.345')`	`146`
DATETIME	DOY	`SELECT EXTRACT(DOY FROM DATETIME '2018-05-26 04:30:20.345')`	`146`
DATETIME	MONTH	`SELECT EXTRACT(MONTH FROM DATETIME '2018-05-26 04:30:20.345')`	`5`
DATETIME	QUARTER	`SELECT EXTRACT(QUARTER FROM DATETIME '2018-05-26 04:30:20.345')`	`2`
DATETIME	YEAR	`SELECT EXTRACT(YEAR FROM DATETIME '2018-05-26 04:30:20.345')`	`2018`
DATETIME	DATE	`SELECT EXTRACT(DATE FROM DATETIME '2018-05-26 04:30:20.345')`	`2018-05-26`
DATETIME	TIME	`SELECT EXTRACT(TIME FROM DATETIME '2018-05-26 04:30:20.345')`	`04:30:20.345`
DATETIME	EPOCH	`SELECT EXTRACT(EPOCH FROM DATETIME '2018-05-26 04:30:20.345')`	`1527309020`
DATETIME	ISOWEEK	`SELECT EXTRACT(ISOWEEK FROM DATETIME '2018-05-26 04:30:20.345')`	`21`
DATETIME	ISOYEAR	`SELECT EXTRACT(ISOYEAR FROM DATETIME '2018-05-26 04:30:20.345')`	`2018`

DATE_TRUNC (datetime)

DATE_TRUNC(precision, datetime)

Truncates the provided datetime to the precision level provided. The precision may be specified as a string (see examples below), a microsecond interval (INTERVAL x MICROSECOND, INTERVAL x DAY, etc), or a moth interval (INTERVAL x MONTH, INTERVAL x YEAR).

Here are supported values for precision (case-insensitive) along with example queries:

Type	Precision	Query	Result
DATETIME	MILLENNIUM	`SELECT DATE_TRUNC('MILLENNIUM', DATETIME '1999-11-20T17:13:56.123456')`	`1000-01-01T00:00:00`
DATETIME	CENTURY	`SELECT DATE_TRUNC('CENTURY', DATETIME '1999-11-20T17:13:56.123456')`	`1900-01-01T00:00:00`
DATETIME	DECADE	`SELECT DATE_TRUNC('DECADE', DATETIME '1999-11-20T17:13:56.123456')`	`1990-01-01T00:00:00`
DATETIME	YEAR	`SELECT DATE_TRUNC('YEAR', DATETIME '1999-11-20T17:13:56.123456')`	`1999-01-01T00:00:00`
DATETIME	QUARTER	`SELECT DATE_TRUNC('QUARTER', DATETIME '1999-11-20T17:13:56.123456')`	`1999-10-01T00:00:00`
DATETIME	MONTH	`SELECT DATE_TRUNC('MONTH', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-01T00:00:00`
DATETIME	WEEK	`SELECT DATE_TRUNC('WEEK', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-14T00:00:00`
DATETIME	DAY	`SELECT DATE_TRUNC('DAY', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-20T00:00:00`
DATETIME	HOUR	`SELECT DATE_TRUNC('HOUR', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-20T17:00:00`
DATETIME	MINUTE	`SELECT DATE_TRUNC('MINUTE', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-20T17:13:00`
DATETIME	SECOND	`SELECT DATE_TRUNC('SECOND', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-20T17:13:56`
DATETIME	MILLISECONDS	`SELECT DATE_TRUNC('MILLISECONDS', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-20T17:13:56.123`
DATETIME	MICROSECONDS	`SELECT DATE_TRUNC('MICROSECONDS', DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-20T17:13:56.123456`
DATETIME	microsecond_interval	`SELECT DATE_TRUNC(INTERVAL 1 SECOND, DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-20T17:13:56`
DATETIME	month_interval	`SELECT DATE_TRUNC(INTERVAL 1 MONTH, DATETIME '1999-11-20T17:13:56.123456')`	`1999-11-01T00:00:00`

DATE_DIFF (datetime)

DATE_DIFF(precision, start_datetime, end_datetime)

Computes the difference between start_datetime and end_datetime at the precision level provided. The difference is truncated to the nearest integer.

Here are supported values for precision (case-insensitive) along with example queries:

Note: A MONTH is equal to 30 DAYs. A QUARTER is equal to 90 DAYs. A YEAR is equal to 365 DAYs.

Type	Precision	Query	Result
DATETIME	MILLENNIUM	`SELECT DATE_DIFF('MILLENNIUM', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')`	`1`
DATETIME	CENTURY	`SELECT DATE_DIFF('CENTURY', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')`	`10`
DATETIME	DECADE	`SELECT DATE_DIFF('DECADE', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')`	`100`
DATETIME	YEAR	`SELECT DATE_DIFF('YEAR', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')`	`1000`
DATETIME	QUARTER	`SELECT DATE_DIFF('QUARTER', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2000-11-20T17:13:56.123456')`	`4`
DATETIME	MONTH	`SELECT DATE_DIFF('MONTH', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2000-11-20T17:13:56.123456')`	`12`
DATETIME	WEEK	`SELECT DATE_DIFF('WEEK', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-12-20T17:13:56.123456')`	`4`
DATETIME	DAY	`SELECT DATE_DIFF('DAY', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-28T17:13:56.123456')`	`8`
DATETIME	HOUR	`SELECT DATE_DIFF('HOUR', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-19T17:13:56.123456')`	`-24`
DATETIME	MINUTE	`SELECT DATE_DIFF('MINUTE', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:23:56.123456')`	`10`
DATETIME	SECOND	`SELECT DATE_DIFF('SECOND', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:12:56.123456')`	`-60`
DATETIME	MILLISECONDS	`SELECT DATE_DIFF('MILLISECONDS', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:13:57.123456')`	`1000`
DATETIME	MICROSECONDS	`SELECT DATE_DIFF('MICROSECONDS', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:13:56.123457')`	`1`