4 ms·
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
by biofuel5 1y 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
- simonw 1y agoThe main advantage of string datetimes is that you can decipher what they mean just by looking at them in a table.
- o11c 1y agoJust CREATE VIEW something (ahead of time, so it's ready) for the rare time you need to visually inspect it.
- bob1029 1y agoThis 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.
- crazygringo 1y agoFor 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.
- hudsonja 1y agoTimezones just give you a set of rules to determine a cultural description of a given point in time. How is timezone any more or less relevant to a future vs. past event?
- jbverschoor 1y agoTimezones can change.
- SoftTalker 1y agoUnits of time can also change. It's possible that a day of 10 hours of 100 minutes could be legislated. Not likely, but possible.
- netsharc 1y agoThis isn't a very good rebuttal, because one of these things (timezone change) happens quite frequently and the other (changes to units of time) hasn't happened in any noticable scale.
- pgwhalen 1y agoThe 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.
- crazygringo 1y agoAs 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!
- jiggunjer 1y agoUTC is pretty stable though. I recall they will obsolete leap seconds somewhere in the next 10 years
- rini17 1y agoIf users expect functionality for filtering by year or year-month, having indexed string date/time makes implementation trivial.