SQLite Data types

SqliteData Type

Reference:

SQLite Data Types | Beginner's Tutorial
Http://www.runoob.com/sqlite/sqlite-data-types.html

The SQLite data type is a property that specifies the data type of any object. Each column in SQLite has an associated data type for each variable and expression.

You can use these data types while creating a table. SQLite uses a more general dynamic type System. In SQLite, the data type of a value is related to the value itself, not to its container.

SQLite Storage Class

Each value stored in the SQLite database has one of the following storage classes:

Storage Class	Description
Null	Value is a NULL value.
INTEGER	The value is a signed integer that is stored in 1, 2, 3, 4, 6, or 8 bytes, depending on the size of the value.
REAL	The value is a floating-point value that is stored as a 8-byte IEEE floating-point number.
TEXT	The value is a text string that is stored using the database encoding (UTF-8, utf-16be, or Utf-16le).
Blob	The value is a blob of data that is stored entirely according to its input.

SQLite's storage classes are slightly more common than data types. An integer storage class, for example, contains 6 different lengths of integral data types.

SQLite Affinity (Affinity) type

SQLite supports the affinity type concept for columns. Any column can still store any type of data, and when the data is inserted, the data for that field will take precedence over the affinity type as the value is stored. The current version of SQLite supports the following five types of affinity:

Affinity Type	Description
TEXT	Numeric data needs to be converted to text format before being inserted into the target field.
NUMERIC	When text data is inserted into a numeric field, if the conversion does not result in loss of data information and is completely reversible, SQLite converts the text data to an integer or real type of data, and if the conversion fails, SQLite will still store the data in text mode. For new data of NULL or BLOB type, SQLite will not do any conversion and store the data directly as null or BLOB. It is necessary to note that for constant text in floating-point format, such as "30000.0", if the value can be converted to an integer without losing the value information, SQLite converts it to an integer storage mode.
INTEGER	For a field whose affinity type is integer, the rule is equal to numeric, and the only difference is when the cast expression is executed.
REAL	The rule is basically the same as NUMERIC, the only difference is that the "30000.0" text data is not converted to an integer storage mode.
NONE	Do not convert directly to the data type to which the data belongs.

SQLite affinity Type (Affinity) and type name

The following table lists the various data type names that can be used when creating the SQLite3 table, and also shows the corresponding affinity types :

Data Type	Affinity Type
Int INTEGER TINYINT SMALLINT Mediumint BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER (20) VARCHAR (255) VARYING CHARACTER (255) NCHAR (55) NATIVE CHARACTER (70) NVARCHAR (100) TEXT Clob	TEXT
Blob No datatype specified	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL (10,5) BOOLEAN DATE Datetime	NUMERIC

Boolean Data Type

SQLite does not have a separate Boolean storage class . instead, the Boolean values are stored as integers 0 (false) and 1 (true).

Date and Time data types

SQLite does not have a separate storage class for storing dates and/or times , but SQLite can store dates and times as TEXT, REAL, or INTEGER values.

Storage Class	Date Format
TEXT	The format is "Yyyy-mm-dd HH:MM:SS." SSS "date.
REAL	The number of days starting from midday in November 24, 4714 GMT.
INTEGER	The number of seconds from 1970-01-01 00:00:00 UTC.

You can store dates and times in any of the above formats, and you can use the built-in date and time functions to freely convert different formats.

SQLite Data types