3 ms·

> Enhancements to date and time functions… What’s the rationale for SQLite not having a date/time data type?

anamexis 5y ago
I think it's just part of SQLite's broader design philosophy of having a minimal set of core datatypes and leaning heavily on dynamic typing. There's a bit more info here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- SQLite 5y ago
  I like anamexis's explanation better than my own (that I was typing in concurrently). :-) I'm going to upvote his...
  - hitekker 5y ago
    Too late, Doctor Hipp. I've already upvoted you all.
    - deleted 5y ago
      [deleted]
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.
- 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.
- 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.
deleted 5y ago
[deleted]

I think it's just part of SQLite's broader design philosophy of having a minimal set of core datatypes and leaning heavily on dynamic typing. There's a bit more info here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html

SQLite 5y ago
I like anamexis's explanation better than my own (that I was typing in concurrently). :-) I'm going to upvote his...
- hitekker 5y ago
  Too late, Doctor Hipp. I've already upvoted you all.
  - deleted 5y ago
    [deleted]

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.

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.
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.

deleted 5y ago

[deleted]