WebsiteDiscordBlog

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.

Updated 6 months ago