6 ms·
This always put me off a bit too. I have memories of mySQL supporters back in the early days defending it storing dates like 2022-02-31 and other such type/rang
by dspillett 4y ago
This always put me off a bit too. I have memories of mySQL supporters back in the early days defending it storing dates like 2022-02-31 and other such type/range/other issues with the standard refrain of “but SELECT * is blazing fast!”…
As of last year there is an option to make things more strict (https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html) though
as SQLite doesn't have real date types, unless you are using one of the integer storage options code could insert invalid dates like mysql used to allow.
--
EDIT: having actually read the linked article, it explicitly mentions the date type issue also.
- paulclinger 4y agoEven in the absence of strict tables, you can also add a CHECK constraint whenever limits are needed on the value types. Here is one example from a recent discussion in SQLite forum threads: `check(datetime(x, '+0 seconds') IS x)`.
- chasil 4y agoAn important proviso on the strict tables is that they will not be observed on older SQLite versions lacking this feature. SQLite has been rock-solid since attaining DO-178B, and commonly isn't ever upgraded in many installations. CentOS 7 is using 3.7.17. Since the v3 database format is standardized, the older version can utilize a STRICT database file, but will not have the capability to alter datatype behavior. For these cases, implementing both STRICT and the relevant CHECK constraints is advisable.
- RedShift1 4y agoIf you use integer unix timstamp as datatype for dates, you technically cannot insert an invalid date
- dspillett 4y agoHence "unless you use one of the integer storage options". I suspect many don't because the values being directly human readable could be useful.