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

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 has extended the DECIMAL type for total size up to 300 digits.
• Use TIMESTAMP – rather than DATE when applicable, as it will have better performance for most operations.