SQLite's time functions handle fixed timezone offsets. eg DATETIME(...) understands that 2022-04-10 08:30Z and 2022-04-10 07:30-01:00 are the same point in time. This means we can be explicit about storing UTC times without breaking SQLite's built-in functions.
So now I'm thinking:
Dates (YYYY-MM-DD) will get a datepicker. Dates lack timezones, so it's up to the database author to communicate how they ought to be used/interpreted.
Ambiguous timestamps (YYYY-MM-DD hh:mm) will be considered localtimes. Users in different timezones will see the same values for them. When editing, you'll still get date- and time- specific controls, but it'll be WYSIWYG. If you enter 17:30 as a time, you'll get 17:30 stored in the database, regardless of your local timezone and the server's timezone.
Timestamps with explicit offsets (YYYY-MM-DD hh:mmZ) will get a "good" treatment. We'll fixup the times that are shown in HTML--I'll use CSS to hide the page until the JS has run and fixed up the content, to avoid jarring flashes/reflowing. When editing, I'll show you the time in your local zone, but persist it in UTC with a Z suffix. When a user creates time columns, I'll push them towards this.
If a user has existing data that fits into case (2), they can "upgrade" to case (3) by explicitly updating it to add a Z suffix to make it explicit that it's stored in UTC.
Doing the localization in JS means that the JSON API/CSV APIs will still export machine-readable ISO8601 timestamps.
One thing I dislike is that a user filtering will get a surprising experience. They'll see 2022-10-01 14:30, but would need to filter on 2022-10-01 19:30 (eg). A sufficiently smart UI could paper over this eventually.
If users want to track timezones explicitly, they can still do that, it just won't be baked in
Should fire for TEXT columns whose min/max look like dates, and for DATE and DATETIME columns, even if no data available.
SQLite's time functions handle fixed timezone offsets. eg
DATETIME(...)understands that2022-04-10 08:30Zand2022-04-10 07:30-01:00are the same point in time. This means we can be explicit about storing UTC times without breaking SQLite's built-in functions.So now I'm thinking:
YYYY-MM-DD) will get a datepicker. Dates lack timezones, so it's up to the database author to communicate how they ought to be used/interpreted.YYYY-MM-DD hh:mm) will be considered localtimes. Users in different timezones will see the same values for them. When editing, you'll still get date- and time- specific controls, but it'll be WYSIWYG. If you enter 17:30 as a time, you'll get 17:30 stored in the database, regardless of your local timezone and the server's timezone.YYYY-MM-DD hh:mmZ) will get a "good" treatment. We'll fixup the times that are shown in HTML--I'll use CSS to hide the page until the JS has run and fixed up the content, to avoid jarring flashes/reflowing. When editing, I'll show you the time in your local zone, but persist it in UTC with a Z suffix. When a user creates time columns, I'll push them towards this.If a user has existing data that fits into case (2), they can "upgrade" to case (3) by explicitly updating it to add a Z suffix to make it explicit that it's stored in UTC.
Doing the localization in JS means that the JSON API/CSV APIs will still export machine-readable ISO8601 timestamps.
One thing I dislike is that a user filtering will get a surprising experience. They'll see
2022-10-01 14:30, but would need to filter on2022-10-01 19:30(eg). A sufficiently smart UI could paper over this eventually.If users want to track timezones explicitly, they can still do that, it just won't be baked in
Should fire for TEXT columns whose min/max look like dates, and for DATE and DATETIME columns, even if no data available.
UI: https://air-datepicker.com/examples#timepicker. Fiddle: https://jsfiddle.net/thkoua07/