TIMESTAMP data type

This topic describes the Firebolt implementation of the TIMESTAMP data type.

Overview
Literal string interpretation
Functions and operators
Serialization and deserialization

Overview

Name	Size	Min	Max	Resolution
`TIMESTAMP`	8 bytes	`0001-01-01 00:00:00.000000`	`9999-12-31 23:59:59.999999`	1 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 type	Example	Note
`TEXT`	`SELECT CAST(TEXT '2023-02-13 11:19:42' as TIMESTAMP); --> 2023-02-13 11:19:42`	Interprets the text according to the literal string format.
`DATE`	`SELECT CAST(DATE '2023-02-13' as TIMESTAMP); --> 2023-02-13 00:00:00`	Extends the date with `00:00:00`.
`TIMESTAMPTZ`	`SELECT CAST(TIMESTAMPTZ '2023-02-13 Europe/Berlin' as TIMESTAMP); --> 2023-02-13 22:00:00`	Converts 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 type	Example	Note
`TEXT`	`SELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as TEXT); --> 2023-02-13 11:19:42`	Converts the timestamp to text in the format `YYYY-MM-DD hh:mm:ss[.f]`.
`DATE`	`SELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as DATE); --> 2023-02-13`	Truncates the timestamp to date.
`TIMESTAMPTZ`	`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 `time_zone` setting. This example assumes `SET time_zone = 'UTC';`.

Comparison operators

Operator	Description
`TIMESTAMP < TIMESTAMP`	Less than
`TIMESTAMP > TIMESTAMP`	Greater than
`TIMESTAMP <= TIMESTAMP`	Less than or equal to
`TIMESTAMP >= TIMESTAMP`	Greater than or equal to
`TIMESTAMP = TIMESTAMP`	Equal to
`TIMESTAMP <> TIMESTAMP`	Not 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”.