Describes the Firebolt implementation of the TIMESTAMPTZ
data type
TIMESTAMPTZ
data type.
Name | Size | Min | Max | Resolution |
---|---|---|---|---|
TIMESTAMPTZ | 8 bytes | 0001-01-01 00:00:00.000000 UTC | 9999-12-31 23:59:59.999999 UTC | 1 microsecond |
TIMESTAMPTZ
data type represents an absolute point in time as a date and time with microsecond resolution.
Firebolt stores TIMESTAMPTZ
values as Unix time, which is UTC without leap seconds.
TIMESTAMP
, the time zone is deliberately unspecified.
For example, the start of the third millennium was celebrated on New Year’s Day at TIMESTAMP '2001-01-01 00:00:00'
, independent of the geographical location.
However, that doesn’t mean that everybody in the world celebrated at the same absolute point in time.
In Munich, Germany, the new year was celebrated at TIMESTAMPTZ '2001-01-01 00:00:00 Europe/Berlin'
, which was TIMESTAMPTZ '2000-12-31 23:00:00 UTC'
.
Seattle in the United States celebrated nine hours later at TIMESTAMPTZ '2001-01-01 00:00:00 US/Pacific'
, which was TIMESTAMPTZ '2001-01-01 08:00:00 UTC'
.
TIMESTAMPTZ
literals can be specified in one of three formats:
local_timestamp [time_zone]
local_timestamp[time_zone_offset]
local_timestamp[utc_time_zone]
local_timestamp
follows the ISO 8601 and RFC 3339 format: YYYY-[M]M-[D]D[( |T)[h]h:[m]m:[s]s[.f]]
.
YYYY
: Four-digit year (0001
- 9999
)[M]M
: One or two digit month (01
- 12
)[D]D
: One or two digit day (01
- 31
)( |T)
: A space or T
separator[h]h
: One or two digit hour (00
- 23
)[m]m
: One or two digit minutes (00
- 59
)[s]s
: One or two digit seconds (00
- 59
)[.f]
: Up to six digits after the decimal separator (000000
- 999999
)time_zone
is a string containing the name of the time zone.time_zone_offset
is a string representing the offset from the UTC time zone.
(+|-)H[H][:m[m]]
.utc_time_zone
: The letter Z
representing the UTC time zone.TIMESTAMPTZ
literal has an explicit time zone specified, it is converted to Unix time using the appropriate offset.
If not, Firebolt uses the session’s timezone
setting and assumes the TIMESTAMPTZ
literal is in that time zone.
The default value of the timezone
setting is UTC.
To set it to, e.g., Europe/Berlin
, you can issue: SET timezone = 'Europe/Berlin';
.
For more information, see system settings.
If only the date is specified, the time is assumed to be 00:00:00.000000
.
Example
TIMESTAMPTZ
data type can be cast to and from types as follows:
From type | Example | Note |
---|---|---|
TEXT | SELECT CAST(TEXT '2023-02-13 11:19:42 Europe/Berlin' as TIMESTAMPTZ); --> 2023-02-13 00:00:00+00 | Interprets the text according to the literal string format. |
DATE | SELECT CAST(DATE '2023-02-13' as TIMESTAMPTZ); --> 2023-02-13 00:00:00+00 | Interprets the timestamp to be midnight in the time zone specified by the session’s timezone setting. |
TIMESTAMP | SELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as TIMESTAMPTZ); --> 2023-02-13 11:19:42+00 | Interprets the timestamp to be local time in the time zone specified by the session’s timezone setting. |
To type | Example | Note |
---|---|---|
TEXT | SELECT CAST(TIMESTAMPTZ '2023-02-13 11:19:42 Europe/Berlin' as TEXT); --> 2023-02-13 10:19:42+00 | Converts from Unix time to local time in the time zone specified by the session’s timezone setting and appends the UTC offset. |
DATE | SELECT CAST(TIMESTAMPTZ '2023-02-13 11:19:42 Europe/Berlin' as DATE); --> 2023-02-13 | Converts from Unix time to local time in the time zone specified by the session’s timezone setting and then truncates the timestamp to the date. |
TIMESTAMP | SELECT CAST(TIMESTAMPTZ '2023-02-13 11:19:42 Europe/Berlin' as TIMESTAMP); --> 2023-02-13 10:19:42 | Convert from Unix time to local time in the time zone specified by the session’s timezone setting. |
timezone
setting for type conversions can be problematic for implicit conversions.
Therefore, we recommend using the AT TIME ZONE
construct to be explicit about which time zone to use.
TIMESTAMP AT TIME ZONE time_zone_str -> TIMESTAMPTZ
TIMESTAMP
to TIMESTAMPTZ
by interpreting it as local time in the time zone time_zone_str
.SELECT TIMESTAMP '1996-09-03' at time zone 'Europe/Berlin' = TIMESTAMPTZ '1996-09-03 Europe/Berlin'; --> 1
TIMESTAMPTZ AT TIME ZONE time_zone_str -> TIMESTAMP
:TIMESTAMPTZ
to TIMESTAMP
by transforming it from Unix time to local time in the time zone time_zone_str
.SELECT TIMESTAMPTZ '1996-09-03 Europe/Berlin' AT TIME ZONE 'US/Pacific'; --> 1996-09-02 15:00:00
The AT TIME ZONE
construct cannot be used with values of type DATE
.
However, you can explicitly cast a DATE
value to TIMESTAMP
and use the converted value instead.
Operator | Description |
---|---|
TIMESTAMPTZ < TIMESTAMPTZ | Less than |
TIMESTAMPTZ > TIMESTAMPTZ | Greater than |
TIMESTAMPTZ <= TIMESTAMPTZ | Less than or equal to |
TIMESTAMPTZ >= TIMESTAMPTZ | Greater than or equal to |
TIMESTAMPTZ = TIMESTAMPTZ | Equal to |
TIMESTAMPTZ <> TIMESTAMPTZ | Not equal to |
TIMESTAMPTZ
value is also comparable with a DATE
or TIMESTAMP
value:
DATE
value is converted to the TIMESTAMPTZ
type for comparison with a TIMESTAMPTZ
value.TIMESTAMP
value is converted to the TIMESTAMPTZ
type for comparison with a TIMESTAMPTZ
value.TIMESTAMPTZ
values can be used for arithmetic with intervals:
TIMESTAMPTZ
value is shown as local time after conversion from Unix time using the time zone specified in the session’s timezone
setting.
Time zone information using the session’s timezone
setting is shown as a signed numeric offset from UTC (hh
if it is an integral number of hours, hh:mm
if it is an integral number of minutes, else hh:mm:ss
), with a positive sign for zones east of Greenwich.
The date and time components are output as a YYYY-MM-DD hh:mm:ss[.f]
string.
Firebolt outputs as few digits after the decimal separator as possible (at most six).
Input is accepted in one of the literal formats described above.
TIMESTAMPTZ
maps to Parquet’s 64-bit signed integer TIMESTAMP
type with the parameter isAdjustedToUTC
set to true
and unit
set to MICROS
, representing the number of microseconds before or after 1970-01-01 00:00:00.000000 UTC
.
It’s also possible to import into a TIMESTAMPTZ
column from Parquet’s 64-bit signed integer TIMESTAMP
type with the parameter isAdjustedToUTC
set to true
and the unit
set to MILLIS
or NANOS
.
In this case, Firebolt implicitly extends or truncates to resolve in microseconds.
TIMESTAMPTZ
maps to Avro’s 64-bit signed integer timestamp-micros
type, representing the number of microseconds before or after 1970-01-01 00:00:00.000000 UTC
.
It’s also possible to import into a TIMESTAMPTZ
column from Avro’s timestamp-millis
type.
TIMESTAMPTZ
column.
ORC’s logical TIMESTAMP
type is independent of a time zone and this would require a possibly unintended time zone conversion on import.
Instead, first import using a TIMESTAMP
column and then use the AT TIME ZONE
expression to convert to TIMESTAMPTZ
.