SQLite 3.38 Released

I like anamexis's explanation better than my own (that I was typing in concurrently). :-) I'm going to upvote his...

By this reasoning why supply integer and 64 bit float since they don't cover all requirements and could instead be encoded in strings or blobs just like dates? Why have strings too, just have a binary type?

A date/time data type is very practical even if it doesn't cover all use cases which is why almost all relational database engines have one or more. I suggest something that supports ISO 8601 encoded efficiently similar to SQL Server datetimeoffset which requires 10 bytes for maximum precision.

JSON is also very annoying for not having a date/time type as well!

It is certainly possible, though, to please most people. The date types in other competing SQL engines are widely used; I don't think you get off the hook by just listing a couple of different requirements that people have and saying that it's hard and throwing up your hands. DATETIMEOFFSET is not possible to reasonably implement in SQLite using any of the built-in types because you need special equality semantics that understand how to convert time zone offsets. You can't just pack it into a string or a number and hope for the best; comparisons between values don't work out. There's no way to pack it into a string format in such a way that a regular string comparison will provide chronological ordering and correct equality semantics.

I have built an entire T-SQL layer on top of SQLite and I had to omit DATETIMEOFFSET support entirely. There's no good way to do it with the tools that SQLite provides. Please let me know if I've missed a way; I would like to add this to my extended SQLite language. DATETIMEOFFSET is the gold standard in the SQL Server world and it is a gaping hole in my compatibility layer.

I have also built a virtual table module that links remote SQL Server tables into SQLite. I had to discard the time zone information for DATETIMEOFFSETs in the remote table and show it in SQLite as a UTC time only in string format. This is strictly worse than what we have in SQL Server, and the gap cannot be crossed with clever application-side code.

Thanks Dr Hipp for your response!

Love all things you make (fossil, lemon, SQLite).

While you’re here, I can’t state how much I’d love to see SQLite to push more into client server use cases (Eg WAL2, BEGIN CONCURRENT, etc) I get it might be cleaner to spin that off into its own separate offering. But having something like bedrock but officially from you would be fantastic.

I get where you're coming from but a lot of people will be happy with date/time types that are compatible with SQL Server or PostgreSQL.

There are standard ANSI SQL DATE, TIME, TIMESTAMP and INTERVAL data types.

SQLite is not truly standards compliant by missing these truly essential column types which make it deeply inconvenient for use in enterprise apps. And also for apps that need pluggable/switchable backend SQL storages.

SQLite is an embedded database rather than client-server, meaning runs within the Python interpreter. It's compiled (or at least linked) into the interpreter binary, meaning the version is determined at compile time. A given build of the Python interpreter has only one version of SQLite present in it.