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

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 2

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.