SQLite Data Types

In SQLite, types are relatively unimportant because SQLite is not strongly typed. It actually uses five basic data types or, rather, storage classes:

Other database systems such as MySQL and PostgreSQL use static typing. When you declare a column with a specific data type, that column can store only data of the declared type.

SQLite uses a dynamic type system. The data type of a column is determined by the value stored in it, rather than by the column's declared data type. Thus, a single column in SQLite can store mixed data types.

Also, you don't have to declare a specific data type for a column when creating a table.

Storage classes describe the formats that SQLite uses to store data on disk. A storage class is more general than a data type; for example, the INTEGER storage class includes six different integer types. In many cases, you can use storage classes and data types interchangeably.

The following table illustrates five storage classes in SQLite:

Storage_class Meaning When
NULL NULL values mean missing information or unknown.
INTEGER Integer values are whole numbers (either positive or negative). An integer can have variable sizes such as 1, 2,3, 4, or 8 bytes. No quotes, no decimal point, and no exponent
REAL Real values are real numbers with decimal values that use 8-byte floats. A literal has a decimal point or exponent and no enclosing quotes
TEXT TEXT is used to store character data. The maximum length of TEXT is unlimited. SQLite supports various character encodings. A literal is enclosed by single or double quotes
BLOB BLOB stands for a binary large object that can store any kind of data. Theoretically, the maximum size of BLOB is unlimited. A literal has a prefix X'…'

AUTOINCREMENT Constraint

The purpose of AUTOINCREMENT is to prevent the reuse of ROWID's from previously deleted rows.

  1. In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
  2. On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.
  3. If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWID's over the lifetime of the database. Therefore, the purpose of AUTOINCREMENT is to prevent the reuse of ROWID's from previously deleted rows.
  4. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever existed before in that same table. If the table has never before contained any data, then a ROWID of 1 is used.

Function TYPEOF()

SQLite provides the TYPEOF() function that allows you to check the storage class of a value based on its format. See the following example:

SELECT
  TYPEOF(100),
  TYPEOF(10.0),
  TYPEOF('100'),
  TYPEOF(x'1000'),
  TYPEOF(NULL);

Data and Time in SQLite

SQLite does not support built-in date and time storage classes. However, you can use the TEXT, INT, or REAL to store date and time values. For detailed information on handling date and time values.

Indeed, SQLite does not support built-in date and/or time storage class. Instead, it leverages some built-in date and time functions to use other storage classes such as TEXT, REAL, or INTEGER for storing the date and time values.

If you use the TEXT storage class to store date and time value, you need to use the ISO8601 string format as follows:

YYYY-MM-DD HH:MM:SS.SSS

For example, 2016-01-01 10:20:05.123

First, create a new table named datetime_text for demonstration.

CREATE TABLE datetime_text(
  d1 text,
  d2 text
);

To insert date and time values into the datetime_text table, you use the DATETIME() function.

For example, to get the current UTC date and time value, you pass the now literal string to the function as follows:

SELECT datetime('now');

To get the local time, you pass an additional argument: localtime.

SELECT datetime('now','localtime');

Second, insert the date and time values into the datetime_text table as follows:

INSERT INTO datetime_text (d1, d2)
VALUES(datetime('now'),datetime('now', 'localtime'));

You can use the REAL storage class to store the date and/or time values as Julian day numbers, which is the number of days since noon in Greenwich on November 24, 4714 B.C. based on the proleptic Gregorian calendar.

Let's take a look at an example of using the REAL storage class to store date and time values.

First, create a new table named datetime_real:

CREATE TABLE datetime_real(
  d1 real
);

Second, insert the current date and time value into the datetime_real table.

INSERT INTO datetime_real (d1)
VALUES(julianday('now'));

Third, query data from the datetime_real table.

SELECT d1 FROM datetime_real;

But, alas!, the output is not human readable. Fortunately, you can use the built-in date() and time() functions to format a date and time value as follows:

SELECT
  date(d1),
  time(d1)
FROM
  datetime_real;

Besides TEXT and REAL storage classes, you can use the INTEGER storage class to store date and time values.

We typically use the INTEGER to store UNIX time which is the number of seconds since 1970-01-01 00:00:00 UTC. See the following example:

First, create a table that has one column whose data type is INTEGER to store the date and time values.

CREATE TABLE datetime_int (d1 int);

Second, insert the current date and time value into the datetime_int table.

INSERT INTO datetime_int (d1)
VALUES(strftime('%s','now'));

Third, query data from the datetime_int table.

SELECT d1 FROM datetime_int;

To format the result, you can use the built-in datetime() function as follows:

SELECT datetime(d1, 'unixepoch')
FROM datetime_int;