Data Types

Space and Time data types are very standard, with only a few differences based on the unique nature of Web3 use-cases. Those differences are marked with *** below.

VARCHAR – text or string of variable size

Can optionally specify the maximum characters, i.e., VARCHAR(16) will only allow 16 characters
*** If size is not specified, defaults to the maximum of 65,536 characters
Storage is allocated when used, so unused characters consume no additional space

CHAR – text or string of fixed size

Must specify the maximum characters, i.e., CHAR(16) stores 16 characters
Storage allocated when defined, so CHAR(16) will always consume 16 bytes
There is no material benefit to CHAR over VARCHAR other than backwards compatibility, so VARCHAR is recommended for newer applications

TINYINT – single byte numeric value, signed

Represents numeric values between -128 and +127
Consumes 1 byte

SMALLINT – two byte numeric value, signed

Represents numeric values between -32,768 and +32,767
Consumes 2 bytes

INTEGER (or INT) – four byte numeric value, signed

Represents numeric values between -2.1B and +2.1B
Consumes 4 bytes

BIGINT – eight byte numeric value, signed

Ranges between -9.2 x1018 and +9.2 x1018
Consumes 8 bytes

DECIMAL – variable byte numeric with set digit size and precision

Must set size (count of all digits) and precision (count after decimal point), i.e., DECIMAL(18,6) can hold a numeric with 12 whole numbers and 6 after the decimal point
*** Due to the extreme size and precision of blockchain use-cases, Space and Time can support near-infinite sized decimal data types. Learn more on our page about Infinite Precision.

DOUBLE (or REAL) – floating point number

Using values and exponents, can represent numbers from very large to very small, but with limitations on high-precision accuracy
Consumes 8 bytes

BOOLEAN – True / False flag

Represents one of two states, either True (on) or False (off)

DATE – validated calendar date

Default format is YYYY-MM-DD
Dates are always validated against standard calendar rules, i.e., invalid dates (such as inserting month 13) will throw a database error
A number of date functions exist to simplify date calculations, see that section below
Note, the DATE type is less efficient that the TIMESTAMP data type

TIME – validated time value

Default format is hh:mm:ss[.nnnnnnnnn]
Times are always validated against standard clock rules, i.e., invalid times (such as inserting minute 61) will throw a database error
Hours range from 0 to 23
Minutes and Seconds range from 0 to 59
A number of time functions exist to simplify date calculations, see that section below

TIMESTAMP – validated date and time combined value

Default format is YYYY-MM-DD hh:mm:ss[.nnnnnnnnn]
Validations are the same as both DATE and TIME data types
A number of date and time functions exist to simplify date calculations, see that section below
Note, this is more efficient than the DATE type, especially for time

BINARY – storage of binary information

Stored as a variable length byte array
Can be used to store binary objects (pictures, audio, etc.)
Maximum byte size is 65k

UUID – randomly generated universally unique identifier

Sometimes referred to as a GUID (globally unique identifier)
Generates a unique 128 bit (16 byte) unique identifier
Frequently used as surrogate keys

Best Practices for Data Types

Use VARCHAR – unlike the older CHAR type, VARCHAR only allocates storage for bytes as needed. This makes for smaller tables and faster queries.
Use DECIMAL(S,0) for Web3 – many Web3 concepts like gas, token values, etc. can range from 60 to 72 digits, bigger than any INT can support, but requiring precision that would be lost using a floating point like REAL or DOUBLE. Space and Time allows for total size up to 300 digits.
Use TIMESTAMP – rather than DATE when applicable, as it will have better performance for most operations.
For very large tables, take time to size your data types to the max application value. For example, if you’re storing a code ranging from 1 to 12, that will fit comfortably in a TINYINT. Storing it as an INT consumes 8x the space, which does increase compute and processing time.