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

​
Overview

NameSizeMinMaxResolution
DATE4 bytes0001-01-019999-12-311 day

The DATE type represents a calendar date, independent of a time zone.

​
Literal string interpretation

DATE literals follow the ISO 8601 format: YYYY-[M]M-[D]D.

  • YYYY: Four-digit year (0001 - 9999)
  • [M]M: One or two digit month (01 - 12)
  • [D]D: One or two digit day (01 - 31)

Examples

SELECT DATE '2023-02-13';
SELECT '2023-02-13'::DATE;
SELECT CAST('2023-6-03' AS DATE);
SELECT DATE('2023-02-13');

​
Functions and operators

​
Type conversions

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

​
To DATE

From typeExampleNote
TEXTSELECT CAST(TEXT '2023-02-13' as DATE); --> 2023-02-13Interprets the text according to the literal string format.
TIMESTAMPSELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as DATE); --> 2023-02-13Truncates the timestamp to the date.
TIMESTAMPTZSELECT CAST(TIMESTAMPTZ '2023-02-13 Europe/Berlin' as DATE); --> 2023-02-13Converts the timestamptz to local time in the time zone specified by the session’s timezone setting, then truncates the timestamp to the date. This example assumes SET timezone = 'UTC';.

​
From DATE

To typeExampleNote
TEXTSELECT CAST(DATE '2023-02-13' as TEXT); --> 2023-02-13Converts the date to text in the format YYYY-MM-DD.
TIMESTAMPSELECT CAST(DATE '2023-02-13' as TIMESTAMP); --> 2023-02-1 00:00:00Extends the date with 00:00:00.
TIMESTAMPTZSELECT CAST(DATE '2023-02-13' as TIMESTAMPTZ); --> 2023-02-13Interprets the date as midnight in the time zone specified by the session’s timezone setting. This example assumes SET timezone = 'UTC';.

​
Comparison operators

OperatorDescription
DATE < DATELess than
DATE > DATEGreater than
DATE <= DATELess than or equal to
DATE >= DATEGreater than or equal to
DATE = DATEEqual to
DATE <> DATENot equal to

A DATE value is also comparable with a TIMESTAMP or TIMESTAMPTZ value:

  • The DATE value is cast to the TIMESTAMP type for comparison with a TIMESTAMP value.
  • The DATE value is cast to the TIMESTAMPTZ type for comparison with a TIMESTAMPTZ value.

​
Arithmetic operators

The + operators described below come in commutative pairs:

OperatorDescriptionExample
DATE + INTEGER -> DATEAdd a number of days to a dateSELECT DATE '2023-03-03' + 42; --> 2023-04-14
DATE - INTEGER -> DATESubtract a number of days from a dateSELECT DATE '2023-03-03' - 42; --> 2023-01-20
DATE - DATE -> INTEGERSubtract dates, producing the number of elapsed daysSELECT DATE '2023-03-03' - DATE '1996-09-03'; --> 9677
DATE + INTERVAL -> TIMESTAMPAdd an interval to a dateSELECT DATE '1996-09-03' + INTERVAL '42' YEAR; --> 2038-09-03 00:00:00
DATE - INTERVAL -> TIMESTAMPSubtract an interval from a dateSELECT DATE '2023-03-18' - INTERVAL '26 years 5 months 44 days 12 hours 41 minutes'; --> 1996-09-03 11:19:00

​
Interval arithmetic

Arithmetic with intervals can be used to add or subtract a duration to or from a date. The result is of type TIMESTAMP.

Example

SELECT DATE '1996-09-03' + INTERVAL '42' YEAR;  --> 2038-09-03 00:00:00
SELECT DATE '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 DATE value is output as a YYYY-MM-DD string. Input is accepted in the literal format described above: YYYY-[M]M-[D]D.

​
Parquet

DATE maps to Parquet’s 32-bit signed integer DATE type, also representing the number of days before or after 1970-01-01.

​
Avro

DATE maps to Avro’s 32-bit signed integer DATE type, also representing the number of days before or after 1970-01-01.

​
ORC

DATE maps to ORC’s signed integer DATE type, also representing the number of days before or after 1970-01-01.