This topic lists the data types available in Firebolt.
- Date and time
A whole number ranging from -2,147,483,648 to 2,147,483,647.
INT data types require 4 bytes of storage. Synonym for
A whole number ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
BIGINT data types require 8 bytes of storage. Synonym for
A floating-point number that has six decimal-digit precision. Decimal (fixed point) types are not supported.
FLOAT data types require 4 bytes of storage.
A floating-point number that has 15 decimal-digit precision. Decimal (fixed point) types are not supported.
DOUBLE data types require 8 bytes. Synonym for
A string of an arbitrary length that can contain any number of bytes, including null bytes. Useful for arbitrary-length string columns. Firebolt supports UTF-8 escape sequences. Synonym for
A year, month and day in the format YYYY-MM-DD. This value is stored as a 4-byte unsigned Unix timestamp. The minimum
DATE value is
1970-01-01. The maximum
DATE value is
2105-12-31. It does not specify 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
CAST(‘2019-07-31' AS DATE) - 4 Returns:
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.
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
CAST ('2105-02-012' AS DATE) + 365
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
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
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);
A year, month, day, hour, minute and second in the format YYYY-MM-DD hh:mm:ss. This value is stored as an unsigned Unix timestamp with 4 bytes.
Same range as
Minimal value: 1970-01-01 00:00:00.
To change the default time zone in Firebolt:
SET DEFAULT_TIMEZONE = "Pacific Standard Time"
This is a synonym for
0. Stores the values as
Represents dense or sparse arrays. An array can contain all data types including nested arrays (array with arrays).
A column whose type is
ARRAY can’t be nullable, but the elements of an
ARRAY are nullable.
For example, the following is an illegal type definition:
array_with_null ARRAY(INT) NULL
This, on the other hand, is a valid definition:
nullElements ARRAY(INT NULL)