Does SQLite actually support DATE type?

Question

After reading https://sqlite.org/datatype3.html which states

"SQLite does not have a storage class set aside for storing dates and/or times."

but able to run this

CREATE TABLE User (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, BORN_ON DATE NULL)

and then see it in "DB Browser for SQL" like this:

I start to wonder if SQLite does support Date type of it is just "faking" the support using other types. And even if so why the DB Browser see it as a Date? Any meta info stored inside the DB?

Answer 1

And even if so why the DB Browser see it as a Date? Any meta info stored inside the DB?

Yes, it simply stores the type name used when the column was created. The linked page calls it "declared type". In this case you get NUMERIC affinity (DATE is even given as one of the examples in 3.1.1) and it behaves like any other column with this affinity:

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved. If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT. For the purposes of this paragraph, hexadecimal integer literals are not considered well-formed and are stored as TEXT. (This is done for historical compatibility with versions of SQLite prior to version 3.8.6 2014-08-15 where hexadecimal integer literals were first introduced into SQLite.) If a floating point value that can be represented exactly as an integer is inserted into a column with NUMERIC affinity, the value is converted into an integer. No attempt is made to convert NULL or BLOB values.

A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.

So if you insert dates looking like e.g. "2021-01-05" they will be stored as strings. But

1. you can also insert strings which don't look like dates.

2. if you insert "20210105" it will be stored as the number 20210105.

You can use CHECK constraints to prevent inserting non-date strings.

See also https://sqlite.org/lang_datefunc.html which says what (string and number) formats date/time functions expect.

Answer 2

Based on what Colonel Thirty Two suggested (read more on the page) it seems that when you declare a field as Date its affinity will be numeric.

So SQLite "fakes" Date with Numerics.

Answer 3

SQLite does not fake Date with Numerics.

There is no Date data type in SQLite.

In Datatypes In SQLite Version 3 it is explained clearly that:

SQLite uses a more general dynamic type system

Instead of data types there are 5 Storage Classes: NULL, INTEGER, REAL, TEXT and BLOB.

Also:

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

So when you use Date as the data type of a column in the CREATE TABLE statement you are not restricted to store in it only date-like values. Actually you can store anything in that column.

Tools like "DB Browser for SQLite" and others may offer various data types to select from to define a column when you create the table.
The selection of the data type that you make is not restrictive, but it is rather indicative of what type of data you want to store in a column.

In fact, you can create a table without even declaring the data types of the columns:

CREATE TABLE tablename(col1, col2) 

or use fictional data types:

CREATE TABLE tablename(col1 somedatatype, col2 otherdatatype)

and insert values of any data type:

INSERT INTO tablename(col1, col2) VALUES 
  (1, 'abc'),
  ('XYZ', '2021-01-06'),
  (null, 3.5)

Answer 4

I have some notes on SQLite dates here: https://internotes.net/sqlite-dates

Here’s the short version.

To begin with, SQLite doesn’t enforce data types. What it does do is type affinity, which basically defines a default interpretation of whatever it finds in a particular column.

SQLite is forgiving enough to take any data type you throw at it. You can make up a type name, and it will accept it, but don’t expect them to know what you’re talking about.

SQLite doesn’t have a specific date type affinity. Instead, you would be expected to use:

• Integer for Unix Epoch times - the number of seconds since the beginning of time (1 January 1970).
• Float for the Julian Day, which is the number of days since Noon 24 November 4714 BC GMT. The decimal is for parts of the day.
• TEXT if you want to represent ISO 8601 format, which is pretty standard in most DBMSs.

Of course, they’re still not dates and times, so SQLite has a number of functions for manipulating them as such. You’ll find more detail on https://www.sqlite.org/lang_datefunc.html .

So, the short answer is no, there is no DATE type. You can choose one of the above type affinities if you want to store and process your date in a particular way.