4 ms·

SQLite has a number of functions to work with time but the underlying storage format is just going to be--depending on what precision/range of timestamp you wan

by saurik 3y ago

SQLite has a number of functions to work with time but the underlying storage format is just going to be--depending on what precision/range of timestamp you want--a float or an integer... to the extent, of course, that SQLite has types at all (as it frankly doesn't, thereby making this question kind of moot).

simonw 3y ago
SQLite has five types, documented here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html null, integer, real, text, blob It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) but that changed in November 2021 with the release of strict table mode in version 3.37.0: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- bch 3y ago
  > It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) Richard Hipp (inventor, principal dev of SQLite) called it “manifest typing”[0], indeed likely inspired by Tcl[1], of which he is a Core Team member emeritus, and of which SQLite was initially born, as a loadable Tcl package. [0] https://www.sqlite.org/different.html https://www.sqlite.org/different.html [1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?R=0&O=EIAS https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...
- o11c 3y ago
  JSON is a sixth type in practice due to the way `text` acts differently depending on whether it is fed directly from one JSON function to another. See https://sqlite.org/json1.html#value_arguments https://sqlite.org/json1.html#value_arguments
  - kobieps 3y ago
    The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.
- saurik 3y ago
  Ah! I mean, only two years old, but OK ;P. Regardless: my point stands that you can trivially store and work with timestamps in SQLite as they are simply a real or integer or text; like, the idea that you need a special timestamp data type or you can't work with such values in an engine that only two years ago got support for checking the types at all and encourages you to just write code is highly strange.

by saurik 3y ago

simonw 3y ago

SQLite has five types, documented here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html null, integer, real, text, blob It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) but that changed in November 2021 with the release of strict table mode in version 3.37.0: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html

bch 3y ago
> It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) Richard Hipp (inventor, principal dev of SQLite) called it “manifest typing”[0], indeed likely inspired by Tcl[1], of which he is a Core Team member emeritus, and of which SQLite was initially born, as a loadable Tcl package. [0] https://www.sqlite.org/different.html https://www.sqlite.org/different.html [1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?R=0&O=EIAS https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...
o11c 3y ago
JSON is a sixth type in practice due to the way `text` acts differently depending on whether it is fed directly from one JSON function to another. See https://sqlite.org/json1.html#value_arguments https://sqlite.org/json1.html#value_arguments
- kobieps 3y ago
  The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.
saurik 3y ago
Ah! I mean, only two years old, but OK ;P. Regardless: my point stands that you can trivially store and work with timestamps in SQLite as they are simply a real or integer or text; like, the idea that you need a special timestamp data type or you can't work with such values in an engine that only two years ago got support for checking the types at all and encourages you to just write code is highly strange.