Test & document date/time handling along with pitfalls

[brodybits]

From discussion in https://groups.google.com/forum/#!topic/phonegap/za7z51_fKRw I suspect date/time handling with JavaScript and SQLite databases can be a bit tricky. (I am not sure about IndexedDB but suspect similar issues with LocalStorage.) I can think of the following ways to store date/time in a SQLite database:

• Integer (NUMERIC) value (note that the SQLite DATETIME typename has a NUMERIC type affinity)
• String value

If someone just uses something like Date.now() in a SQL parameter list, its toString() function is called and the plugin would simply store the string value. (I am 99.9% sure that this would be the case for Web SQL as well.) I suspect that it is possible to use the valueOf() member function to get the numeric value.

It can also be tricky to figure out how to use date comparisons when selecting data.

From https://groups.google.com/d/msg/phonegap/za7z51_fKRw/gMCypOCwBQAJ:

There's actually two tricks to solving this.

First, if you declare your field as a datetime (which is nothing more than an integer) you will NEVER get this to work. Not under iOS 9.x, not under Android and not on Chrome 52.x (64-bit) on Windows 10.

What you have to do is store your date as a TEXT field and store your datetime as a string like this:

tx.executeSql("CREATE TABLE IF NOT EXISTS mytable (msg_id UNSIGNED INT PRIMARY KEY ASC, msg_when TEXT, msg_read INTEGER, title TEXT, msg_text TEXT)",

Next, you have to wrap your comparison in (parenthesis) so that sqlite treats it as a formula and does the math conversions for you... like this:

tx.executeSql(DELETE FROM mytable WHERE (msg_when <= datetime('now', '-4 days'))",

Notice the parenthesis before the (msg_when column and after the closing parenthesis on the datetime)) function.

(Note that he gave the JavaScript samples with the callback functions cut off.)

I personally think it should be possible and perhaps a little more efficient to use numeric "DATETIME" fields but may well be mistaken here. Unfortunately I cannot promise when I will have a chance to test this.

[brodybits]

From further discussion in https://groups.google.com/forum/#!topic/phonegap/za7z51_fKRw I think the following SQLite date/time pitfalls can be identified:

• DATETIME keyword has a very strange characteristic of overloading: in CREATE TABLE it is an alias for the NUMERIC type affinity (ref: https://www.sqlite.org/datatype3.html#section_3_2) while DATETIME is also a function call that returns a TEXT string (ref: https://www.sqlite.org/lang_datefunc.html)
• Data is stored in SQLite with dynamic typing (ref: https://www.sqlite.org/datatype3.html#section_1) and column type names in CREATE TABLE only specify type affinity (https://www.sqlite.org/datatype3.html#section_3). A number of these are just aliases (ref: https://www.sqlite.org/datatype3.html#section_3_2). I think the idea is to provide a high level of compatibility with "more traditional SQL implementations" while supporting dynamic typing. But this means that in general SQLite does not really enforce data typing. For example, it would be possible to do CREATE TABLE with a column of INTEGER affinity then store a TEXT value that can then be retrieved (I just tried it with the sqlite3 CLI).
• It is possible to store date/time in multiple formats: TEXT for "ISO8601 strings", REAL for "Julian day numbers", or INTEGER for "Unix Time" (refs: https://www.sqlite.org/datatype3.html#section_2_2 http://stackoverflow.com/questions/26978342/how-to-store-datetime-in-sqlite). Especially given the DATETIME keyword overloading described above, it would be possible for one function to store a date in one format and then another function to expect the date in a different format. For example, it is entirely possible to store a TEXT value in a column with INTEGER affinity as described in the previous point.
• Since DATETIME acts as an alias for NUMERIC in CREATE TABLE, it does not specify whether the column was intended to store a REAL ("Julian day" value) or INTEGER ("Unix Time" value). In addition the NUMERIC type affinity is also designed to deal with TEXT values (ref https://www.sqlite.org/datatype3.html#section_3: "A column with NUMERIC affinity may contain values using all five storage classes"). So again it would be possible for one function to store the date/time in one format and another function (or another programmer) to expect the date/time in a different format.
• The type affinity can affect value comparisons ref: https://www.sqlite.org/datatype3.html#section_4
• When using a WHERE clause (in DELETE for example), the comparison should be wrapped in parentheses. @brodybits TODO: test and document this

Additional SQLite pitfall(s) in general:

• According to https://www.sqlite.org/datatype3.html#section_3_2: in CREATE TABLE columns declared as "STRING" would have NUMERIC instead of TEXT affinity and columns declared as "FLOATING POINT" would have INTEGER instead of REAL affinity.

I can identify the following TODOs:

• Document the pitfalls above
• Describe the DATETIME and related pitfalls on the SQLite user forum and push to get them clearly documented on sqlite.org
• Document the date/time storage options along with a comparison of pros and cons. Refs again: https://www.sqlite.org/datatype3.html#section_2_2 http://stackoverflow.com/questions/26978342/how-to-store-datetime-in-sqlite
• Document with examples how date/time columns can be created with "DATETIME" (NUMERIC, possibly INTEGER), INTEGER, TEXT, or possibly REAL type affinity along with the pros & cons of each
• Test and document how to do basic date comparisons in case of dates stored in each of these formats (maybe not for REAL "Julian day" values)
• Further testing and documentation of how comparisons work and how comparisons may be affected by declared type affinity (ref: https://www.sqlite.org/datatype3.html#section_4)

In general I would really like to see these items clearly documented by the SQLite project.

P.S. I found http://stackoverflow.com/questions/26978342/how-to-store-datetime-in-sqlite through reference at: http://stackoverflow.com/questions/17227110/what-is-the-best-way-to-save-datetime-type-in-sqlite

[brodybits]

Also linking to #415 with another pitfall: no explicit handling of JavaScript Date object