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
overVARCHAR
other than backwards compatibility, soVARCHAR
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 theTIMESTAMP
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
andTIME
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 olderCHAR
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 anyINT
can support, but requiring precision that would be lost using a floating point likeREAL
orDOUBLE
. Space and Time allows for total size up to 300 digits. - Use
TIMESTAMP
– rather thanDATE
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 aTINYINT
. Storing it as anINT
consumes 8x the space, which does increase compute and processing time.
Updated 10 months ago