<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><br class=""><div><blockquote type="cite" class=""><div class="">On 19 Sep 2018, at 05:23, Konstantin Osipov <<a href="mailto:kostja@tarantool.org" class="">kostja@tarantool.org</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">* Nikita Pettik <</span><a href="mailto:korablev@tarantool.org" style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">korablev@tarantool.org</a><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">> [18/09/18 01:20]:</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><blockquote type="cite" style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">From: Georgy Kirichenko <<a href="mailto:georgy@tarantool.org" class="">georgy@tarantool.org</a>><br class=""><br class="">As a main part of introducing strict typing in SQL it is required to<br class="">prohibit typeless columns in parser's grammar. Originally, SQLite simply<br class="">assigns typeless columns to BLOB affinity. Moreover, due to historical<br class="">reasons, all columns were stored with <SCALAR> type in Tarantool core<br class="">(except for <INTEGER> when it comes to primary key). Column type should<br class="">be defined on table creation. Allowed data types are: <TEXT>, <VARCHAR>,<br class=""><CHAR>, <BLOB>, <INT[EGER]>, <REAL>, <FLOAT>, <NUMERIC>, <DECIMAL>,<br class=""><DOUBLE> <DATE> and <DATETIME>. However, still any declared data type is<br class="">converted to one of <BLOB>, <TEXT>, <REAL> or <INTEGER> affinities.<br class="">While affinity reaches space format, it is (again) converted to<br class="">Tarantool's field type. To be more precise, table of conversions:<br class=""><br class="">+----------+----------+------------+<br class="">| SQL TYPE | AFFINITY | FIELD TYPE |<br class="">+----------+----------+------------+<br class="">| FLOAT | REAL | NUMBER |<br class="">| REAL | REAL | NUMBER |<br class="">| DOUBLE | REAL | NUMBER |<br class="">| NUMERIC | REAL | NUMBER |<br class="">| DECIMAL | REAL | NUMBER |<br class="">| INTEGER | INTEGER | INTEGER |<br class="">| TEXT | TEXT | STRING |<br class="">| VARCHAR | TEXT | STRING |<br class="">| CHAR | TEXT | STRING |<br class="">| BLOB | BLOB | SCALAR |<br class="">| DATETIME | REAL | NUMBER |<br class="">| DATE | REAL | NUMBER |<br class="">| TIME | REAL | NUMBER |<br class="">+----------+----------+------------+<br class=""></blockquote><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">How do you manage to store datetime/date/time values as numbers?</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">What is your conversion procedure? Are you storing it as unix</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">timestamp? Note that SQL date begins (AFAIR) at 1900-01-01.</span><br class=""></div></blockquote><div><br class=""></div><div>Now we don’t have any facilities to process or store DATETIME</div><div>format in any convenient way. SQLite originally only features</div><div>DATETIME() function, which converts string to REAL type</div><div>(in the same way you mentioned).</div><div>Hence, temporary this ’type’ works simply as REAL/FLOAT without</div><div>any implicit conversions to unix timestamp or date/time validations.</div><div>Another option is remove DATETIME type at all - until it will be implemented,</div><div>in order to avoid confusions.</div><div><br class=""></div></div><br class=""></body></html>