My rule is to either work with integer Unix timestamps or timezone-aware datetime objects. A naive datetime object is, well, naive. If it's the number of seconds since the Epoch, represent it as a number, not an object.
The object is basically just a wrapper around that number anyways. From the programmer's perspective there is not really a difference.
If only there were mechanisms that could use a stored set of predefined rules enabling those other values to be derived when given a unix timestamp.
For instance, a user added an appointment and her timezone is America/Los_Angeles. Then she moved to China and changed her timezone in your app to Asia/Shanghai. Then you run analytics to see how many users had had appointments on weekdays.
When storing date information in persistent stores (like databases), it's always advisable to store the time and the timezone.
If the assumption is that the problem is a theoretical one because nobody has appointments in the middle of the night when DST changes come, I present you with sysadmins, rescue workers and other people that do have these things scheduled at odd times.
I know that around DST changes many people explicitly not work over night to avoid these issues. Same with year changes and other situations where computing and time does not work properly. For instance overnight trains in Europe just wait for an hour and do nothing.
If you store only Unix time, you don't have enough information. If you store both the Unix timestamp, the local time and the timezone, you have too much information. You can derive Unix time from local time and the timezone.
Ergo: store time with time zone.
Because local time is ambiguous. During DST changes local time can mean that it happens twice. Your calendar would have to show 4 AM twice for instance and if you want to make an appointment there you would have to select which one you actually mean, thus UTC + local time.
But granted, it does not happen often that people schedule appointments during DST switches, but certain professions have to so certain people already have to live with that.
> If you store both the Unix timestamp, the local time and the timezone, you have too much information.
Nope. That is incorrect. If I store local time only + timezone and someone sets a date during that transition window it's ambiguous. If I store the date in UTC only and remember the timezone that date was intended for I will not be able to account for changes in the timezone. For instance the legislation of the country could deactivate or change DST settings (which is not uncommon). In that case the timezone information of my operating system will change again and my appointment will move without my consent.
If I store both I can see if they are still in sync and if not, prefer the local time and rebase as necessary.
Yes, storing the local time and the timezone is not exactly what's enough, all that time I was thinking about the PostgreSQL timestamp with time zone type, which is preferable to use vs storing the Unix timestamp.
I guess we've been talking past each other, with me being the main culpable :)
And you save yourself the misery of endless conversion when trying to compare anything internally.