Skip to main content
This topic describes the Firebolt implementation of the TIMESTAMP data type.

​
Overview

NameSizeMinMaxResolution
TIMESTAMP8 bytes0001-01-01 00:00:00.0000009999-12-31 23:59:59.9999991 microsecond
The TIMESTAMP data type represents a date and time with microsecond resolution independent of a time zone. To represent an absolute point in time, use TIMESTAMPTZ.

​
Literal string interpretation

TIMESTAMP literals follow 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 minute (00 - 59)
  • [s]s: One or two digit second (00 - 59)
  • [.f]: Up to six digits after the decimal separator (000000 - 999999)
If only the date is specified, the time is assumed to be 00:00:00.000000. Examples 
SELECT TIMESTAMP '1996-09-03 11:19:33.123456';
SELECT '2023-02-13'::TIMESTAMP;  --> 2023-02-13 00:00:00
SELECT CAST('2019-7-23T16:9:3.1' as TIMESTAMP);  --> 2019-07-23 16:09:03.1

​
Functions and operators

​
Type conversions

The TIMESTAMP data type can be cast to and from types as follows:

​
To TIMESTAMP

From typeExampleNote
TEXTSELECT CAST(TEXT '2023-02-13 11:19:42' as TIMESTAMP); --> 2023-02-13 11:19:42Interprets the text according to the literal string format.
DATESELECT CAST(DATE '2023-02-13' as TIMESTAMP); --> 2023-02-13 00:00:00Extends the date with 00:00:00.
TIMESTAMPTZSELECT CAST(TIMESTAMPTZ '2023-02-13 Europe/Berlin' as TIMESTAMP); --> 2023-02-13 22:00:00Converts the timestamptz value to local time in the time zone specified by the session’s time_zone setting. This example assumes SET time_zone = 'UTC';.

​
From TIMESTAMP

To typeExampleNote
TEXTSELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as TEXT); --> 2023-02-13 11:19:42Converts the timestamp to text in the format YYYY-MM-DD hh:mm:ss[.f].
DATESELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as DATE); --> 2023-02-13Truncates the timestamp to date.
TIMESTAMPTZSELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as TIMESTAMPTZ); --> 2023-02-13 11:19:42+00Interprets the timestamp to be local time in the time zone specified by the session’s time_zone setting. This example assumes SET time_zone = 'UTC';.

​
Comparison operators

OperatorDescription
TIMESTAMP < TIMESTAMPLess than
TIMESTAMP > TIMESTAMPGreater than
TIMESTAMP <= TIMESTAMPLess than or equal to
TIMESTAMP >= TIMESTAMPGreater than or equal to
TIMESTAMP = TIMESTAMPEqual to
TIMESTAMP <> TIMESTAMPNot equal to
A TIMESTAMP value is also comparable with a DATE or TIMESTAMPTZ value:
  • The DATE value is cast to the TIMESTAMP type for comparison with a TIMESTAMP value.
  • The TIMESTAMP value is cast to the TIMESTAMPTZ type for comparison with a TIMESTAMPTZ value.

​
Arithmetic operators

Arithmetic with intervals can be used to add or subtract a duration to or from a timestamp. The result is of type TIMESTAMP. 
SELECT TIMESTAMP '1996-09-03' + INTERVAL '42' YEAR;  --> 2038-09-03 00:00:00
SELECT TIMESTAMP '2023-03-18' - INTERVAL '26 years 5 months 44 days 12 hours 41 minutes';  --> 1996-09-03 11:19:00
For more information, see Arithmetic with intervals.

​
Serialization and deserialization

​
Text, CSV, JSON

In the text, CSV, and JSON format, a TIMESTAMP value is 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 the literal format described above: YYYY-[M]M-[D]D[( |T)[h]h:[m]m:[s]s[.f]].

​
Parquet

TIMESTAMP maps to Parquet’s 64-bit signed integer TIMESTAMP type with the parameter isAdjustedToUTC set to false and unit set to MICROS, also representing the number of microseconds before or after 1970-01-01 00:00:00.000000.

​
Avro

It’s not possible to import directly from Avro into a TIMESTAMP column. Instead, first import using a TIMESTAMPTZ column and then use the AT TIME ZONE expression to convert to TIMESTAMP.

​
ORC

TIMESTAMP maps to ORC’s “timezone-unaware” logical type “timestamp”.
⌘I