On the application side, I use Dapper and DateTimeOffset to map these to a domain type with proper DateTime/UTC fields.
I've found that storing time as integers in the database has some interesting upsides. For example, range queries over the field tend to be faster.
But for storing future events that are tied to a time zone, you need the string with time zone. Otherwise when time zone definitions change, your time will become wrong.
When describing past events, it’s often most precise to describe the literal universe time that it happened.
Obviously these are just generalities, whether you choose one strategy or another depends on the specific use case.
If daylight saving time gets cancelled by legislation, then the event happening at noon two summers from now, you will still probably want to happen at noon -- the new noon.
But changes to timezones don't apply retroactively.
This is made worse when you have a lot of `createdAt` columns that get set to NOW. You have to deal with the missing Z in all places where it matters. And in general, it is pointless to use the `localtime` parameter since that is the server time, and for UI I want to display the time local for the user. So I want to deal exclusively in UTC on the server and do any time zone conversions on the client.
Worth noting that when I changed to PostgreSQL, its date function does add the Z which makes life easier in general. But it is an inconsistency to be aware of if you use both DBs.
strftime('%Y-%m-%dT%H:%M:%SZ')
strftime('%Y-%m-%dT%H:%M:%fZ')
strftime('%Y-%m-%dT%H:%M:%fZ', column, 'auto')Users could then just use either client or user created functions to do the conversion, in whatever makes sense for the app. If all you need is GMT, just store seconds/milliseconds etc. from epoch. If you want to store older dates like in a historical database, strings or day/month/year split or even just single integer. Name columns appropriately to avoid ambiguity, like "gmt_ms" and it shouldn't cause too many problems.
For example, we’re using better-sqlite3 which has a convenient API for adding SQL functions [1], and we have dozens of helper methods for dealing with time using the temporal-polyfill module.
We have custom JSON-based serialisation formats for PlainDate, PlainTime, PlainDateTime, ZonedDateTome, etc. Then in SQL we can call ‘isDate_Between__(a, b, c)`.
a, b, and c are deserialised by the JS (TS) function, the logic is run, and the result is returned to SQLite. We’ve had no performance issues with this approach, though we’re only dealing with simple CRUD stuff. No big data.
You can even use these functions with generated columns and indexes, but I haven’t found a proper use for this yet in my work.
[1] https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/ap...
But I also use postgres as a sort of better excel, so what do I know. My desktop instance has a million silly small tables, you know back of envelope ideas, exploratory data, to do lists, etc
needusername•7h ago