[SOLVED] Why is SqliteStudio showing DATETIME column in its Create Table statement

Why is SqliteStudio showing DATETIME column in its Create Table statement

I have read that date time is stored in either Text, Real, or Integer in sqlite, but sqlitestudio shows the datetime column as Datetime, like in the following DDL it shows the DATETIME column:

CREATE TABLE tblWeights (
    uid         STRING,
    weight      DOUBLE,
    dt          DATETIME,
    printed     INTEGER,
    dispdt      DATETIME,
    transid     INTEGER
);

Whats going on?

Solution

The answer will be because that was what was used when creating the table, or altering the table and adding the column.

You can actually have a column type (decalred) of virtually anything, even something like mycolumn rumplestiltskin NOT NULL and this will be recorded/stored as such (type affinity will be NUMERIC). As what SQLite does is apply an algorithim to then apply a type affinity of INTEGER, TEXT, NUMERIC, REAL or BLOB using some simple rules as per :-

3.1. Determination Of Column Affinity The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

If the declared type contains the string "INT" then it is assigned INTEGER affinity.

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.

If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER. Datatypes In SQLite Version 3

However, additionally with the exception of a column that is an alias of rowid or rowid itself (must have a unique INTEGER), you can store any type of value in any type of column. There are subtle differences in how data is stored (the Storage Class) again as per the link previously given.