What types of data can SQLite databases store?

Question

What types of data can SQLite databases store?

Answer

SQLite databases can store several different types of data. Some of the most common data types, which we will encounter in this course, are INTEGER, TEXT and REAL.

The INTEGER type is for a signed whole number, such as -25, 0, 100,

The TEXT type is similar to strings in other programming languages, and stores a sequence of characters like ‘123 parkway street’.

The REAL type includes floating point values, like 1.5, 3.141, 103.3333,

It is worth noting that the values of each data type can take the state of NULL (covered in exercise 8) which denotes a lack of value.

16 Likes

Is DATE a separate data type or this is just a form of TEXT?

2 Likes

DATE is not a separate data type in SQLite. Dates can be stored as TEXT, REAL or INTEGER values. Please refer to the corresponding article in SQLite3 documentation. Relevant quote:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

11 Likes

It is a data type recognized by SQL, but can be stored as text, integer or real values in SQLite.

4 Likes

Thank you for the insight!

To remind: Integer, text and real!