SQL Reference > Date & Time Functions

Date & 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.

Current Values

CURRENT_DATE

CURRENT_DATE([timezone])

Returns current date in the timezone time zone (default UTC). Return value is of date type.

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

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

CURRENT_TIME

CURRENT_TIME([timezone])

Returns current time in the timezone time zone (default UTC). Return value is of time type.

CURRENT_TIMESTAMP

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.

Conversion Functions

AT TIME ZONE

datetime AT TIME ZONE timezone
timestamp AT TIME ZONE timezone

Note that these are not functions, but SQL expressions.

The former returns the timestamp made by associating a time zone with a datetime. The latter does the opposite; returns the datetime in the given time zone at a specific timestamp.

Note that this can be easily used to convert timezones:

datetime AT TIME ZONE 'America/New_York' AT TIME ZONE 'America/Los_Angeles'

EXTRACT

EXTRACT(part FROM datetime)

Note that EXTRACT uses special syntax.

Extracts a component from a datetime (or date or time). Supported values for part are:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • DAYOFWEEK. Return the weekday. 1-based. 1=Sunday, 2=Monday, …, 7=Saturday.
  • DAYOFYEAR. Return the 1-based day in the year. 1=January 1.
  • MONTH
  • QUARTER. Return the 1-based quarter.
  • YEAR
  • DATE. Return the date part of a datetime value.
  • TIME. Return the time part of a datetime value.

FORMAT_ISO8601

FORMAT_ISO8601(datetime)

Formats a date, time, or datetime value using the ISO 8601 extended format:

  • date: YYYY-mm-dd for years between 0000 and 9999. For negative years (before 1 BCE = year 0), we use a leading - sign. For years greater than 9999, we use a leading + sign. Examples: 2018-08-22, +12345-08-22, -10-08-22.
  • time: HH:MM:SS[.cccccc]. 24 hour time, with optional fractional seconds. Example: 16:26:05.123
  • datetime: a date and time value joined by a literal T character. Example: 2018-08-22T16:26:05.123

No timezone is added to the output string for for date, time, or datetime value (but a timezone is added for timestamp, see below).

FORMAT_ISO8601(timestamp[, timezone])

Formats a timestamp using the ISO 8601 extended format. The timestamp is converted to timezone for output (default UTC). We always include a timezone offset in the output string (or a Z suffix for UTC).

Date Functions

DATE

DATE(year, month, date)

Constructs value of type date based on year, month, and date.

DATE(datetime)

Extracts the date part of datetime.

DATE(timestamp[, timezone])

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

FORMAT_DATE

FORMAT_DATE(format, date)

Returns a string containing the date value formatted using the given format. The format specification is the standard strftime with CCTZ extensions.

PARSE_DATE

PARSE_DATE(format, string)

Parses the date string (formatted using the given format) into a date value. The format specification is the standard strptime with CCTZ extensions.

PARSE_DATE_ISO8601

PARSE_DATE_ISO8601(string)

Parse a date from an ISO 8601 string without a timezone.

Time Functions

FORMAT_TIME

FORMAT_TIME(format, time)

Returns a string containing the time value formatted using the given format. The format specification is the standard strftime with CCTZ extensions.

PARSE_TIME

PARSE_TIME(format, string)

Parses the date string (formatted using the given format) into a time value. The format specification is the standard strptime with CCTZ extensions.

PARSE_TIME_ISO8601

PARSE_TIME_ISO8601(string)

Parse a time from an ISO 8601 string without a timezone.

TIME

TIME(hour, min, sec[, microsecond])

Constructs value of type time based on hour, min, sec, and, optionally, microsecond.

TIME(datetime)

Extracts the time part of datetime.

TIME(timestamp[, timezone])

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

Datetime Functions

DATETIME

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

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

DATETIME(timestamp[, timezone])

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

DATETIME(date, time)

Returns a datetime value from date (of type date) and time (of type time) components.

FORMAT_DATETIME

FORMAT_DATETIME(format, datetime)

Returns a string containing the datetime value formatted 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.

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.

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 a common (and more readable) than ISO 8601: 2018-08-22 16:26:05.123.

Timestamp Functions

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format, timestamp[, timezone])

Returns a string containing the timestamp value formatted using the given format, as of the given timezone (default UTC). The format specification is the standard strftime with CCTZ extensions.

PARSE_TIMESTAMP

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). The format specification is the standard strptime with CCTZ extensions.

PARSE_TIMESTAMP_ISO8601

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).

TIMESTAMP_MICROS

TIMESTAMP_MICROS(n)

Constructs value of type timestamp from n microseconds since the Unix epoch.

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(n)

Constructs value of type timestamp from n milliseconds since the Unix epoch.

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(n)

Constructs value of type timestamp from n seconds since the Unix epoch.

UNIX_MICROS

UNIX_MICROS(ts)

Returns the value of the timestamp ts as an int number of microseconds since the Unix epoch.

UNIX_MILLIS

UNIX_MILLIS(ts)

Returns the value of the timestamp ts as an int number of milliseconds since the Unix epoch.

UNIX_SECONDS

UNIX_SECONDS(ts)

Returns the value of the timestamp ts as an int number of seconds since the Unix epoch.

Interval Constructors

MICROSECONDS

MICROSECONDS(n)

Constructs a microsecond_interval value that refers to a duration of n microseconds.

MILLISECONDS

MILLISECONDS(n)

Constructs a microsecond_interval value that refers to a duration of n milliseconds.

SECONDS

SECONDS(n)

Constructs a microsecond_interval value that refers to a duration of n seconds.

MINUTES

MINUTES(n)

Constructs a microsecond_interval value that refers to a duration of n minutes.

HOURS

HOURS(n)

Constructs a microsecond_interval value that refers to a duration of n hours.

DAYS

DAYS(n)

Constructs a microsecond_interval value that refers to a duration of n days.

MONTHS

MONTHS(n)

Constructs a month_interval value that refers to a duration of n months.

YEARS

YEARS(n)

Constructs a month_interval value that refers to a duration of n years.

PARSE_DURATION_SECONDS

PARSE_DURATION_SECONDS(s)

Parses a microsecond_interval value from a string. Supported formats are:

  • [-]HH:MM:SS[.nnnnnn]
  • [-]MM:SS[.nnnnnn]
  • [-]SS[.nnnnnn]

PARSE_DURATION_MONTHS

PARSE_DURATION_MONTHS(s)

Parses a month_interval value from a string. Supported formats are:

  • [-]Y-M
  • [-]M

Interval Durations

DURATION_MICROS

DURATION_MICROS(interval)

Returns the duration of a microsecond_interval in microseconds.

DURATION_MILLIS

DURATION_MILLIS(interval)

Returns the duration of a microsecond_interval in milliseconds.

DURATION_SECONDS

DURATION_SECONDS(interval)

Returns the duration of a microsecond_interval in seconds.

DURATION_MONTHS

DURATION_MONTHS(interval)

Returns the duration of a month_interval in months.