DATE data type - Firebolt Documentation

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

Overview

Name	Size	Min	Max	Resolution
`DATE`	4 bytes	`0001-01-01`	`9999-12-31`	1 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 type	Example	Note
`TEXT`	`SELECT CAST(TEXT '2023-02-13' as DATE); --> 2023-02-13`	Interprets the text according to the literal string format.
`TIMESTAMP`	`SELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as DATE); --> 2023-02-13`	Truncates the timestamp to the date.
`TIMESTAMPTZ`	`SELECT CAST(TIMESTAMPTZ '2023-02-13 Europe/Berlin' as DATE); --> 2023-02-13`	Converts 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 type	Example	Note
`TEXT`	`SELECT CAST(DATE '2023-02-13' as TEXT); --> 2023-02-13`	Converts the date to text in the format `YYYY-MM-DD`.
`TIMESTAMP`	`SELECT CAST(DATE '2023-02-13' as TIMESTAMP); --> 2023-02-1 00:00:00`	Extends the date with `00:00:00`.
`TIMESTAMPTZ`	`SELECT CAST(DATE '2023-02-13' as TIMESTAMPTZ); --> 2023-02-13`	Interprets the date as midnight in the time zone specified by the session’s `timezone` setting. This example assumes `SET timezone = 'UTC';`.

Comparison operators

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

Operator	Description	Example
`DATE + INTEGER -> DATE`	Add a number of days to a date	`SELECT DATE '2023-03-03' + 42; --> 2023-04-14`
`DATE - INTEGER -> DATE`	Subtract a number of days from a date	`SELECT DATE '2023-03-03' - 42; --> 2023-01-20`
`DATE - DATE -> INTEGER`	Subtract dates, producing the number of elapsed days	`SELECT DATE '2023-03-03' - DATE '1996-09-03'; --> 9677`
`DATE + INTERVAL -> TIMESTAMP`	Add an interval to a date	`SELECT DATE '1996-09-03' + INTERVAL '42' YEAR; --> 2038-09-03 00:00:00`
`DATE - INTERVAL -> TIMESTAMP`	Subtract an interval from a date	`SELECT 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.

On this page

Overview
Literal string interpretation
Functions and operators
Type conversions
To DATE
From DATE
Comparison operators
Arithmetic operators
Interval arithmetic
Serialization and deserialization
Text, CSV, JSON
Parquet
Avro
ORC

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

Overview

Name	Size	Min	Max	Resolution
`DATE`	4 bytes	`0001-01-01`	`9999-12-31`	1 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 type	Example	Note
`TEXT`	`SELECT CAST(TEXT '2023-02-13' as DATE); --> 2023-02-13`	Interprets the text according to the literal string format.
`TIMESTAMP`	`SELECT CAST(TIMESTAMP '2023-02-13 11:19:42' as DATE); --> 2023-02-13`	Truncates the timestamp to the date.
`TIMESTAMPTZ`	`SELECT CAST(TIMESTAMPTZ '2023-02-13 Europe/Berlin' as DATE); --> 2023-02-13`	Converts 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 type	Example	Note
`TEXT`	`SELECT CAST(DATE '2023-02-13' as TEXT); --> 2023-02-13`	Converts the date to text in the format `YYYY-MM-DD`.
`TIMESTAMP`	`SELECT CAST(DATE '2023-02-13' as TIMESTAMP); --> 2023-02-1 00:00:00`	Extends the date with `00:00:00`.
`TIMESTAMPTZ`	`SELECT CAST(DATE '2023-02-13' as TIMESTAMPTZ); --> 2023-02-13`	Interprets the date as midnight in the time zone specified by the session’s `timezone` setting. This example assumes `SET timezone = 'UTC';`.

Comparison operators

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

Operator	Description	Example
`DATE + INTEGER -> DATE`	Add a number of days to a date	`SELECT DATE '2023-03-03' + 42; --> 2023-04-14`
`DATE - INTEGER -> DATE`	Subtract a number of days from a date	`SELECT DATE '2023-03-03' - 42; --> 2023-01-20`
`DATE - DATE -> INTEGER`	Subtract dates, producing the number of elapsed days	`SELECT DATE '2023-03-03' - DATE '1996-09-03'; --> 9677`
`DATE + INTERVAL -> TIMESTAMP`	Add an interval to a date	`SELECT DATE '1996-09-03' + INTERVAL '42' YEAR; --> 2038-09-03 00:00:00`
`DATE - INTERVAL -> TIMESTAMP`	Subtract an interval from a date	`SELECT 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.

On this page

Overview
Literal string interpretation
Functions and operators
Type conversions
To DATE
From DATE
Comparison operators
Arithmetic operators
Interval arithmetic
Serialization and deserialization
Text, CSV, JSON
Parquet
Avro
ORC

​Overview

​Literal string interpretation

​Functions and operators

​Type conversions

​To DATE

​From DATE

​Comparison operators

​Arithmetic operators

​Interval arithmetic

​Serialization and deserialization

​Text, CSV, JSON

​Parquet

​Avro

​ORC

Firebolt Core

​Overview

​Literal string interpretation

​Functions and operators

​Type conversions

​To DATE

​From DATE

​Comparison operators

​Arithmetic operators

​Interval arithmetic

​Serialization and deserialization

​Text, CSV, JSON

​Parquet

​Avro

​ORC

Overview

Literal string interpretation

Functions and operators

Type conversions

To DATE

From DATE

Comparison operators

Arithmetic operators

Interval arithmetic

Serialization and deserialization

Text, CSV, JSON

Parquet

Avro

ORC

Overview

Literal string interpretation

Functions and operators

Type conversions

To DATE

From DATE

Comparison operators

Arithmetic operators

Interval arithmetic

Serialization and deserialization

Text, CSV, JSON

Parquet

Avro

ORC