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.

Date and Time Types

When data ingested into or returned from Rockset in JSON form, these types can be specified in a special format shown below.

{
  "_id": "foo"
  "_event_time": {"__rockset_type": "timestamp", "value": "42"}
}

Note that field _event_time is parsed not as object but rather timestamp with value 42.

DATE

date

A date value represents a logical calendar date (year, month, day) independent of time zone. A date does not represent a specific time period; it can differ based on timezones. To represent an absolute point in time, use a timestamp instead.

A date literal in SQL syntax is formatted as follows.

DATE 'YYYY-[M]M-[D]D'

This consists of:

• YYYY: Four-digit year
• [M]M: One or two digit month
• [D]D: One or two digit day

DATE '2018-01-01' -- example literal

DATE(2018, 1, 1) -- constructor function

DATETIME

datetime

A datetime value represents a point in time (year, month, day, hour, minute, second, microsecond). It does not refer to an absolute instance in time, unlike timestamp. Instead, it is the civil time; the time that a user would see on a watch or calendar.

A date literal in SQL syntax is formatted as follows.

DATETIME 'YYYY-[M]M-[D]D[( )[H]H:[M]M:[S]S[.DDDDDD]]'

This consists of:

• YYYY: Four-digit year
• [M]M: One or two digit month
• [D]D: One or two digit day
• ( ): A space separator
• [H]H: One or two digit hour (valid values from 00 to 23)
• [M]M: One or two digit minutes (valid values from 00 to 59)
• [S]S: One or two digit seconds (valid values from 00 to 59)
• [.DDDDDD]: Up to six fractional digits

DATETIME '2018-01-01 9:30:45.456' -- example literal

DATETIME(2018, 1, 1, 9, 30, 45, 456) -- constructor function

TIME

time

A time value represents the time of the day (hour, minute, second, millisecond) independent of a specific date.

A time literal in SQL syntax is formatted as follows.

TIME '[H]H:[M]M:[S]S[.DDDDDD]'

This consists of:

• [H]H: One or two digit hour (valid values from 00 to 23)
• [M]M: One or two digit minutes (valid values from 00 to 59)
• [S]S: One or two digit seconds (valid values from 00 to 59)
• [.DDDDDD]: Up to six fractional digits

TIME '09:30:45.456' -- example literal

TIME(9, 30, 45, 456) -- constructor function

TIMESTAMP

timestamp

A timestamp value represents absolute date and time values independent of any time zone.

A timestamp literal in SQL syntax is formatted as follows.

TIMESTAMP 'YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone]'

This consists of:

• YYYY: Four-digit year
• [M]M: One or two digit month
• [D]D: One or two digit day
• ( ): A space separator
• [H]H: One or two digit hour (valid values from 00 to 23)
• [M]M: One or two digit minutes (valid values from 00 to 59)
• [S]S: One or two digit seconds (valid values from 00 to 59)
• [.DDDDDD]: Up to six fractional digits
• [time zone]: Offset from Coordinated Universal Time (UTC). When a time zone is not explicitly specified, the default time zone, UTC, is used. The offset is formatted as (+|-)H[H][:M[M]], or simply Z to refer to UTC. When using this format, no space is allowed between the time zone and the rest of the timestamp.

TIMESTAMP '2018-01-01 09:30:45.456-05:00' -- example literal

MONTH INTERVAL

month_interval

A month interval refers to a specific number of months.

As months have different lengths, month intervals may only be added to or subtracted from date or datetime values.

Examples of month intervals are shown below.

INTERVAL 3 MONTH;

INTERVAL 2 YEAR;

INTERVAL '2-3' YEAR TO MONTH;

MICROSECOND INTERVAL

microsecond_interval

A microsecond interval refers to a fixed amount of time with microsecond precision.

Microsecond intervals may be added to or subtracted from dates, times, datetimes, and timestamps. Also, you get a microsecond interval when you subtract two dates, times, datetimes, or timestamps (indicating the length of time between the two time points).

Examples of microsecond intervals are shown below.

INTERVAL 2 DAY;

INTERVAL 3 HOUR;

INTERVAL 5 MINUTE;

INTERVAL 10 SECOND;

INTERVAL '2 10:23:45.56' DAY TO SECOND;

Date Functions

CURRENT_DATE([timezone])

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

SELECT

    CURRENT_DATE()

2019-08-19

SELECT

    CURRENT_DATE('America/Los_Angeles')

2019-08-19

SELECT

    TYPEOF(CURRENT_DATE())

'date'

SELECT

    CAST(CURRENT_DATE() AS string)

'2019-08-19'

SELECT

    FORMAT_DATE('%Y-%h-%d', CURRENT_DATE())

'2019-Aug-19'

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

SELECT

    CURRENT_DATE() > PARSE_DATE('%Y-%m-%d', '2019-01-01')

true

SELECT

    CURRENT_DATE() > CURRENT_DATE() - INTERVAL 1 DAY

true

SELECT

    CURRENT_DATE() AS today,

    (CURRENT_DATE() - INTERVAL 1 WEEK) AS last_week

+-------------+--------------+
| today       | last_week    |
|-------------+--------------+
| 2019-08-19  | 2019-08-12T  |
+-------------+--------------+

DATE Constructor

DATE(year, month, day)

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

SELECT

    TYPEOF(DATE(2019, 8, 17))

'date'

SELECT

    DATE(2019, 8, 17)

2019-08-17

SELECT

    CAST(DATE(2019, 8, 17) AS string)

'2019-08-17'

SELECT

    FORMAT_DATE('%Y-%h-%d', DATE(2019, 8, 17))

'2019-Aug-17'

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

SELECT

    CURRENT_DATE() > DATE(2019, 1, 1)

true

SELECT

    CURRENT_DATE() > DATE(2019, 8, 17) + INTERVAL 1 DAY

true

SELECT

    DATE(2019, 8, 19) AS today,

    (DATE(2019, 8, 19) - INTERVAL 7 DAY) AS last_week

+---------------+--------------+
| today         | last_week    |
|---------------+--------------+
| 2019-08-19    |  2019-08-12T |
+---------------+--------------+

DATE(datetime)

Extracts the date part of datetime.

SELECT

    TYPEOF(DATE(CURRENT_DATETIME()))

'date'

SELECT

    DATE(CURRENT_DATETIME())

2019-08-19

SELECT

    CAST(DATE(CURRENT_DATETIME()) AS string)

'2019-08-19'

SELECT

    FORMAT_DATE('%Y-%h-%d', DATE(CURRENT_DATETIME()))

'2019-Aug-19'

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

SELECT

    DATE(CURRENT_DATETIME()) > DATE(2019, 1, 1)

true

SELECT

    DATE(CURRENT_DATETIME()) > DATE(2019, 8, 19) - INTERVAL 1 DAY

true

SELECT

    DATE(2019, 8, 19) AS today,

    DATE(2019, 8, 19) - INTERVAL 7 DAY AS last_week

+---------------+--------------+
| today         | last_week    |
|---------------+--------------+
| 2019-08-19    | 2019-08-12T  |
+---------------+--------------+

DATE(timestamp[, timezone]

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

SELECT

    TYPEOF(DATE(CURRENT_TIMESTAMP()))

'date'

SELECT

    DATE(CURRENT_TIMESTAMP())

2019-08-19

SELECT

    DATE(CURRENT_TIMESTAMP(), 'Australia/Sydney')

2019-08-20

SELECT

    CAST(DATE(CURRENT_TIMESTAMP()) AS string)

'2019-08-19'

SELECT

    FORMAT_DATE('%Y-%h-%d', DATE(CURRENT_TIMESTAMP()))

'2019-Aug-19'

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

SELECT

    DATE(CURRENT_TIMESTAMP()) > DATE(2019, 1, 1)

true

SELECT

    DATE(CURRENT_TIMESTAMP()) > DATE(2019, 8, 19) - INTERVAL 1 DAY

true

SELECT

    DATE(2019, 8, 19) AS today,

    (DATE(CURRENT_TIMESTAMP()) - INTERVAL 7 DAY) AS last_week

+---------------+--------------+
| today         | last_week    |
|---------------+--------------+
| 2019-08-19    | 2019-08-12T  |
+---------------+--------------+

DATE_PARSE

DATE_PARSE(string, format)

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

The format specification is the standard strptime with CCTZ extensions. This function behaves exactly like PARSE_DATE but accepts parameters in different order. It exists for compatibility with DATE_PARSE function in Presto.

FORMAT_DATE

FORMAT_DATE(format, date)

Converts date to string formatted using the given format.

The format specification is the standard strftime with CCTZ extensions.

SELECT

    FORMAT_DATE('%Y/%m/%d', DATE '2018-05-26')

'2018/05/26'

SELECT

    FORMAT_DATE('%Y-%h-%d', DATE '2019-8-19')

'2019-Aug-19'

FORMAT_ISO8601 date

FORMAT_ISO8601(date)

Converts a date value to string using the ISO 8601 extended format. Returns string formatted using YYYY-mm-dd as the format specifier 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.

SELECT

    FORMAT_ISO8601(DATE '-0427-00-00')

'-428-11-30'

SELECT

    FORMAT_ISO8601(DATE '2019-01-01')

'2019-01-01'

SELECT

    FORMAT_ISO8601(DATE '10000-01-01')

'+10000-01-01'

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.

SELECT

    PARSE_DATE('%Y-%m-%d', '2019-8-19')

2019-08-19

SELECT

    TYPEOF(PARSE_DATE('%Y-%m-%d', '2019-8-19'))

'date'

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

SELECT

    DATE(2019, 8, 19) > PARSE_DATE('%Y/%m/%d', '2019/8/17')

true

SELECT

    DATE(2019, 8, 19) > PARSE_DATE('%Y/%m/%d', '2019/8/19') - INTERVAL 1 DAY

true

SELECT

    PARSE_DATE('%Y/%m/%d', '2019/8/19') AS today,

    (

        PARSE_DATE('%Y/%m/%d', '2019/8/19') - INTERVAL 7 DAY

    ) AS last_week

+---------------+--------------+
| today         | last_week    |
|---------------+--------------+
| 2019-08-19    | 2019-08-12T  |
+---------------+--------------+

PARSE_DATE_ISO8601

PARSE_DATE_ISO8601(string)

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

SELECT

    PARSE_DATE_ISO8601('2019-8-17')

2019-08-17

SELECT

    TYPEOF(PARSE_DATE_ISO8601('2019-8-19'))

'date'

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

SELECT

    DATE(2019, 8, 19) > PARSE_DATE_ISO8601('2019-8-17')

true

SELECT

    DATE '2019-08-19' > PARSE_DATE_ISO8601('2019-08-19') - INTERVAL 1 DAY

true

SELECT

    PARSE_DATE_ISO8601('2019-08-19') AS today,

    (PARSE_DATE_ISO8601('2019-8-19') - INTERVAL 7 DAY) AS last_week

+---------------+--------------+
| today         | last_week    |
|---------------+--------------+
| 2019-08-19    | 2019-08-12T  |
+---------------+--------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(number)

Returns a date from the given number, the number of days since January 1st, 1970.

DATE_FROM_UNIX_DATE(unix_date)

Returns a Date from the given unix_date, the number of days since January 1st, 1970.

SELECT

    DATE_FROM_UNIX_DATE(18125)

2019-08-17

SELECT

    TYPEOF(DATE_FROM_UNIX_DATE(18127))

'date'

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

SELECT

    DATE(2019, 8, 19) > DATE_FROM_UNIX_DATE(18125)

true

SELECT

    DATE '2019-08-19' > DATE_FROM_UNIX_DATE(18127) - INTERVAL 1 DAY

true

SELECT

    DATE_FROM_UNIX_DATE(18127) AS today,

    (DATE_FROM_UNIX_DATE(18127) - INTERVAL 7 DAY) AS last_week

+---------------+--------------+
| today         | last_week    |
|---------------+--------------+
| 2019-08-19    | 2019-08-12T  |
+---------------+--------------+

UNIX_DATE

UNIX_DATE(date)

Given a date, returns an integer representing the number of days since January 1st, 1970.

UNIX_DATE(date)

Given a Date, returns an integer representing the number of days since January 1st, 1970.

SELECT

    UNIX_DATE(DATE(2019, 8, 19))

18127

SELECT

    TYPEOF(UNIX_DATE(DATE(2019, 8, 19)))

'int'

EXTRACT date

EXTRACT(part FROM date)

Extracts a component specified by part from date (assumed at timezone UTC). To use an alternate timezone use AT TIME ZONE.

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

DATE_TRUNC (date)

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.

DATE_TRUNC(precision, date)

Truncates the provided date 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.

DATE_DIFF (date)

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.

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.

Time Functions

CURRENT_TIME

CURRENT_TIME([timezone])

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

SELECT

    CURRENT_TIME()

18:19:06.705792

SELECT

    CURRENT_TIME('America/Los_Angeles')

11:19:06.705792

SELECT

    TYPEOF(CURRENT_TIME('America/Los_Angeles'))

'time'

SELECT

    CAST(CURRENT_TIME('America/Los_Angeles') AS string)

'11:19:06.705792'

SELECT

    FORMAT_TIME('%H:%M', CURRENT_TIME())

'18:19'

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

SELECT

    CURRENT_TIME() > PARSE_TIME('%H:%M', '10:10')

true

SELECT

    CURRENT_TIME() > CURRENT_TIME() - INTERVAL 1 SECOND

true

SELECT

    CURRENT_TIME() AS now,

    (CURRENT_TIME() - INTERVAL 1 HOUR) AS one_hour_ago

+---------------------+-------------------+
| now                 | one_hour_ago      |
|---------------------+-------------------+
| 18:19:06.705792     | 17:19:06.705792   |
+---------------------+-------------------+

TIME constructor

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

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

SELECT

    TIME(10, 30, 10)

10:30:10

SELECT

    TYPEOF(TIME(10, 30, 10))

'time'

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

SELECT

    TIME(10, 30, 10) > PARSE_TIME('%H.%M.%S', '10.30.10')

false

SELECT

    TIME(10, 30, 10) > PARSE_TIME('%H.%M.%S', '10.30.10') - INTERVAL 1 SECOND

true

SELECT

    TIME(10, 31, 10) AS now,

    (TIME(10, 31, 10) - INTERVAL 1 MINUTE) AS one_minute_ago

+---------------+----------------+
| now           | one_minute_ago |
|---------------+----------------+
| 10:31:10      | 10:30:10       |
+---------------+----------------+

TIME(datetime)

Returns the time part of datetime.

SELECT

    CURRENT_DATETIME()

2019-08-19T18:19:06.705792

SELECT

    TIME(CURRENT_DATETIME())

18:19:06.705792

SELECT

    TYPEOF(TIME(CURRENT_DATETIME()))

'time'

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

SELECT

    TIME(CURRENT_DATETIME()) > TIME(11, 10, 10)

true

SELECT

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

true

SELECT

    TIME(CURRENT_DATETIME()) AS now,

    (TIME(CURRENT_DATETIME()) - INTERVAL 1 MINUTE) AS one_minute_ago

+------------------+-----------------+
| now              | one_minute_ago  |
|------------------+-----------------+
| 18:19:06.705792  | 18:18:06.705792 |
+------------------+-----------------+

TIME(timestamp[, timezone]

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

SELECT

    CURRENT_TIMESTAMP()

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

SELECT

    TIME(CURRENT_TIMESTAMP())

18:19:06.705792

SELECT

    TYPEOF(TIME(CURRENT_TIMESTAMP()))

'time'

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

SELECT

    TIME(CURRENT_TIMESTAMP()) > TIME(11, 10, 10)

true

SELECT

    TIME(CURRENT_TIMESTAMP()) > TIME(CURRENT_TIMESTAMP()) - INTERVAL 1 SECOND

true

SELECT

    TIME(CURRENT_TIMESTAMP()) AS now,

    (TIME(CURRENT_TIMESTAMP()) - INTERVAL 1 MINUTE) AS one_minute_ago

+------------------+-----------------+
| now              | one_minute_ago  |
|------------------+-----------------+
| 18:19:06.705792  | 18:18:06.705792 |
+------------------+-----------------+

FORMAT_TIME

FORMAT_TIME(format, time)

Converts time to string using the given format.

The format specification is the standard strftime with CCTZ extensions.

SELECT

    FORMAT_TIME('%H:%M:%E*S', TIME '21:30:20.345')

'21:30:20.345'

SELECT

    FORMAT_TIME('%I:%M:%S', TIME '21:30:20.345')

'09:30:20'

FORMAT_ISO8601 time

FORMAT_ISO8601(time)

Converts a time value to string using the ISO 8601 extended format. Returns string formatted using HH:MM:SS[.cccccc] as the format specifier for time. It formats it as a 24 hour time, with optional fractional seconds.

SELECT

    FORMAT_ISO8601(TIME '11:11:10.1002')

'11:11:10.100200'

SELECT

    FORMAT_ISO8601(TIME '21:11:10')

'21:11:10'

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.

SELECT

    PARSE_TIME('%H:%M:%S', '10:30:10')

10:30:10

SELECT

    TYPEOF(PARSE_TIME('%H:%M:%S', '10:30:10'))

'time'

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

SELECT

    TIME '10:31:00' > PARSE_TIME('%H.%M.%S', '10.30.10')

true

SELECT

    TIME(10, 30, 10) > PARSE_TIME('%H.%M.%S', '10.30.10') - INTERVAL 1 SECOND

true

SELECT

    PARSE_TIME('%H:%M:%S', '10:31:10') AS now,

    (

        PARSE_TIME('%H:%M:%S', '10:31:10') - INTERVAL 1 MINUTE

    ) AS one_minute_ago

+---------------+----------------+
| now           | one_minute_ago |
|---------------+----------------+
| 10:31:10      | 10:30:10       |
+---------------+----------------+

PARSE_TIME_ISO8601

PARSE_TIME_ISO8601(string)

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

SELECT

    PARSE_TIME_ISO8601('10:30:10')

10:30:10

SELECT

    TYPEOF(PARSE_TIME_ISO8601('10:30:10'))

'time'

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

SELECT

    TIME(10, 31, 10) > PARSE_TIME_ISO8601('10:30:10')

true

SELECT

    TIME '10:30:10' > PARSE_TIME_ISO8601('10:30:10') - INTERVAL 1 SECOND

true

SELECT

    PARSE_TIME_ISO8601('10:31:10') AS now,

    (

        PARSE_TIME_ISO8601('10:31:10') - INTERVAL 1 MINUTE

    ) AS one_minute_ago

+---------------+----------------+
| now           | one_minute_ago |
|---------------+----------------+
| 10:31:10      | 10:30:10       |
+---------------+----------------+

EXTRACT time

EXTRACT(part FROM time)

Extracts a component specified by part from time.

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

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

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

DATETIME(date, time)

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

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

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

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.

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.

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.

Timestamp Functions

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.

SELECT

    CURRENT_TIMESTAMP()

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

SELECT

    TYPEOF(CURRENT_TIMESTAMP())

'timestamp'

SELECT

    CAST(CURRENT_TIMESTAMP() AS string)

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

SELECT

    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP())

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

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

SELECT

    CURRENT_TIMESTAMP() > PARSE_TIMESTAMP('%Y/%m/%d %H:%M', '2019/08/17 10:10')

true

SELECT

    CURRENT_TIMESTAMP() > CURRENT_TIMESTAMP() - INTERVAL 1 SECOND

true

SELECT

    CURRENT_TIMESTAMP() AS now,

    CURRENT_TIMESTAMP() - INTERVAL 1 HOUR AS one_hour_ago

+--------------------------------+-------------------------------+
| now                            | one_hour_ago                  |
|--------------------------------+-------------------------------+
| '2019-08-19T18:19:06.705792Z'  | '2019-08-19T17:19:06.705792Z' |
+--------------------------------+-------------------------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format, timestamp[, timezone]

Converts timestamp to string using the given format, as of the given timezone (default UTC).

The format specification is the standard strftime with CCTZ extensions.

Note that FORMAT_TIMESTAMP 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_TIMESTAMP(

        '%Y/%m/%d %H:%M:%S',

        TIMESTAMP '2019-08-19T18:19:06Z'

    )

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

SELECT

    FORMAT_TIMESTAMP(

        '%Y/%m/%d %H:%M:%S',

        DATETIME '2019-08-19 18:19:06'

    )

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

SELECT

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

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

SELECT

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

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

FORMAT_ISO8601 timestamp

FORMAT_ISO8601(timestamp[, timezone])

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

SELECT

    FORMAT_ISO8601(CURRENT_TIMESTAMP(), 'America/Los_Angeles')

'2019-08-19T11:19:06.705792-7:00'

SELECT

    FORMAT_ISO8601(CURRENT_TIMESTAMP())

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

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.

SELECT

    CURRENT_TIMESTAMP()

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

SELECT

    PARSE_TIMESTAMP(

        '%Y-%m-%d %H:%M:%E*S',

        '2019-08-19 18:19:06.705792'

    )

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

SELECT

    PARSE_TIMESTAMP(

        '%Y-%m-%d %H:%M:%E*S %Ez',

        '2019-08-19 11:19:06.705792 -07:00'

    )

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

SELECT

    TYPEOF(

        PARSE_TIMESTAMP(

            '%Y-%m-%d %H:%M:%E*S',

            '2019-08-19 18:19:06.705792'

        )

    )

'timestamp'

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

SELECT

    CURRENT_TIMESTAMP() > PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-17 18:19')

true

SELECT

    PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') > PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') - INTERVAL 1 SECOND

true

SELECT

    PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') AS today,

    (

        PARSE_TIMESTAMP('%Y-%m-%d %H:%M', '2019-08-19 18:19') - INTERVAL 7 DAY

    ) AS last_week

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

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

SELECT

    CURRENT_TIMESTAMP()

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

SELECT

    PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z')

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

SELECT

    PARSE_TIMESTAMP_ISO8601('2019-08-19T11:19:06.705792-7')

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

SELECT

    TYPEOF(

        PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z')

    )

'timestamp'

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

SELECT

    CURRENT_TIMESTAMP() > PARSE_TIMESTAMP_ISO8601('2019-08-17T18:19:06Z')

true

SELECT

    PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z') > PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:06.705792Z') - INTERVAL 1 SECOND

true

SELECT

    PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:00Z') AS today,

    (

        PARSE_TIMESTAMP_ISO8601('2019-08-19T18:19:00Z') - INTERVAL 7 DAY

    ) AS last_week

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

TIME_BUCKET

TIME_BUCKET(interval, timestamp[, origin])

Truncates timestamp to the largest multiple of interval smaller than or equal to timestamp.

SELECT

    TIME_BUCKET(

        SECONDS(30),

        TIMESTAMP '2018-05-26T10:30:20.345Z'

    )

'2018-05-26T10:30:00.000000Z'

SELECT

    TIME_BUCKET(

        MINUTES(20),

        TIMESTAMP '2018-05-26T10:30:20.345Z'

    )

'2018-05-26T10:20:00.000000Z'

SELECT

    TIME_BUCKET(HOURS(1), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-26T10:00:00.000000Z'

SELECT

    TIME_BUCKET(DAYS(1), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-26T00:00:00.000000Z'

SELECT

    TIME_BUCKET(MONTHS(2), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-05-01T00:00:00.000000Z'

SELECT

    TIME_BUCKET(MONTHS(3), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-04-01T00:00:00.000000Z'

SELECT

    TIME_BUCKET(MONTHS(18), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-01-01T00:00:00.000000Z'

SELECT

    TIME_BUCKET(YEARS(1), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2018-01-01T00:00:00.000000Z'

SELECT

    TIME_BUCKET(YEARS(5), TIMESTAMP '2018-05-26T10:30:20.345Z')

'2015-01-01T00:00:00.000000Z'

SELECT

    TIME_BUCKET(

        YEARS(1000),

        TIMESTAMP '2018-05-26T10:30:20.345Z'

    )

'1970-01-01T00:00:00.000000Z'

The optional origin parameter is of type timestamp.It allows you to specify how the time buckets should be aligned. The origin parameter is only supported if interval is a microsecond interval, and not if it is a month interval (i.e. MONTHS(1) or YEARS(1)). If origin is not specified, 1970-01-01T00:00:00.000Z (UTC) is used. When bucketizing on a month interval, the origin is always 1970-01-01T00:00:00.000Z.

SELECT

    TIME_BUCKET(

        DAYS(1),

        TIMESTAMP '2018-05-26T10:30:20.345Z',

        TIMESTAMP '2016-01-26T06:30:00.000Z'

    )

'2018-05-26T06:30:00.000000Z'

SELECT

    TIME_BUCKET(

        DAYS(1),

        TIMESTAMP '2018-05-26T10:30:20.345Z',

        TIMESTAMP '2023-01-26T06:30:00.000Z'

    )

'2018-05-26T06:30:00.000000Z'

TIMESTAMP_MICROS

TIMESTAMP_MICROS(n)

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

SELECT

    TIMESTAMP_MICROS(1566341708000000)

'2019-08-20T22:55:08.000000Z'

SELECT

    TYPEOF(TIMESTAMP_MICROS(1566341708000000))

'timestamp'

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(n)

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

SELECT

    TIMESTAMP_MILLIS(1566341708000.5)

'2019-08-20T22:55:08.000500Z'

SELECT

    TYPEOF(TIMESTAMP_MILLIS(1566341708000))

'timestamp'

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(n)

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

SELECT

    TIMESTAMP_SECONDS(1566341708)

'2019-08-20T22:55:08.000000Z'

SELECT

    TIMESTAMP_SECONDS(1566341708.5)

'2019-08-20T22:55:08.500000Z'

SELECT

    TYPEOF(TIMESTAMP_SECONDS(1566341708))

'timestamp'

UNIX_MICROS

UNIX_MICROS(ts)

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

SELECT

    UNIX_MICROS(TIMESTAMP '2019-08-20T22:55:08.000000Z')

1566341708000000

UNIX_MILLIS

UNIX_MILLIS(ts)

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

SELECT

    UNIX_MILLIS(TIMESTAMP '2019-08-20T22:55:08.000000Z')

1566341708000

UNIX_SECONDS

UNIX_SECONDS(ts)

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

SELECT

    UNIX_SECONDS(TIMESTAMP '2019-08-20T22:55:08.000000Z')

1566341708

AT TIME ZONE (timestamp)

timestamp AT TIME ZONE timezone

Returns the datetime in the given time zone at a specific timestamp.

SELECT

    CURRENT_TIMESTAMP() AT TIME ZONE 'America/Los_Angeles'

2019-08-19T11:19:06.705792

SELECT

    TYPEOF(

        CURRENT_TIMESTAMP() AT TIME ZONE 'America/Los_Angeles'

    )

'datetime'

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

SELECT

    CURRENT_TIMESTAMP() AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York'

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

EXTRACT timestamp

EXTRACT(part FROM timestamp)

Extracts a component specified by part from timestamp.

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

DATE_TRUNC (timestamp)

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

DATE_DIFF (timestamp)

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.

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.

Interval Constructors

MICROSECONDS

MICROSECONDS(n)

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

SELECT

    TYPEOF(MICROSECONDS(1000000))

'microsecond_interval'

SELECT

    TIME '10:10:10' + MICROSECONDS(1000000)

10:10:11

MILLISECONDS

MILLISECONDS(n)

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

SELECT

    TYPEOF(MILLISECONDS(1000))

'microsecond_interval'

SELECT

    TIME '10:10:10' + MILLISECONDS(1000)

10:10:11

SECONDS

SECONDS(n)

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

SELECT

    TYPEOF(SECONDS(1))

'microsecond_interval'

SELECT

    TIME '10:10:10' + SECONDS(1)

10:10:11

MINUTES

MINUTES(n)

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

SELECT

    TYPEOF(MINUTES(1))

'microsecond_interval'

SELECT

    TIME '10:10:10' + MINUTES(1)

10:11:10

HOURS

HOURS(n)

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

SELECT

    TYPEOF(HOURS(1))

'microsecond_interval'

SELECT

    TIME '10:10:10' + HOURS(1)

11:10:10

DAYS

DAYS(n)

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

SELECT

    TYPEOF(DAYS(1))

'microsecond_interval'

SELECT

    DATE '2019-08-17' + DAYS(1)

2019-08-18T

MONTHS

MONTHS(n)

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

SELECT

    TYPEOF(MONTHS(1))

'month_interval'

SELECT

    DATE '2019-08-17' + MONTHS(1)

2019-09-17

YEARS

YEARS(n)

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

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

SELECT

    TYPEOF(YEARS(1))

'month_interval'

SELECT

    DATE '2019-09-17' + YEARS(1)

2020-09-17

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]

SELECT

    PARSE_DURATION_SECONDS('10:23.1')

{"__rockset_type": "MICROSECOND_INTERVAL", "value": 623100000}

SELECT

    TYPEOF(PARSE_DURATION_SECONDS('10:23.1'))

'microsecond_interval'

SELECT

    DATETIME '2018-05-26T10:30:20.345' + PARSE_DURATION_SECONDS('10:23.1')

2018-05-26T10:40:43.445

PARSE_DURATION_MONTHS

PARSE_DURATION_MONTHS(s)

Parses a month_interval value from a string.

Supported formats are:

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

SELECT

    PARSE_DURATION_MONTHS('1-1')

{"__rockset_type": "MONTH_INTERVAL", "value": 13}

SELECT

    TYPEOF(PARSE_DURATION_MONTHS('1-1'))

'month_interval'

SELECT

    DATETIME '2018-05-26T10:30:20.345' + PARSE_DURATION_MONTHS('1-1')

2019-06-26T10:30:20.345

Interval Durations

DURATION_MICROS

DURATION_MICROS(interval)

Returns the duration of a microsecond_interval in microseconds.

SELECT

    DURATION_MICROS(SECONDS(1))

1000000

SELECT

    DURATION_MICROS(HOURS(1))

3600000000

DURATION_MILLIS

DURATION_MILLIS(interval)

Returns the duration of a microsecond_interval in milliseconds.

SELECT

    DURATION_MILLIS(SECONDS(1))

1000

SELECT

    DURATION_MILLIS(HOURS(1))

3600000

DURATION_SECONDS

DURATION_SECONDS(interval)

Returns the duration of a microsecond_interval in seconds.

SELECT

    DURATION_SECONDS(SECONDS(1))

1

SELECT

    DURATION_SECONDS(HOURS(1))

3600

DURATION_MONTHS

DURATION_MONTHS(interval)

Returns the duration of a month_interval in months.

SELECT

    DURATION_MONTHS(YEARS(2))

24

Valid Datetime Format Strings

This section describes some of the important formats supported to convert a date, time, datetime, or timestamp value to string. The format specification is the standard strftime with CCTZ extensions.

We also support formatting options with the following extentions: