The latest version of SQLite has a STRICT command to enforce the data types. This option is set per table, but even in a STRICT table you can specify the type of some columns as ANY if you want to allow any type of data in that column (this is not the same meaning of ANY in non-strict tables).
You are limited to the basic types (int, floating point, string, and blob), however. I can somewhat get behind the opinionated argument for not needing more specific types like most common language types, but not the lack of a date type.
i've also found this to be truly bizarre. even more bizarre than not actually respecting (via coercing or error) to the specified type...why even have types, then?
What's so special about having a named type for datetime? User will still need to call functions to manipulate the dates. If only for the default display and import?
I've seen a sqlite database with datetimes in three different formats in the same field, because different parts of the application I inherited had different ideas of how to write a datetime and sqlite accepts everything. It's only a string after all.
That's a mistake that the same bad developer couldn't have done with a PostgreSQL or a MySQL.
I understand that SQLite can store datetime in REAL type no problems, just on import one needs to convert the date strings properly. Of course, stuffing strings verbatim may work too, but that's about the robustness of the load process or the ETL pipeline.
For dates, having a specific date type instead of a text field is required to have proper behavior when sorting and efficient storage/data transfer.
It's also important to have date-related functions on the DB server side, so that you can use them in filtering data before it gets sent over to the user code running on the client, to avoid unnecessary data transfer and allow proper use of indexes in optimizing it.
Also, it is nice if a DB engine can perform the equivalent of `WHERE year(date)=2021` without actually running that function on every date, but rather automatically optimize it to an index lookup of `WHERE date between '2021-01-01' and '2021-12-31'`.
> ...`WHERE year(date)=2021` without actually running that function on every date, but rather automatically optimize it to an index lookup of `WHERE date between '2021-01-01' and '2021-12-31'`
Sure this would be handy. Are there engines that implement such optimization?
I can also see how the 'dated' WHERE clause could be used directly in SQLite to leverage the index. Of course, using year() is more expressive. It may also make sense in such a case to simply add a year column and have it indexed.
Having a standardised format for data from different sources. If there's no standard people will use their OS specific format. That makes it harder to compare datasets from different sources
