Making sense of date-time and datatypes in SQLite

I’m learning SQL and SQLite at the moment, and from what I understand, SQLite doesn’t support a datetime datatype.

However, when I run the command PRAGMA table_info(Orders); it’s showing one column as being of type datetime.

I’ve read that In SQLite, the datatype of a value is associated with the value itself, not with its container. taken from here https://www.sqlite.org/datatype3.html

I’m trying to understand what exactly this means, which may help to explain.

Can someone help me understand what’s going on here?

Answer

What you see in the column type when you execute:

PRAGMA table_info(tablename)

or:

SELECT * FROM pragma_table_info('tablename');

is the data type that was used for the definition of the column in the CREATE TABLE statement.

SQLite allows the use of anything (even nothing), as long as it is not a reserved word, as a data type.

This:

CREATE TABLE tablename (
  column0 integer primary key, 
  column1 integer,
  column2 text,
  column3 real, 
  column4 string,
  column5 something,
  column6 Jack,
  column7, -- no data type defined
  column8 real char int -- ??,
  column9 datetime  
);

is a valid statement!

From all the above column definitions, SQLite will enforce type checking only for the column column0 which is defined as integer primary key.

From Datatypes In SQLite Version 3/Storage Classes and Datatypes:

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.

When you define a column as datetime, don’t expect SQLite to understand your intention and set any internal constraints so that the values you store in it will be datetime-like.
Actually, by the rules described in Determination Of Column Affinity, this column will have NUMERIC affinity.

Of course you can store datetime values in a column, as described in Date and Time Datatype, by using INTEGER, REAL or TEXT data type, depending on the form of the datetimes that you want: Unix Times, Julian day numbers or ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS") strings respectively.

In conclusion, SQLite will never complain for any value in any column defined as any data type (except for integer primary key).
It is your responsibility to make sure that you store values in the proper format that you can read/write, make calculations, compare etc.
For example, never store text datetimes in any other format than "YYYY-MM-DD HH:MM:SS.SSS" because all SQLite’s datetime functions work with this format only.