Link Search Menu Expand Document

Data types

This topic lists the data types available in Firebolt.

Numeric

INT

A whole number ranging from -2,147,483,648 to 2,147,483,647. INT data types require 4 bytes of storage. Synonym for INTEGER.

INTEGER

Synonym for INT.

BIGINT

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 LONG.

LONG

Synonym for BIGINT.

FLOAT

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.

DOUBLE

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 DOUBLE PRECISION.

DOUBLE PRECISION

Synonym for DOUBLE.

String

VARCHAR

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 TEXT and STRING.

TEXT

Synonym for VARCHAR and STRING.

STRING

Synonym for VARCHAR and TEXT.

Date and time

DATE

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

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.

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

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 DATE type.

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 DATETIME

DATETIME

Synonym for TIMESTAMP

Boolean

BOOLEAN

Accepts true, false, 1 and 0. Stores the values as 1 or 0 respectively.

Semi-structured

ARRAY

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)