Comment by biofuel5
Comment by biofuel5 a day ago
I just store millis or nanos as INTEGER. Never found the correct use for string datetimes, also they're slower and take much more space
Comment by biofuel5 a day ago
I just store millis or nanos as INTEGER. Never found the correct use for string datetimes, also they're slower and take much more space
For storing actual moments in physical time (especially past events), and where the time zone is irrelevant, for sure.
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.
As I said, because time zone definitions change.
If daylight savings 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. At least not in this universe!
The cultural rules tend to be more important when describing future events, where the “human friendly” description is what really defines it.
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.
Units of time can also change. It's possible that a day of 10 hours of 100 minutes could be legislated. Not likely, but possible.
This is the best path in my experience. I typically store timestamps as 64-bit unix seconds in the same way.
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.
The main advantage of string datetimes is that you can decipher what they mean just by looking at them in a table.