* Nikita Pettik <korablev@tarantool.org> [18/09/18 01:20]:From: Georgy Kirichenko <georgy@tarantool.org>
As a main part of introducing strict typing in SQL it is required to
prohibit typeless columns in parser's grammar. Originally, SQLite simply
assigns typeless columns to BLOB affinity. Moreover, due to historical
reasons, all columns were stored with <SCALAR> type in Tarantool core
(except for <INTEGER> when it comes to primary key). Column type should
be defined on table creation. Allowed data types are: <TEXT>, <VARCHAR>,
<CHAR>, <BLOB>, <INT[EGER]>, <REAL>, <FLOAT>, <NUMERIC>, <DECIMAL>,
<DOUBLE> <DATE> and <DATETIME>. However, still any declared data type is
converted to one of <BLOB>, <TEXT>, <REAL> or <INTEGER> affinities.
While affinity reaches space format, it is (again) converted to
Tarantool's field type. To be more precise, table of conversions:
+----------+----------+------------+
| SQL TYPE | AFFINITY | FIELD TYPE |
+----------+----------+------------+
| FLOAT | REAL | NUMBER |
| REAL | REAL | NUMBER |
| DOUBLE | REAL | NUMBER |
| NUMERIC | REAL | NUMBER |
| DECIMAL | REAL | NUMBER |
| INTEGER | INTEGER | INTEGER |
| TEXT | TEXT | STRING |
| VARCHAR | TEXT | STRING |
| CHAR | TEXT | STRING |
| BLOB | BLOB | SCALAR |
| DATETIME | REAL | NUMBER |
| DATE | REAL | NUMBER |
| TIME | REAL | NUMBER |
+----------+----------+------------+
How do you manage to store datetime/date/time values as numbers?What is your conversion procedure? Are you storing it as unixtimestamp? Note that SQL date begins (AFAIR) at 1900-01-01.
Now we don’t have any facilities to process or store DATETIME
format in any convenient way. SQLite originally only features
DATETIME() function, which converts string to REAL type
(in the same way you mentioned).
Hence, temporary this ’type’ works simply as REAL/FLOAT without
any implicit conversions to unix timestamp or date/time validations.
Another option is remove DATETIME type at all - until it will be implemented,
in order to avoid confusions.