1

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?

user734028
  • 1,021
  • 1
  • 9
  • 21

2 Answers2

2

How did you create that table?

I am guessing that the application just displays the DDL that created the table (as entered by the user) as opposed to re-constructing it from meta-data.

For compatibility you can declare a column type of DATETIME (among many others), and it will be treated as NUMERIC (which in turn is a "flexible" type that will end up storing Text, Real, Integer, Null or Blob depending on what the data looks like).

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • the table was created using sqlitestudio, and it gives an option of column data type DATETIME, so this was created that way. So this means that, according to documentation, this is actually a string of the "TEXT as ISO8601 strings"? – user734028 May 08 '18 at 11:33
2

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.

MikeT
  • 51,415
  • 16
  • 49
  • 68