Time matters: when UTC time is just not enough(lazystone.github.io)
lazystone.github.io
Time matters: when UTC time is just not enough
https://lazystone.github.io//programming/time/2017/03/13/time-matters.html
65 comments
I work on a room and equipment reservation system. To my users, the time of the booking is always the time on the clock on the wall where the booking is. It never changes, regardless of what insanity the time zones go through. So, it is stored twice, as a wall clock timestamp without tz, which is authorative, and as utc, derived every time the wall clock time is saved. It is a best of both worlds solution, utc for when we need to compare against now (to know when a booking becomes final) and wall clock time as authorative source and for user display.
No disasters so far. ;)
No disasters so far. ;)
[deleted]
This is an acceptable solution in your situation it wouldn't be in mine and only storing local time is never acceptable IMHO.
I disagree in cases like this, where local wall time is the source of truth. I would go further than OP and not keep UTC time around in a separate column either.
Why? On March 13 last year, Chile announced a new daylight savings change, to take place on May 15. [1] Let's say I already had an appointment somewhere for 3:00pm on May 21. When the timezone change was announced, that reservation is still at 3:00pm local. It is not still at the same time in UTC.
utc_time is derived from local_time and timezone, not the other way around.
(I am generally a fire-breathing advocate for UTC everywhere)
[1] http://codeofmatt.com/2016/04/23/on-the-timing-of-time-zone-...
Why? On March 13 last year, Chile announced a new daylight savings change, to take place on May 15. [1] Let's say I already had an appointment somewhere for 3:00pm on May 21. When the timezone change was announced, that reservation is still at 3:00pm local. It is not still at the same time in UTC.
utc_time is derived from local_time and timezone, not the other way around.
(I am generally a fire-breathing advocate for UTC everywhere)
[1] http://codeofmatt.com/2016/04/23/on-the-timing-of-time-zone-...
Would not epoch time then make the most sense?
He's storing both. Actually that's what I did in a system to store events. Clients sent both their local time and UTC. UTC was used to sort events and local time to display the event time to users, in the time zone the event was generated into, forever. I think I also stored the UTC timestamp of when the server received the event.
Storing and comparing UTC is the minimum for sure but you often need to store local time and zone as entered and sometimes you do need to change what that is in UTC after the fact. I worked on a internationally-focused scheduling application with clients in multiple timezones doing recurring events in future. Israeli customer's computers for instance could not be trusted to tell us the correct timezone so we needed to ask explicitly their intent then due the 'Israel Summer Time's offset being a political issue[1] we would have to fix up our calculations after the fact: [1] https://en.wikipedia.org/wiki/Israel_Summer_Time#2005.E2.80....
> Flight arrival and departure times
As a former commercial pilot, we use UTC exclusively when flight planning and reporting waypoints - no matter where we are in the world.
When I report that we expect to reach waypoint 'X' at 0451, then anybody listening in ANYWHERE in the world knows exactly how long before I get there, or how long overdue I am. No ambiguity whatsoever.
To this day, in my new life as a developer, I ensure that all my servers are set to UTC and I always use UTC as my time reference datum.
As a former commercial pilot, we use UTC exclusively when flight planning and reporting waypoints - no matter where we are in the world.
When I report that we expect to reach waypoint 'X' at 0451, then anybody listening in ANYWHERE in the world knows exactly how long before I get there, or how long overdue I am. No ambiguity whatsoever.
To this day, in my new life as a developer, I ensure that all my servers are set to UTC and I always use UTC as my time reference datum.
You're extremely lucky. Most of us have to deal with the fact that people are more interested in local time than some idealized time.
Good point, and it is also a reason why we used 'humanised time' in our apps where possible, i.e. the old "you posted this 7 minutes ago" rather than "you posted this at 07:35" because it gives the same datum no matter where our users are.
But even now when we schedule meetings with (IT) colleagues in other countries, I will always reiterate/exchange the meeting times in UTC so that there is no confusion at all when we start the sessions.
But even now when we schedule meetings with (IT) colleagues in other countries, I will always reiterate/exchange the meeting times in UTC so that there is no confusion at all when we start the sessions.
I know this is totally off-topic, but I'd love to hear about how and why you transitioned from pilot to developer.
Yes, but if you want passengers to show up on time you better tell them the local time.
Ah, that is a problem for Operations! :P
But honestly, when I was first learning to fly, it was a difficult thing to get used to. Like most pilots back then, I got one of those watches with two faces and set one (usually the primary/largest one) to UTC. After getting used to dealing purely in UTC for a while, I was amazed that more people didn't do it.
I stopped looking at time as being defined by the position of the sun, but as a reference datum. If a colleague called out "See you at the pub at oh-two hundred hours", I never thought "2 in the morning??" but rather "Oh, that is 1 hour and 45 minutes from now - cool!".
But honestly, when I was first learning to fly, it was a difficult thing to get used to. Like most pilots back then, I got one of those watches with two faces and set one (usually the primary/largest one) to UTC. After getting used to dealing purely in UTC for a while, I was amazed that more people didn't do it.
I stopped looking at time as being defined by the position of the sun, but as a reference datum. If a colleague called out "See you at the pub at oh-two hundred hours", I never thought "2 in the morning??" but rather "Oh, that is 1 hour and 45 minutes from now - cool!".
Sorry, no. Clients can be responsible for themselves. All I care about is providing unambiguous, standardized data to everyone. If they want to corrupt it after that, it's on them, not me.
Unless you do recurrent scheduling. In that case it's important to understand that you schedule in a particular timezone. Both because the relationship between local time an UTC changes throughout the year with DST changes and because evaluating an expression like "Thursdays at 03:00" might evaluate differently if you first move the time to UTC (say 22:00) and then check if it's a Thursday (no, it's only Wednesday still, but in 24 hours it'll be time).
Disagree. DST is actually part of the time signature.
You are either in DST or not - that's why we have PST and PDT - one is a daylight time and reflects a different offset from standard.
Most people are in the same timezone but when not, it's the responsibility of the attendee to know that it's shifted - and the calendaring system should know to update it accordingly.
You are either in DST or not - that's why we have PST and PDT - one is a daylight time and reflects a different offset from standard.
Most people are in the same timezone but when not, it's the responsibility of the attendee to know that it's shifted - and the calendaring system should know to update it accordingly.
How you store and how you represent time are different but related problems.
As for how you should store times, yes you should always store time as UTC. This is a different problem.
As for how you should store times, yes you should always store time as UTC. This is a different problem.
It is reasonable to represent reoccurring events outside of UTC. If your user wants an alarm is at 7:30 AM MTWTF then you can store it just like that. If they travel to another time zone and they still get a 7:30 AM wake up call that's generally desired behavior. And yes there are a huge number of edge cases.
It's specific times you want UTC, if I setup a meeting then I want someone in another time zone to call in at the same time.
It's specific times you want UTC, if I setup a meeting then I want someone in another time zone to call in at the same time.
I'm sorry, but if you have an application dealing with time and you want to even think of defining a behavior independent of time zone, you should still be storing values in UTC.
Your example may work fine on a server that you control, but let's say your use case is a phone alarm program. You can't guarantee that your application will know when it's 7:30AM in the local time, but you can guarantee what the time is in UTC and not need network or the device's help to do it.
So now the time format you store depends on the architecture of your platform? What if you have to send this data between platforms now? Am I going to have to convert back and forth for each message? No thanks. I'll just store everything as UTC and stay sane.
Your example may work fine on a server that you control, but let's say your use case is a phone alarm program. You can't guarantee that your application will know when it's 7:30AM in the local time, but you can guarantee what the time is in UTC and not need network or the device's help to do it.
So now the time format you store depends on the architecture of your platform? What if you have to send this data between platforms now? Am I going to have to convert back and forth for each message? No thanks. I'll just store everything as UTC and stay sane.
Please make the distinction that _instants in time_ should be stored in the UTC but time specifications for scheduling purposes (10:30, mondays 14:50, 2017-05-17T13:40) are not instants in time, they are specification of time to be interpreted in a specific time zone and should be stored as such.
In a very real example there was once a product which let you schedule events by specifying a start date and a recurrence interval, say "daily". The software converted the start time to UTC in the zone of the schedule creator and stored that. But the conversion was done at the creation time which means it was done with a particular offset (say -5 hours) and when the relationship between the creators time zone and UTC changed as a result of DST changes the schedule ended up one hour of from what the creator intended. And the information about which timezone the thing had originally been scheduled in was lost and could not be retrieved.
In a very real example there was once a product which let you schedule events by specifying a start date and a recurrence interval, say "daily". The software converted the start time to UTC in the zone of the schedule creator and stored that. But the conversion was done at the creation time which means it was done with a particular offset (say -5 hours) and when the relationship between the creators time zone and UTC changed as a result of DST changes the schedule ended up one hour of from what the creator intended. And the information about which timezone the thing had originally been scheduled in was lost and could not be retrieved.
What was this like...1990?
This is 2017. Are you not putting all of your transactions in an ordered table in a database with creation timestamps? That information should not be lost and should be easy to figure out, in UTC. Figuring out the conversion of some other time/date is a solved problem.
There should never be a question of when some work happened if you're following sane practices.
I do huge ETL projects that live or die on the accuracy of our timing data. Give me date(time)s in UTC and I'm happy. Anything else? Pain.
This is 2017. Are you not putting all of your transactions in an ordered table in a database with creation timestamps? That information should not be lost and should be easy to figure out, in UTC. Figuring out the conversion of some other time/date is a solved problem.
There should never be a question of when some work happened if you're following sane practices.
I do huge ETL projects that live or die on the accuracy of our timing data. Give me date(time)s in UTC and I'm happy. Anything else? Pain.
Of course we knew who did what and when, that was not the problem and I never said it was. But the start date+time they entered for recurring series was converted to UTC on the client before it was sent to the server. So when it was decided that what they really wanted wasn't to run it "every 24 hours after the start date+time" but to run it on the exact time they entered every day, starting on the day they entered, the information about the exact time they had entered was gone.", all we had was that time converted to UTC. This meant we could not deduce the original time zone in which it was entered and thus could not deduce which DST schedule it should follow to keep it running at the same time every day.
The answer to the question "when did some work happen" is an instant and I specifically said that instants should be stored in UTC. I'm talking about storing recurrent schedules, i.e. information about when an (potentially infinite) series of future events should take place. And that information is not a series of instants and can't be stored as UTC timestamps because it's potentially infinite, subject to future reconfiguration, subject to future timezone offset changes, and must be preserved in a format that allows it to be viewed and edited (Thursdays odd weeks at 10:00 is not easy to glean from a series of pre-calculated numbers).
[deleted]
Yes, agreed - my iPhone alarm at 6AM for my workout should trigger whether I'm in Florida or California, and does.
Doesn't this become completely unglued once you've got people in different time zones, with different DST settings, using the same calendar?
If I have a meeting every day at 10am local time with someone in Arizona, their meeting suddenly jumps by an hour when my time changes. (Similarly, if it's my Arizonian colleagues who set the meeting to be at 10am daily, I'm suddenly meeting them at 11am my time once spring hits.)
If I have a meeting every day at 10am local time with someone in Arizona, their meeting suddenly jumps by an hour when my time changes. (Similarly, if it's my Arizonian colleagues who set the meeting to be at 10am daily, I'm suddenly meeting them at 11am my time once spring hits.)
Indeed, but this is representative of a very real change in the relationship between your clocks. The only way to keep this non-surprising is to make the time zone the schedule runs in explicit to the user. If somebody in a different zone edits the schedule it has to remain in the original time zone unless they explicitly change it. You do all your scheduling calculations in the schedule time zone and then convert it to an instant in time, likely expressed as a UTC time, when you need to calculate how long your computer should wait until the next event.
(Date)Times stored in the schedule (start times, end times, trigger times, clock times) are not instants in UTC, they are time specification in a calendar system which can only be interpreted as specific instants in the context of a particular time zone. Which is stored explicitly and separately from the calendar date-times.
This crucial separation of a time specifications in a calendar system from instants in time is one thing which many date/time libraries get wrong and which Noda-/JodaTime gets right.
(Date)Times stored in the schedule (start times, end times, trigger times, clock times) are not instants in UTC, they are time specification in a calendar system which can only be interpreted as specific instants in the context of a particular time zone. Which is stored explicitly and separately from the calendar date-times.
This crucial separation of a time specifications in a calendar system from instants in time is one thing which many date/time libraries get wrong and which Noda-/JodaTime gets right.
Yes, and that's exactly what happens in practice in every calendar system out there. Usually event creator's timezone takes precedence, and for them the calendar stays the same, while other parties have their events suddenly jump by an hour.
It's that 'unambiguous' part that is the problem. When you convert scheduled times into UTC you are making them ambiguous.
Heartily agree!
Makes a classic "falsehoods programmers believe about time" mistake.
The example (which is not so uncommon) that an appointment is set in timezone X, author of the service storing the appointment helpfully converts to UTC, timezone of the originating country changes and then the "return" from UTC to source timezone is incorrect, because now UTC-0600 is actually UTC-0700.
In short, any conversion that reduces precision is flawed. Dropping timezones after normalizing them to a canonical format makes it impossible to restore the conversion.
The example (which is not so uncommon) that an appointment is set in timezone X, author of the service storing the appointment helpfully converts to UTC, timezone of the originating country changes and then the "return" from UTC to source timezone is incorrect, because now UTC-0600 is actually UTC-0700.
In short, any conversion that reduces precision is flawed. Dropping timezones after normalizing them to a canonical format makes it impossible to restore the conversion.
Dropping timezones after normalizing them to a canonical
format makes it impossible to restore the conversion.
Normalizing TimeZones converts them to ZULU/GMT. You can de-normalize ZULU/GMT to any timezone easily and well within the UTC standard by converting them back to the local timezone. No data is lost, and the standard makes this process transparent.
Actually converting from Zulu to Local should always be done on the client side as their locality settings _should_ include a timezone.
This presumes, of course, that you can ALWAYS know in advance what UTC will be.
In practice there are edge-cases that you can't account for when the theory meets the practice of the messy, real world.
For example, last year Turkey decided, with a couple of days notice, to delay the daylight savings switch [1]. If you did things in UTC, you would have no idea which of your dates was now wrong, which ones that needed to be fixed.
Storing UTC might be useful in its own right, but if you're looking for correctness, you need to store the details and not discard the extra information.
[1] http://www.bbc.co.uk/news/world-europe-34631326
In practice there are edge-cases that you can't account for when the theory meets the practice of the messy, real world.
For example, last year Turkey decided, with a couple of days notice, to delay the daylight savings switch [1]. If you did things in UTC, you would have no idea which of your dates was now wrong, which ones that needed to be fixed.
Storing UTC might be useful in its own right, but if you're looking for correctness, you need to store the details and not discard the extra information.
[1] http://www.bbc.co.uk/news/world-europe-34631326
I have to ask...
What time encoding format _can_ deal with changes like this? Are their libraries that update often enough?
Also provided the Host OS had the proper time offset the UTC conversion would work fine, even for cases of fractional-hour timezones. So this can would be covered if the client updated their TZ locality.
What time encoding format _can_ deal with changes like this? Are their libraries that update often enough?
Also provided the Host OS had the proper time offset the UTC conversion would work fine, even for cases of fractional-hour timezones. So this can would be covered if the client updated their TZ locality.
Many libraries use the OS-wide timezone databases (which generally are based on https://www.iana.org/time-zones), so as long as you update your OS regularly (or explicitly when notified of a timezone change) you should always be up-to-date. The proper way to store (edit: future) times attached to a location is thus to keep a local timestamp with a named timezone (e.g. "Europe/London"), not an offset.
Local time plus time zone is not a perfect solution either. Times stored only as local time are not canonical. With DST some hours happen twice. You can never find out in retrospect which moment a local time refers to if it falls inside one of those magic hours.
If the user entered a local time it was always ambiguous. Converting it to UTC only hides the ambiguity.
I'm not sure I buy your example. If you save the time in the local time, then the time zone changes, the stored date will now be wrong. If you convert to UTC, and update your time zone tables when the time zone changes, then when you convert back the time will be correct.
I think you're missing the point of the example. When I make plans to meet someone "at 10AM Central time on Friday", I don't mean "let's meet at UTC epoch 1489762800". I mean "let's meet when the clock says 10:00". If the government suddenly decided to cancel daylight saving time on Thursday, then our meeting would still happen at 10:00 on Friday, even though in absolute (UTC) terms it would be an hour earlier.
A calendar appointment isn't identified by a UTC instant; it's identified by a predicate.
A calendar appointment isn't identified by a UTC instant; it's identified by a predicate.
Another example is recurring events. Let's say I have a meeting every Friday at 4PM Central in Chicago. That's UTC 11:00 now, but it was UTC 10:00 last week.
Which also makes it really interesting for people in other time zones. E.g. my recurring meetings, defined in eastern time zone, are the same for me this week as last week (thank goodness) -- but they've moved by an hour for people in other countries who are also in the meetings.
Yes, all of the following are possible needs:
(1) event scheduled for 7:00pm local time in a certain place on a certain date, that needs to be at that local time regardless of whether the government changes to time zone of that locality, or changes DST rules.
(2) event at 7:00pm Central Time ona certain date, that needs to occur at that time in the regardless of DST date changes.
(3) event at 7:00pm CST, which has a fixed offset from UTC.
(1) event scheduled for 7:00pm local time in a certain place on a certain date, that needs to be at that local time regardless of whether the government changes to time zone of that locality, or changes DST rules.
(2) event at 7:00pm Central Time ona certain date, that needs to occur at that time in the regardless of DST date changes.
(3) event at 7:00pm CST, which has a fixed offset from UTC.
Unless you are meeting up to watch the eclipse...
Ha, fair point!
But it's only a small step farther to allow scheduling an appointment relative to an arbitrary time zone, instead of the one you happen to currently be in. Which naturally includes UTC, for situations where astronomical time is what you really care about.
But it's only a small step farther to allow scheduling an appointment relative to an arbitrary time zone, instead of the one you happen to currently be in. Which naturally includes UTC, for situations where astronomical time is what you really care about.
I think the point is that if you store local time, and have a decently maintained time zone handling library, the library will adjust correctly for before/after the switch, but only if you store in the local time.
Of course it can't happen by magic, and you rely on the authors of the timezone libraries (a thankless task in itself), but if you remove a dimension of your data, nobody can ever help you.
Of course it can't happen by magic, and you rely on the authors of the timezone libraries (a thankless task in itself), but if you remove a dimension of your data, nobody can ever help you.
Summary:
Don't let the title throw you off, UTC is fine. Just be aware that when you convert the UTC time that you store back to local time, the conversion might be off depending on where you convert it. If your API is hosted on a server in Amsterdam and your users in Japan, that won't work. If your app is run on a tablet bought a few years ago, it will have an old timezone database. As the article highlights:
> Do date/time transformation on the server side, where you can control it.
Don't let the title throw you off, UTC is fine. Just be aware that when you convert the UTC time that you store back to local time, the conversion might be off depending on where you convert it. If your API is hosted on a server in Amsterdam and your users in Japan, that won't work. If your app is run on a tablet bought a few years ago, it will have an old timezone database. As the article highlights:
> Do date/time transformation on the server side, where you can control it.
It's more than that. If the daylight saving laws change in the zone you're interested in, the round trip from local to UTC to local again can be off even if your database is up to date and your timezones are correct. Converting from local to UTC using the old rule then UTC to local with the new rule will really mess you up.
The article offers no solution to that though.
> So, you had everything right, but result is wrong. Life is unfair. Deal with it.
It mentions a workaround, but not a solution.
> So, you had everything right, but result is wrong. Life is unfair. Deal with it.
It mentions a workaround, but not a solution.
The solution is right there in the next paragraph. Realize when you need to store local times instead of UTC.
There's no cookie cutter solution, you need to think about your particular problem and do what's best.
There's no cookie cutter solution, you need to think about your particular problem and do what's best.
IIRC, for the first few years, Facebook stored all event times in "local time", but with the Pacific time zone.
So if your event was in 3 PM Eastern Time, it would be written to the database as 3 PM Pacific Time.
Did it result in bugs? Yeah, sure. Should it have been fixed to use UTC? Of course. But it wasn't the end of the world -- people lived with it for years and it worked well enough. (Engineers grumbled about the code, of course.)
So if your event was in 3 PM Eastern Time, it would be written to the database as 3 PM Pacific Time.
Did it result in bugs? Yeah, sure. Should it have been fixed to use UTC? Of course. But it wasn't the end of the world -- people lived with it for years and it worked well enough. (Engineers grumbled about the code, of course.)
A simple concept for no-timezone time: instead of using a number for hours, use some city (perhaps first one/two letters of the name) where it is noon at that time. E.g. this post is around 15:00 PST in such system will be just "samoa". 12:00 GMT will be "greenwich", 16:00 GMT - "moscow", e.t.c.
Does not solve the problem for local time (stores in one country will be open from moscow to greenwich, in other - samoa to tokyo), but helps to put global time in perspective. E.g. if its berlin, you can guess, that folks in Berlin have around 5 hours more of work time, and folks in Moscow will be done in 4, and Japan is enjoying nice evening already.
Does not solve the problem for local time (stores in one country will be open from moscow to greenwich, in other - samoa to tokyo), but helps to put global time in perspective. E.g. if its berlin, you can guess, that folks in Berlin have around 5 hours more of work time, and folks in Moscow will be done in 4, and Japan is enjoying nice evening already.
>So, as an experienced programmer you always use UTC to store time. And you are right!
Umm, no, as an experience programmer I don't touch UTC until I hit something a human might read at this moment, otherwise I ship unix timestamps. I'm not that insane.
The UI which is currently displayed has the best information available to use the correct timezone, calendar, font, notation format and language to display time.
Not hitting frontend but needs a human-readable timestamp for insane reasons? UTC. All databases are UTC, all servers are UTC.
I'm not gonna touch time unless absolutely necessary and if I'm going to do it, I'm going to do it with a long pole and acid-resistant gloves on.
Umm, no, as an experience programmer I don't touch UTC until I hit something a human might read at this moment, otherwise I ship unix timestamps. I'm not that insane.
The UI which is currently displayed has the best information available to use the correct timezone, calendar, font, notation format and language to display time.
Not hitting frontend but needs a human-readable timestamp for insane reasons? UTC. All databases are UTC, all servers are UTC.
I'm not gonna touch time unless absolutely necessary and if I'm going to do it, I'm going to do it with a long pole and acid-resistant gloves on.
In your database always store time as seconds since the Epoch (or milliseconds since the Epoch if you need more precision). Forget about SQL date/time types, they are useless. Then convert to "civilian format" only in the presentation layer. You can't go wrong with that.
Seconds since the Epoch is unambiguous, eschews time zones, daylight savings time and other man-made concepts (it makes sense to Martians too). Plus it only requires an INTEGER type in the database which every RDBMS provides (so is migration safe). You also create demand for your future colleagues (or future self) around 2038. Or you can use BIGINT if that bothers you.
Seconds since the Epoch is unambiguous, eschews time zones, daylight savings time and other man-made concepts (it makes sense to Martians too). Plus it only requires an INTEGER type in the database which every RDBMS provides (so is migration safe). You also create demand for your future colleagues (or future self) around 2038. Or you can use BIGINT if that bothers you.
As others have pointed out, you can go wrong with that. If you don't store the local time and timezone, then your integer becomes meaningless whenever the DST rules change, as you can no longer tell if your conversion back to local time corresponds to the initial conversion from local time.
Seconds since Epoch is always unambiguous, but you can't always unambiguously convert a time to seconds since Epoch. If I set my alarm clock to 7 AM while I am in EST and you save that as 11 AM UTC, then I travel to somewhere that is in CST that 7 AM is now 12 PM UTC and the time you saved is now wrong.
A lot of online time calculators don't properly account for leap seconds. For example, http://billionbirthday.com/ doesn't (of course, it can't account for yet-to-be-announced leap seconds, but it could include currently known leap-seconds). Wolfram Alpha doesn't seem to either (at least, it doesn't have the most recent leap second).
I only know this because I work on a balloon experiment and some of the quantities our GPS reports are not in UTC Time (so don't have leap second corrections).
I only know this because I work on a balloon experiment and some of the quantities our GPS reports are not in UTC Time (so don't have leap second corrections).
I deal with this, we monitor various sensor data, and for the most UTC is never what's wanted, except for exporting and importing, but even then, I've seen people want/give things in standard time, or even DLS time. Certain things naturally need to be in local time like with water measuring over night usage looking for leaks. So things are recorded with datetimeoffsets so you can know when something occured locally to the sensor, as well as being able to translate to UTC, or the local time as it is now, or the local time of the viewer of the data
I wrote this somewhat thorough post about time, date, events, and timezones in response to a stackoverflow question once: http://stackoverflow.com/questions/2532729/daylight-saving-t...
It covers repeating events, historical accuracy, ranges, events that are fixed in localtime but variable in UTC, and more.
TL;DR: Always store UTC datetime + TimeZone, sometimes you need to store a little more information.
It covers repeating events, historical accuracy, ranges, events that are fixed in localtime but variable in UTC, and more.
TL;DR: Always store UTC datetime + TimeZone, sometimes you need to store a little more information.
Sadly, there's a lot more to it:
http://www.quirksmode.org/blog/archives/2009/04/making_time_...
http://www.quirksmode.org/blog/archives/2009/04/making_time_...
Even UTC can be ambiguous when it comes to leap seconds. I don't know any major DBMS that can correctly store a leap second event in a standard datetime column. Perhaps the answer is to standardize on miliseconds since 1958 (19580101T000000Z) when TAI began.
Look I know TZ's and dates are hard, I work with them quite regularly, I have written code to handle things like recurring items, and I have written a lot of code around knowing what TZ to show a date in to an end user (in my case you have to deal with things happening in a participant's TZ, their supervisor's TZ, our call center's TZ, and whatever the TZ is of the browser the end user is accessing the site from, all of which could be different). A bad UTC time due to mismatched timezone databases is by far the least of my worries, in fact it's not even on my radar nor do I plan to add it to it. You HAVE to standardize, period. UTC makes the most sense (epoch timestamps if you can get away with it) and it's up to the client to format that however they want (moment.js is your friend if you are a web dev). Yes, keep your TZ databases up to date and know which DB your software is using but use UTC. Anything else is a recipe for disaster.