Rockset

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

    DATE Constructor

    DATE(year, month, day)

    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(2019, 8, 19) - INTERVAL 7 DAY 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   |
    +---------------+--------------+

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

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

    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.

    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
    DOWSELECT EXTRACT(DOW FROM DATE '2018-05-26')7
    DAYOFYEARSELECT EXTRACT(DAYOFYEAR FROM DATE '2018-05-26')146
    DOYSELECT EXTRACT(DOY 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

    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.

    TypePrecisionQueryResult
    DATEMILLENNIUMSELECT DATE_TRUNC('MILLENNIUM', DATE '1999-11-20')1000-01-01
    DATECENTURYSELECT DATE_TRUNC('CENTURY', DATE '1999-11-20')1900-01-01
    DATEDECADESELECT DATE_TRUNC('DECADE', DATE '1999-11-20')1990-01-01
    DATEYEARSELECT DATE_TRUNC('YEAR', DATE '1999-11-20')1999-01-01
    DATEQUARTERSELECT DATE_TRUNC('QUARTER', DATE '1999-11-20')1999-10-01
    DATEMONTHSELECT DATE_TRUNC('MONTH', DATE '1999-11-20')1999-11-01
    DATEWEEKSELECT DATE_TRUNC('WEEK', DATE '1999-11-20')1999-11-14
    DATEDAYSELECT DATE_TRUNC('DAY', DATE '1999-11-20')1999-11-20
    DATEmicrosecond_intervalSELECT DATE_TRUNC(INTERVAL 2 DAY, DATE '1999-11-20')1999-11-19
    DATEmonth_intervalSELECT DATE_TRUNC(INTERVAL 2 MONTH, DATE '1999-11-20')1999-11-01

    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.

    TypePrecisionQueryResult
    DATEMILLENNIUMSELECT DATE_DIFF('MILLENNIUM', DATE '1999-11-20', DATE '2999-11-20')1
    DATECENTURYSELECT DATE_DIFF('CENTURY', DATE '1999-11-20', DATE '2999-11-20')10
    DATEDECADESELECT DATE_DIFF('DECADE', DATE '1999-11-20', DATE '2999-11-20')100
    DATEYEARSELECT DATE_DIFF('YEAR', DATE '1999-11-20', DATE '2999-11-20')1000
    DATEQUARTERSELECT DATE_DIFF('QUARTER', DATE '1999-11-20', DATE '2000-11-20')4
    DATEMONTHSELECT DATE_DIFF('MONTH', DATE '1999-11-20', DATE '2000-11-20')12
    DATEWEEKSELECT DATE_DIFF('WEEK', DATE '1999-11-20', DATE '1999-12-20')4
    DATEDAYSELECT DATE_DIFF('DAY', DATE '1999-11-20', DATE '1999-11-28')8

    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.

    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
    TIMEDOWSELECT EXTRACT(DOW FROM TIME '04:30:20.345')5
    TIMEDAYOFYEARSELECT EXTRACT(DAYOFYEAR FROM TIME '04:30:20.345')1
    TIMEDOYSELECT EXTRACT(DOY 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.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.

    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
    DATETIMEDOWSELECT EXTRACT(DOW FROM DATETIME '2018-05-26 04:30:20.345')7
    DATETIMEDAYOFYEARSELECT EXTRACT(DAYOFYEAR FROM DATETIME '2018-05-26 04:30:20.345')146
    DATETIMEDOYSELECT EXTRACT(DOY 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

    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.

    TypePrecisionQueryResult
    DATETIMEMILLENNIUMSELECT DATE_TRUNC('MILLENNIUM', DATETIME '1999-11-20T17:13:56.123456')1000-01-01T00:00:00
    DATETIMECENTURYSELECT DATE_TRUNC('CENTURY', DATETIME '1999-11-20T17:13:56.123456')1900-01-01T00:00:00
    DATETIMEDECADESELECT DATE_TRUNC('DECADE', DATETIME '1999-11-20T17:13:56.123456')1990-01-01T00:00:00
    DATETIMEYEARSELECT DATE_TRUNC('YEAR', DATETIME '1999-11-20T17:13:56.123456')1999-01-01T00:00:00
    DATETIMEQUARTERSELECT DATE_TRUNC('QUARTER', DATETIME '1999-11-20T17:13:56.123456')1999-10-01T00:00:00
    DATETIMEMONTHSELECT DATE_TRUNC('MONTH', DATETIME '1999-11-20T17:13:56.123456')1999-11-01T00:00:00
    DATETIMEWEEKSELECT DATE_TRUNC('WEEK', DATETIME '1999-11-20T17:13:56.123456')1999-11-14T00:00:00
    DATETIMEDAYSELECT DATE_TRUNC('DAY', DATETIME '1999-11-20T17:13:56.123456')1999-11-20T00:00:00
    DATETIMEHOURSELECT DATE_TRUNC('HOUR', DATETIME '1999-11-20T17:13:56.123456')1999-11-20T17:00:00
    DATETIMEMINUTESELECT DATE_TRUNC('MINUTE', DATETIME '1999-11-20T17:13:56.123456')1999-11-20T17:13:00
    DATETIMESECONDSELECT DATE_TRUNC('SECOND', DATETIME '1999-11-20T17:13:56.123456')1999-11-20T17:13:56
    DATETIMEMILLISECONDSSELECT DATE_TRUNC('MILLISECONDS', DATETIME '1999-11-20T17:13:56.123456')1999-11-20T17:13:56.123
    DATETIMEMICROSECONDSSELECT DATE_TRUNC('MICROSECONDS', DATETIME '1999-11-20T17:13:56.123456')1999-11-20T17:13:56.123456
    DATETIMEmicrosecond_intervalSELECT DATE_TRUNC(INTERVAL 1 SECOND, DATETIME '1999-11-20T17:13:56.123456')1999-11-20T17:13:56
    DATETIMEmonth_intervalSELECT DATE_TRUNC(INTERVAL 1 MONTH, DATETIME '1999-11-20T17:13:56.123456')1999-11-01T00:00:00

    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.

    TypePrecisionQueryResult
    DATETIMEMILLENNIUMSELECT DATE_DIFF('MILLENNIUM', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')1
    DATETIMECENTURYSELECT DATE_DIFF('CENTURY', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')10
    DATETIMEDECADESELECT DATE_DIFF('DECADE', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')100
    DATETIMEYEARSELECT DATE_DIFF('YEAR', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2999-11-20T17:13:56.123456')1000
    DATETIMEQUARTERSELECT DATE_DIFF('QUARTER', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2000-11-20T17:13:56.123456')4
    DATETIMEMONTHSELECT DATE_DIFF('MONTH', DATETIME '1999-11-20T17:13:56.123456', DATETIME '2000-11-20T17:13:56.123456')12
    DATETIMEWEEKSELECT DATE_DIFF('WEEK', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-12-20T17:13:56.123456')4
    DATETIMEDAYSELECT DATE_DIFF('DAY', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-28T17:13:56.123456')8
    DATETIMEHOURSELECT DATE_DIFF('HOUR', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-19T17:13:56.123456')-24
    DATETIMEMINUTESELECT DATE_DIFF('MINUTE', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:23:56.123456')10
    DATETIMESECONDSELECT DATE_DIFF('SECOND', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:12:56.123456')-60
    DATETIMEMILLISECONDSSELECT DATE_DIFF('MILLISECONDS', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:13:57.123456')1000
    DATETIMEMICROSECONDSSELECT DATE_DIFF('MICROSECONDS', DATETIME '1999-11-20T17:13:56.123456', DATETIME '1999-11-20T17:13:56.123457')1

    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.

    TypePartQueryResult
    TIMESTAMPMICROSECONDSELECT EXTRACT(MICROSECOND FROM TIMESTAMP '2018-05-26 04:30:20.345Z')345000
    TIMESTAMPMILLISECONDSELECT EXTRACT(MILLISECOND FROM TIMESTAMP '2018-05-26 04:30:20.345Z')345
    TIMESTAMPSECONDSELECT EXTRACT(SECOND FROM TIMESTAMP '2018-05-26 04:30:20.345Z')20
    TIMESTAMPMINUTESELECT EXTRACT(MINUTE FROM TIMESTAMP '2018-05-26 04:30:20.345Z')30
    TIMESTAMPHOURSELECT EXTRACT(HOUR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')4
    TIMESTAMPDAYSELECT EXTRACT(DAY FROM TIMESTAMP '2018-05-26 04:30:20.345Z')26
    TIMESTAMPDAYOFWEEKSELECT EXTRACT(DAYOFWEEK FROM TIMESTAMP '2018-05-26 04:30:20.345Z')7
    TIMESTAMPDOWSELECT EXTRACT(DOW FROM TIMESTAMP '2018-05-26 04:30:20.345Z')7
    TIMESTAMPDAYOFYEARSELECT EXTRACT(DAYOFYEAR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')146
    TIMESTAMPDOYSELECT EXTRACT(DOY FROM TIMESTAMP '2018-05-26 04:30:20.345Z')146
    TIMESTAMPMONTHSELECT EXTRACT(MONTH FROM TIMESTAMP '2018-05-26 04:30:20.345Z')5
    TIMESTAMPQUARTERSELECT EXTRACT(QUARTER FROM TIMESTAMP '2018-05-26 04:30:20.345Z')2
    TIMESTAMPYEARSELECT EXTRACT(YEAR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')2018
    TIMESTAMPDATESELECT EXTRACT(DATE FROM TIMESTAMP '2018-05-26 04:30:20.345Z')2018-05-26
    TIMESTAMPTIMESELECT EXTRACT(TIME FROM TIMESTAMP '2018-05-26 04:30:20.345Z')04:30:20.345
    TIMESTAMPISOWEEKSELECT EXTRACT(ISOWEEK FROM TIMESTAMP '2018-05-26 04:30:20.345Z')21
    TIMESTAMPISOYEARSELECT EXTRACT(ISOYEAR FROM TIMESTAMP '2018-05-26 04:30:20.345Z')2018

    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.

    TypePrecisionQueryResult
    TIMESTAMPMILLENNIUMSELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1999-11-20T17:13:56.123456Z')1000-01-01T00:00:00.000000Z
    TIMESTAMPCENTURYSELECT DATE_TRUNC('CENTURY', TIMESTAMP '1999-11-20T17:13:56.123456Z')1900-01-01T00:00:00.000000Z
    TIMESTAMPDECADESELECT DATE_TRUNC('DECADE', TIMESTAMP '1999-11-20T17:13:56.123456Z')1990-01-01T00:00:00.000000Z
    TIMESTAMPYEARSELECT DATE_TRUNC('YEAR', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-01-01T00:00:00.000000Z
    TIMESTAMPQUARTERSELECT DATE_TRUNC('QUARTER', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-10-01T00:00:00.000000Z
    TIMESTAMPMONTHSELECT DATE_TRUNC('MONTH', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-01T00:00:00.000000Z
    TIMESTAMPWEEKSELECT DATE_TRUNC('WEEK', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-14T00:00:00.000000Z
    TIMESTAMPDAYSELECT DATE_TRUNC('DAY', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-20T00:00:00.000000Z
    TIMESTAMPHOURSELECT DATE_TRUNC('HOUR', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-20T17:00:00.000000Z
    TIMESTAMPMINUTESELECT DATE_TRUNC('MINUTE', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-20T17:13:00.000000Z
    TIMESTAMPSECONDSELECT DATE_TRUNC('SECOND', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-20T17:13:56.000000Z
    TIMESTAMPMILLISECONDSSELECT DATE_TRUNC('MILLISECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-20T17:13:56.123000Z
    TIMESTAMPMICROSECONDSSELECT DATE_TRUNC('MICROSECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-20T17:13:56.123456Z
    TIMESTAMPmicrosecond_intervalSELECT DATE_TRUNC(INTERVAL 1 SECOND, TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-20T17:13:56.000000Z
    TIMESTAMPmonth_intervalSELECT DATE_TRUNC(INTERVAL 1 MONTH, TIMESTAMP '1999-11-20T17:13:56.123456Z')1999-11-01T00:00:00.000000Z

    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.

    TypePrecisionQueryResult
    TIMESTAMPMILLENNIUMSELECT DATE_DIFF('MILLENNIUM', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')1
    TIMESTAMPCENTURYSELECT DATE_DIFF('CENTURY', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')10
    TIMESTAMPDECADESELECT DATE_DIFF('DECADE', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')100
    TIMESTAMPYEARSELECT DATE_DIFF('YEAR', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2999-11-20T17:13:56.123456Z')1000
    TIMESTAMPQUARTERSELECT DATE_DIFF('QUARTER', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2000-11-20T17:13:56.123456Z')4
    TIMESTAMPMONTHSELECT DATE_DIFF('MONTH', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '2000-11-20T17:13:56.123456Z')12
    TIMESTAMPWEEKSELECT DATE_DIFF('WEEK', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-12-20T17:13:56.123456Z')4
    TIMESTAMPDAYSELECT DATE_DIFF('DAY', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-28T17:13:56.123456Z')8
    TIMESTAMPHOURSELECT DATE_DIFF('HOUR', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-19T17:13:56.123456Z')-24
    TIMESTAMPMINUTESELECT DATE_DIFF('MINUTE', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:23:56.123456Z')10
    TIMESTAMPSECONDSELECT DATE_DIFF('SECOND', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:12:56.123456Z')-60
    TIMESTAMPMILLISECONDSSELECT DATE_DIFF('MILLISECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:13:57.123456Z')1000
    TIMESTAMPMICROSECONDSSELECT DATE_DIFF('MICROSECONDS', TIMESTAMP '1999-11-20T17:13:56.123456Z', TIMESTAMP '1999-11-20T17:13:56.123457Z')1

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

    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.

    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')'17 Aug, 2019'
    %BFull month nameSELECT FORMAT_DATE('%d %B, %Y', DATE '2019-08-17')'17 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/19'
    %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:%E*S', TIME '10:10:11.314567')'10:10:11.314567'