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:

FunctionDescription
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.
TIMESTAMP_SECONDS(n)Constructs value of type timestamp from n seconds since the Unix epoch.
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.