7 ms·

No date/time data type will meet everybody's requirements. Some date/time formats will have a limited range, or won't have enough precision, and on the other h

by SQLite 5y ago

No date/time data type will meet everybody's requirements. Some date/time formats will have a limited range, or won't have enough precision, and on the other hand, some date/time type implementations will give lots of range and precision but then end up taking up too much space on disk. It is not possible to please everyone. It seems better to provide basic low-level datatypes (integer, 64-bit IEEE float, string, BLOB) and let the developer choose whatever date/time representation best meets the needs of the application.

SigmundA 5y ago
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!
electroly 5y ago
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.
tiffanyh 5y ago
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.
pdimitar 5y ago
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.
lenkite 5y ago
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.

by SQLite 5y ago

SigmundA 5y ago

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!

electroly 5y ago

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.

tiffanyh 5y ago

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.

pdimitar 5y ago

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.

lenkite 5y ago

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.