You are looking at the documentation for Firebolt’s redesigned date and timestamp types. These types were introduced in DB version 3.19 under the names
TIMESTAMPTZ, and synonyms
TIMESTAMPTZmade available in DB version 3.22.
If you worked with Firebolt before DB version 3.22, you might still be using the legacy date and timestamp types. Determine which types you are using by executing the query
SELECT EXTRACT(CENTURY FROM DATE '2023-03-16');. If this query returns a result, you are using the redesigned date and timestamp types and can continue with this documentation. If this query returns an error, you are using the legacy date and timestamp types and can find legacy documentation here, or instructions to use the new types here.
Converts a string to
TIMESTAMPTZ type (i.e., timestamp with time zone) using format.
The functions returns the new date data type
TIMESTAMPTZ. If you seek for the old type, see TO_TIMESTAMP (legacy).
|Parameter||Description||Supported input types|
| ||The text to convert to a timestamp with time zone. If no optional || |
| ||Optional. A string literal that specifies the format of the || |
<format> patterns include the following specifications:
| ||Year (4 or more digits)|| |
| ||Last 3 digits of year|| |
| ||Last 2 digits of year|| |
| ||Last digit of year|| |
| ||Full month name (case insensitive)|| |
| ||abbreviated month name (3 chars, case insensitive)|| |
| ||Month number (01–12)|| |
| ||Day of month (01–31)|| |
| ||Hour of day (01–12)|| |
| ||Hour of day (00–23)|| |
| ||Minute (00–59)|| |
| ||Second (00–59)|| |
| ||Millisecond (000–999)|| |
| ||Microsecond (000000–999999)|| |
| ||meridiem indicator (without periods)|| |
| ||meridiem indicator (with periods)|| |
| ||Time zone hours|| |
| ||Time zone minutes|| |
Usage notes for formatting
- Case letters in the input
- A separator (non-digit and non-letter) in the
<format>string will match exactly one separator or is skipped
- Any non-separator in the
<format>that is not part of a format option will match exactly one other character.
- Any character in quotes
"will match exactly one other character.
- If the year format specification is
'Y'and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, (e.g.,
- Milliseconds and microseconds are interpreted as subseconds in the form
ss.xxx[xxx]. This means that
TO_TIMESTAMP('30.7', 'SS.MS')is not 7 milliseconds, but 700, because it will be treated as 30 + 0.7 seconds. To achieve 7 milliseconds, one ust write
- Modifiers (e.g.,
'FM') are not supported.
The example below shows our separators and non-separators can cause skips. The separator
' ' (space) in the
<format> matches the other separator
'/' in the
<expression>. The non-separator
'x' will match any other character, in this case the
'a'. Lastly, the two separators
'++' will match up to two other separators, here the first
'.' while the second
'x' will simply be ignored as no other separators follow.
SELECT TO_TIMESTAMP( '2023/aJUN.23', 'YYYY xMON++DD' );
The example below shows how the year is adjusted to be nearest to 2020 because
YYY was used to match a less than four digit number. To receive the exact year
YYYY instead. Furthermore, as the three separators are quotes
"..." they will match any character (separator or non-separator) which in this case is
SELECT TO_TIMESTAMP( 'Year 180: August 4th', 'xx"..."yyy: month DDxx' );
SELECT TO_TIMESTAMP( 'Date: August 2nd, 2023 at 3pm +2', 'Date: month DDxx, YYYY at HH12am TZH' );
SELECT TO_TIMESTAMP( 'h:19 m:34 s:29 ms:035 us:000123', 'X:hh24 X:mi X:ss XX:ms XX:us' );