Datetime with timezone in sqlite is a string, and comparisons are lexicographic which is wrong if there are mixed timezones. The datetime("...") function turns a datetime with timezone into a timezone-less utc datetime which should be used for comparisons.
In order to keep the timezone during storage and type-preserving manipulations, we can't just normalize all datetimes as they're loaded or put into the system. I think it could (must) be done automatically for boolean ops, but sometimes there are tuple exprs and a timezone datetime element can't be normalized as part of the expression (i.e. decomposing and recomposing the tuple).
I think the solution I'll go with is blocking timezone datetimes in binary ops and providing a function to manually convert to utc first, but a more automatic solution would be nice.
Datetime with timezone in sqlite is a string, and comparisons are lexicographic which is wrong if there are mixed timezones. The
datetime("...")function turns a datetime with timezone into a timezone-less utc datetime which should be used for comparisons.In order to keep the timezone during storage and type-preserving manipulations, we can't just normalize all datetimes as they're loaded or put into the system. I think it could (must) be done automatically for boolean ops, but sometimes there are tuple exprs and a timezone datetime element can't be normalized as part of the expression (i.e. decomposing and recomposing the tuple).
I think the solution I'll go with is blocking timezone datetimes in binary ops and providing a function to manually convert to utc first, but a more automatic solution would be nice.