Date And Time Functions

This page documents functions to construct and manipulate date and time values in Rockset. Refer to the data types page for more information about the date and time data types.

Some datetime functions take a (usually optional) time zone argument. Time zones may be either strings (e.g. 'America/Los_Angeles', 'UTC') conforming to the TZ database or intervals (e.g. INTERVAL 1 HOUR represents the time zone with a fixed offset of 1 hour from UTC). If the time zone is not specified, the default time zone is UTC.

List of functions defined in this section:

Function	Description
`CURRENT_DATE([timezone])`	Returns current date in the `timezone` time zone (default UTC). Return value is of `date` type.
`CURRENT_DATETIME([timezone])`	Returns current date and time in the `timezone` time zone (default UTC). Return value is of `datetime` type.
`CURRENT_TIME([timezone])`	Returns current time in the `timezone` time zone (default UTC). Return value is of `time` type.
`CURRENT_TIMESTAMP()`	Returns the current timestamp. As the timestamp refers to an absolute moment in time, no time zone argument is necessary (or allowed). The returned value is of the `timestamp` type.
`DATE_DIFF(precision, start_timestamp, end_timestamp)`	Computes the difference between `start_timestamp` and `end_timestamp` at the precision level provided. The difference is truncated to the nearest integer.
`DATE_FROM_UNIX_DATE(number)`	Returns a `date` from the given `number`, the number of days since January 1st, 1970.
`DATE_PARSE(string, format)`	Parses the date string (formatted using the given `format`) into a `date` value.
`DATE_TRUNC(precision, timestamp)`	Truncates the provided timestamp (assumed at timezone UTC) to the precision level provided. To use an alternate timezone use `AT TIME ZONE`.
`DAYS(n)`	Constructs a `microsecond_interval` value that refers to a duration of `n` days.
`DURATION_MICROS(interval)`	Returns the duration of a `microsecond_interval` in microseconds.
`DURATION_MILLIS(interval)`	Returns the duration of a `microsecond_interval` in milliseconds.
`PARSE_DURATION_MONTHS(s)`	Parses a `month_interval` value from a string.
`PARSE_DURATION_SECONDS(s)`	Parses a `microsecond_interval` value from a string.
`EXTRACT(part FROM date)`	Extracts a component specified by `part` from `date`.
`EXTRACT(part FROM datetime)`	Extracts a component specified by `part` from `datetime`.
`EXTRACT(part FROM time)`	Extracts a component specified by `part` from `time`.
`EXTRACT(part FROM timestamp)`	Extracts a component specified by `part` from `timestamp`.
`FORMAT_DATE(format, date)`	Converts `date` to `string` formatted using the given `format`.
`FORMAT_DATETIME(format, datetime)`	Converts `datetime` to `string` using the given `format`.
`FORMAT_TIME(format, time)`	Converts `time` to `string` using the given `format`.
`FORMAT_TIMESTAMP(format, timestamp[, timezone])`	Converts `timestamp` to `string` using the given `format`, as of the given `timezone` (default UTC).
`HOURS(n)`	Constructs a `microsecond_interval` value that refers to a duration of `n` hours.
`MICROSECONDS(n)`	Constructs a `microsecond_interval` value that refers to a duration of `n` microseconds.
`MILLISECONDS(n)`	Constructs a `microsecond_interval` value that refers to a duration of `n` milliseconds.
`MINUTES(n)`	Constructs a `microsecond_interval` value that refers to a duration of `n` minutes.
`MONTHS(n)`	Constructs a `month_interval` value that refers to a duration of `n` months.
`PARSE_DATE(format, string)`	Parses the date string (formatted using the given `format`) into a `date` value.
`PARSE_DATETIME(format, string)`	Parses the date string (formatted using the given `format`) into a `datetime` value.
`PARSE_DATETIME_ISO8601(string)`	Parse a `datetime` from an ISO 8601 string without a timezone.
`PARSE_DATE_ISO8601(string)`	Parses a `date` from an ISO 8601 string without a timezone.
`PARSE_DURATION_MONTHS(s)`	Parses a `month_interval` value from a string.
`PARSE_DURATION_SECONDS(s)`	Parses a `microsecond_interval` value from a string.
`PARSE_TIME(format, string)`	Parses the date string (formatted using the given `format`) into a `time` value.
`PARSE_TIMESTAMP(format, string[, default_timezone])`	Parses the date string (formatted using the given `format`) into a `timestamp` value. If the format string does not contain a timezone, `default_timezone` is used (UTC if not specified).
`PARSE_TIMESTAMP_ISO8601(string)`	Parses the timestamp from an ISO 8601 string. The string must include a timezone offset (or the `Z` suffix for UTC).
`PARSE_TIME_ISO8601(string)`	Parses a `time` from an ISO 8601 string without a timezone.
`CURRENT_TIME([timezone])`	Returns current time in the `timezone` time zone (default UTC). Return value is of `time` type.
`TIMESTAMP_MICROS(n)`	Constructs value of type `timestamp` from `n` microseconds since the Unix epoch.
`TIMESTAMP_MILLIS(n)`	Constructs value of type `timestamp` from `n` milliseconds since the Unix epoch.
`TIMESTAMP_SECONDS(n)`	Constructs value of type `timestamp` from `n` seconds since the Unix epoch.
`TIME_BUCKET(interval, timestamp[, origin])`	Truncates `timestamp` to the largest multiple of `interval` smaller than or equal to `timestamp`.
`UNIX_DATE(date)`	Given a `date`, returns an integer representing the number of days since January 1st, 1970.
`UNIX_MICROS(ts)`	Returns the value of the `timestamp` `ts` as an `int` number of microseconds since the Unix epoch.
`UNIX_MILLIS(ts)`	Returns the value of the `timestamp` `ts` as an `int` number of milliseconds since the Unix epoch.
`UNIX_SECONDS(ts)`	Returns the value of the `timestamp` `ts` as an `int` number of seconds since the Unix epoch.
`YEARS(n)`	Constructs a `month_interval` value that refers to a duration of `n` years.