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.

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.

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.

This consists of:

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

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.

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 10.123 SECOND  # fractional seconds allowed
INTERVAL '2 10:23:45.56' DAY TO SECOND
INTERVAL '10:23' DAY TO MINUTE  # (etc)

Date Functions

CURRENT_DATE

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-12   |
+-------------+--------------+

DATE Constructor

DATE(year, month, date)

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

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-12  |
+---------------+--------------+
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(CURRENT_DATETIME()) - INTERVAL 7 DAY AS string
    ) AS last_week
+---------------+--------------+
| today         | last_week    |
|---------------+--------------+
| 2019-08-19    | 2019-08-12   |
+---------------+--------------+
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-12   |
+---------------+--------------+

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-8-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-12   |
+---------------+--------------+

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/8/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-12   |
+---------------+--------------+

EXTRACT date

EXTRACT(part FROM date)

Extracts a component specified by part from date.

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

PartQueryResult
MICROSECONDSELECT EXTRACT(MICROSECOND FROM DATE '2018-05-26')0
MILLISECONDSELECT EXTRACT(MILLISECOND FROM DATE '2018-05-26')0
SECONDSELECT EXTRACT(SECOND FROM DATE '2018-05-26')0
MINUTESELECT EXTRACT(MINUTE FROM DATE '2018-05-26')0
HOURSELECT EXTRACT(HOUR FROM DATE '2018-05-26')0
DAYSELECT EXTRACT(DAY FROM DATE '2018-05-26')26
DAYOFWEEKSELECT EXTRACT(DAYOFWEEK FROM DATE '2018-05-26')7
DAYOFYEARSELECT EXTRACT(DAYOFYEAR FROM DATE '2018-05-26')146
MONTHSELECT EXTRACT(MONTH FROM DATE '2018-05-26')5
QUARTERSELECT EXTRACT(QUARTER FROM DATE '2018-05-26')2
YEARSELECT EXTRACT(YEAR FROM DATE '2018-05-26')2018
DATESELECT EXTRACT(DATE FROM DATE '2018-05-26')2018-05-26
TIMESELECT EXTRACT(TIME FROM DATE '2018-05-26')00:00:00.000
ISOWEEKSELECT EXTRACT(ISOWEEK FROM DATE '2018-05-26')21
ISOYEARSELECT EXTRACT(ISOYEAR FROM DATE '2018-05-26')2018

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')
true
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.1002'
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('10:30:10')
10:30:10
SELECT
    TYPEOF(PARSE_TIME('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.

TypePartQueryResult
TIMEMICROSECONDSELECT EXTRACT(MICROSECOND FROM TIME '04:30:20.345')345000
TIMEMILLISECONDSELECT EXTRACT(MILLISECOND FROM TIME '04:30:20.345')345
TIMESECONDSELECT EXTRACT(SECOND FROM TIME '04:30:20.345')20
TIMEMINUTESELECT EXTRACT(MINUTE FROM TIME '04:30:20.345')30
TIMEHOURSELECT EXTRACT(HOUR FROM TIME '04:30:20.345')4
TIMEDAYSELECT EXTRACT(DAY FROM TIME '04:30:20.345')1
TIMEDAYOFWEEKSELECT EXTRACT(DAYOFWEEK FROM TIME '04:30:20.345')5
TIMEDAYOFYEARSELECT EXTRACT(DAYOFYEAR FROM TIME '04:30:20.345')1
TIMEMONTHSELECT EXTRACT(MONTH FROM TIME '04:30:20.345')1
TIMEQUARTERSELECT EXTRACT(QUARTER FROM TIME '04:30:20.345')1
TIMEYEARSELECT EXTRACT(YEAR FROM TIME '04:30:20.345')1970
TIMEDATESELECT EXTRACT(DATE FROM TIME '04:30:20.345')1970-01-01
TIMETIMESELECT EXTRACT(TIME FROM TIME '04:30:20.345')04:30:20.345
TIMEISOWEEKSELECT EXTRACT(ISOWEEK FROM TIME '04:30:20.345')1
TIMEISOYEARSELECT EXTRACT(ISOYEAR FROM TIME '04:30:20.345')1970

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
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
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')
true
SELECT
    PARSE_DATETIME_ISO8601('2019-08-19 10:10') > PARSE_DATETIME_ISO8601('2019-08-19 10:10') - INTERVAL 1 SECOND
true
SELECT
    PARSE_DATETIME_ISO8601('2019-08-19 18:19') AS today,
    (
        PARSE_DATETIME_ISO8601('2019-08-19 18:19') - 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.

TypePartQueryResult
DATETIMEMICROSECONDSELECT EXTRACT(MICROSECOND FROM DATETIME '2018-05-26 04:30:20.345')345000
DATETIMEMILLISECONDSELECT EXTRACT(MILLISECOND FROM DATETIME '2018-05-26 04:30:20.345')345
DATETIMESECONDSELECT EXTRACT(SECOND FROM DATETIME '2018-05-26 04:30:20.345')20
DATETIMEMINUTESELECT EXTRACT(MINUTE FROM DATETIME '2018-05-26 04:30:20.345')30
DATETIMEHOURSELECT EXTRACT(HOUR FROM DATETIME '2018-05-26 04:30:20.345')4
DATETIMEDAYSELECT EXTRACT(DAY FROM DATETIME '2018-05-26 04:30:20.345')26
DATETIMEDAYOFWEEKSELECT EXTRACT(DAYOFWEEK FROM DATETIME '2018-05-26 04:30:20.345')7
DATETIMEDAYOFYEARSELECT EXTRACT(DAYOFYEAR FROM DATETIME '2018-05-26 04:30:20.345')146
DATETIMEMONTHSELECT EXTRACT(MONTH FROM DATETIME '2018-05-26 04:30:20.345')5
DATETIMEQUARTERSELECT EXTRACT(QUARTER FROM DATETIME '2018-05-26 04:30:20.345')2
DATETIMEYEARSELECT EXTRACT(YEAR FROM DATETIME '2018-05-26 04:30:20.345')2018
DATETIMEDATESELECT EXTRACT(DATE FROM DATETIME '2018-05-26 04:30:20.345')2018-05-26
DATETIMETIMESELECT EXTRACT(TIME FROM DATETIME '2018-05-26 04:30:20.345')04:30:20.345
DATETIMEISOWEEKSELECT EXTRACT(ISOWEEK FROM DATETIME '2018-05-26 04:30:20.345')21
DATETIMEISOYEARSELECT EXTRACT(ISOYEAR FROM DATETIME '2018-05-26 04:30:20.345')2018

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
    CURRENT_TIME('America/Los_Angeles')
2019-08-19T11: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-07-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(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)
2019-08-20T22:55:08.000000Z
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
    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'

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

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

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

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

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

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

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.

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

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')
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:40:43.346

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.

FORMATMeaningQueryResult
%YYear with centurySELECT FORMAT_DATE('%Y', DATE '2019-08-17')'2019'
%yYear without century (00 - 99)SELECT FORMAT_DATE('%y', DATE '2019-08-17')'19'
%mMonth (01 - 12)SELECT FORMAT_DATE('%Y/%m', DATE '2019-08-17')'2019/08'
%dDay of the month (01 - 31)SELECT FORMAT_DATE('%Y/%m/%d', DATE '2019-08-17')'2019/08/17'
%aAbbreviated name of the day of the weekSELECT FORMAT_DATE('%Y/%m/%d, (%a)', DATE '2019-08-17')'2019/08/17, (Sat)'
%AFull name of the day of the weekSELECT FORMAT_DATE('%Y/%m/%d, (%A)', DATE '2019-08-17')'2019/08/17, (Saturday)
%bAbbreviated month nameSELECT FORMAT_DATE('%d %b, %Y', DATE '2019-08-17')'08 Aug, 2019'
%BFull month nameSELECT FORMAT_DATE('%d %B, %Y', DATE '2019-08-17')'08 August, 2019'
%FEquivalent to %Y-%m-%dSELECT FORMAT_DATE('%F', DATE '2019-08-17')'2019-08-17'
%DEquivalent to %m/%d/%ySELECT FORMAT_DATE('%D', DATE '2019-08-17')'08/17/2019'
%HHour using a 24-hour clock (00 - 23)SELECT FORMAT_TIME('%H', TIME '21:10:11')'21'
%IHour using a 12-hour clock (01 - 12)SELECT FORMAT_TIME('%I', TIME '21:10:11')'09'
%MMinute (00 - 59)SELECT FORMAT_TIME('%H:%M', TIME '21:10:11')'21:10'
%SSeconds (00 - 60)SELECT FORMAT_TIME('%H:%M:%S', TIME '21:10:11')'21:10:11'
%TEquivalent to %H:%M:%SSELECT FORMAT_TIME('%T', TIME '21:10:11')'21:10:11'
%sSeconds since the Epoch, 1970-01-01 00:00:00 (UTC)SELECT FORMAT_TIME('%s', TIME '21:10:11')'76211'

We also support formatting options with the following extentions:

FORMATMeaningQueryResult
%EzNumeric UTC offset (+hh:mm or -hh:mm)SELECT FORMAT_DATETIME('%F, %T %Ez', TIMESTAMP '2019-08-17T10:10:11+7')'2019-08-17, 03:10:11 +00:00'
%E#SSeconds with # digits of fractional precisionSELECT FORMAT_TIME('%H:%M:%E3S', TIME '10:10:11.314567')'10:10:11.314'
%E*SSeconds with full fractional precision (literal ‘*’)SELECT FORMAT_DATE('%H:%M:%E3S', TIME '10:10:11.314567')'10:10:11.314567'