Date and timestamp (legacy)

You are looking at legacy documentation for Firebolt’s deprecated date and timestamp types. New types were introduced in DB version 3.19 under the names PGDATE and TIMESTAMPNTZ, and synonyms DATE, TIMESTAMP and TIMESTAMPTZ made available in DB version 3.22.

If you worked with Firebolt before DB version 3.22, you might still be using the legacy date and timestamp types. Determine which types you are using by executing the query SELECT EXTRACT(CENTURY FROM DATE '2023-03-16');. If this query returns a result, you are already using the redesigned date and timestamp types and can find their documentation here. If this query returns an error, you are still using the legacy date and timestamp types and can continue with this documentation.

Firebolt supports date- and time-related data types:

After DB version 3.22.0, new customers and those who have reingested data to use these types can use synonyms DATE and TIMESTAMP for types PGDATE and TIMESTAMPNTZ to use these supported types, but in the past these types names referred to legacy types:

Dates are counted according to the proleptic Gregorian calendar. Each year consists of 365 days, with leap days added to February in leap years.

PGDATE

A year, month, and day calendar date independent of a time zone. For more information, see DATE data type, but be aware that you need to use the name PGDATE every time the DATE data type document uses the name DATE, as that document assumes you have enabled synonyms for these new types.

Type conversions

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

TIMESTAMPNTZ

A year, month, day, hour, minute, second, and microsecond timestamp independent of a time zone. For more information, see TIMESTAMP data type, but be aware that you need to use the name TIMESTAMPNTZ every time the TIMESTAMP data type document uses the name TIMESTAMP, as that document assumes you have enabled synonyms for these new types.

Type conversions

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

TIMESTAMPTZ

A year, month, day, hour, minute, second, and microsecond timestamp associated with a time zone. For more information, see TIMESTAMPTZ data type. TIMESTAMPTZ is not a legacy data type, and is only available on DB versions after 3.19.0.

Type conversions

The TIMESTAMPTZ data type can be cast to and from types as follows (assuming SET time_zone = 'UTC';):

DATE (legacy)

A year, month and day in the format YYYY-MM-DD. DATE is independent of a time zone.

Arithmetic operations can be executed on DATE values. The examples below show the addition and subtraction of integers.

CAST(‘2019-07-31' AS DATE) + 4

Returns: 2019-08-04

CAST(‘2019-07-31' AS DATE) - 4

Returns: 2019-07-27

Working with dates outside the allowed range

Arithmetic, conditional, and comparative operations are not supported for date values outside the supported range. These operations return inaccurate results because they are based on the minimum and maximum dates in the range rather than the actual dates provided or expected to be returned. PGDATE data type has a much wider range, and we recommend using this type instead.

The arithmetic operations in the examples below return inaccurate results as shown because the dates returned are outside the supported range.

CAST ('1970-02-02' AS DATE) - 365
Returns 1970-01-31

CAST ('2105-02-012' AS DATE) + 365
Returns 2105-12-31

If you work with dates outside the supported range, we recommend that you use a string datatype such as TEXT. For example, the following query returns all rows with the date 1921-12-31.

SELECT
  *
FROM
  tab1text
WHERE
  date_as_text = '1921-12-31';

The example below selects all rows where the date_as_text column specifies a date after 1921-12-31.

SELECT
  *
FROM
  tab1text
WHERE
  date_as_text > '1921-12-31';

The example below generates a count of how many rows in date_as_text are from each month of the year. It uses SUBSTR to extract the month value from the date string, and then it groups the count by month.

SELECT
  COUNT(), SUBSTR(date_as_text,6,2)
FROM
  tab1text
GROUP BY
  SUBSTR(date_as_text,6,2);

TIMESTAMP (legacy)

A year, month, day, hour, minute and second in the format YYYY-MM-DD hh:mm:ss. We recommend using new TIMESTAMPNTZ type instead.

The minimum TIMESTAMP value is 1970-01-01 00:00:00. The maximum TIMESTAMP value is 2105-12-31 23:59.59

Synonyms: DATETIME

Legacy date and timestamp functions

Functions have been redesigned to support new date and timestamp types. Determine how to adjust scripts to use supported functions for new date and timestamp types using the following table.