Datetime with timezone in sqlite

[andrewbaxter]

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.

[andrewbaxter]

I think there's an argument that you might want to do a comparison and guarantee that both the time and timezone are the same, not that they merely refer to the same instant.

I think greater/less never make sense for dates with timezones (er, taking into account the timezone) so those are clear errors.