How to save datetimes for future events(creativedeletion.com)
creativedeletion.com
How to save datetimes for future events
http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html
95 comments
Spot on. You don't need the UTC at all to be stored. What matters is the local time at the venue.
If its a virtual meeting (eg. over Skype) with participants from multiple time zones, it is safe to assume always one time zone will take precedence over others and time should be saved with respect to that.
If its a natural event like a solar eclipse, it can be saved in UTC.
There is always one time zone in which the event won't be adjusted. Just use that.
If its a virtual meeting (eg. over Skype) with participants from multiple time zones, it is safe to assume always one time zone will take precedence over others and time should be saved with respect to that.
If its a natural event like a solar eclipse, it can be saved in UTC.
There is always one time zone in which the event won't be adjusted. Just use that.
"There is always one time zone in which the event won't be adjusted. Just use that."
I agree with everything except I would modify that statement to be "There is always one location in the which the event won't be adjusted. Just use that."
It seems like people are assuming time zone is fixed when it can change. The fixed point is the location which is used to determine the time zone.
Natural events are trickier because they have different assumptions than human created events. UTC is probably best for those.
I agree with everything except I would modify that statement to be "There is always one location in the which the event won't be adjusted. Just use that."
It seems like people are assuming time zone is fixed when it can change. The fixed point is the location which is used to determine the time zone.
Natural events are trickier because they have different assumptions than human created events. UTC is probably best for those.
You're correct, the relation between time zones and locations isn't fixed. Unfortunately the time zone databases don't have enough granularity, you're forced to choose the nearest location that shares your current time zone.
I do love picking Minneapolis or Chicago when I want a ND location (look at North Dakota in the TZ database, we have some funky stuff going on). Looks like this question has some API for that http://stackoverflow.com/questions/16086962/how-to-get-a-tim...
No no no--you will add a lot of hairball code if you persist in not UTC.
For the virtual meeting, you just localize the time as they query it--this will make your life vastly simpler when, for example, querying over an interval or what have you.
UTC is the right answer.
For the virtual meeting, you just localize the time as they query it--this will make your life vastly simpler when, for example, querying over an interval or what have you.
UTC is the right answer.
Did you read the article? A specific case was presented where persisting in UTC simply won't work.
I still believe that UTC is useful as an intermediary, and persisting in UTC can work if all your times are in the past and all your conversions are done with an up-to-date rule list.
I still believe that UTC is useful as an intermediary, and persisting in UTC can work if all your times are in the past and all your conversions are done with an up-to-date rule list.
Yeah, read the article. Considering new house rule of UTC for present and past, wall time (?) for future.
Person I was responding to gave bad reasoning ("Pick the timezone that matters most") instead of echoing what the article said.
Person I was responding to gave bad reasoning ("Pick the timezone that matters most") instead of echoing what the article said.
The problem with your solution is that while it solves the client-side of the equation, it creates a nightmare on the server-side. Imagine I need to send a reminder email to people an hour before their event. I now need to convert every saved time to the time on the server to know when to send that email. And I can't efficiently query the store of events since they're not stored in a uniform time format.
I'd rather handle edge cases like the story covers explicitly rather than have to deal with all the problems that result from storing times in a heterogenous fashion. I'd rather move the burden for this to the timezone libraries. What's being pointed out is that timezone conversion isn't a two-input function that takes a UTC time and a destination timezone. It's actually a 3-input function that takes those two arguments and an additional timestamp which is the date when the conversion should take place.
I'd rather handle edge cases like the story covers explicitly rather than have to deal with all the problems that result from storing times in a heterogenous fashion. I'd rather move the burden for this to the timezone libraries. What's being pointed out is that timezone conversion isn't a two-input function that takes a UTC time and a destination timezone. It's actually a 3-input function that takes those two arguments and an additional timestamp which is the date when the conversion should take place.
"it creates a nightmare on the server-side"
Actually, last time I used something like this the queries aren't really that bad on a server (to write or execute). Its a fairly easy set of relational queries (build current time at location table -> events) and I would rather take the time on the server versus the client. This is the kind of thing a relation database does quite well and user expectations must be met.
Actually, last time I used something like this the queries aren't really that bad on a server (to write or execute). Its a fairly easy set of relational queries (build current time at location table -> events) and I would rather take the time on the server versus the client. This is the kind of thing a relation database does quite well and user expectations must be met.
I have a feeling that you're oversimplifying it. You will have events/dates on the table which are even for a single timezone have multiple offset like before/after DST. Essentially you will have to store the whole timezone rule data in some table and dynamically query based on the time value of the date, location and corresponding tz rule. I believe it would be nightmare of a query. But even that maybe possible if you have to basically do this for one single event table and few queries. Try to do this kind of query for 100s of tables and thousands of date type of column and see how quickly it spirals out of control.
I think your over complicating the query. You split the location -> time zone -> offset into one query to a temp table (doesn't need to run each time - very easy to predicate needed updates) then do the local time / location part. Its really not that hard.
I should point out I'm in the SQL / Stored Procedure camp and not talking using some object mapping solution. That might get problematic.
I should point out I'm in the SQL / Stored Procedure camp and not talking using some object mapping solution. That might get problematic.
Not sure I understand the temp table part. My point was that you are always calculating it dynamically. So if you have two dates in location x, timezone y and you save the rows
time1, x = time1, ytimezone, yoffset
time2, x = time2, ytimezone, yoffset
but given the timezone rule change time1, ytimezone has an offset of z1, time2, ytimezone has z2 offset. Now you have to compare the 'time' against the validity period of each offset dynamically in the query. You can I guess get away with also storing the UTC additionally and schedule recalculate on timezone rule change.
But even then the ambiguity of which timezone to actually apply remains as mentioned in other comments.
time1, x = time1, ytimezone, yoffset
time2, x = time2, ytimezone, yoffset
but given the timezone rule change time1, ytimezone has an offset of z1, time2, ytimezone has z2 offset. Now you have to compare the 'time' against the validity period of each offset dynamically in the query. You can I guess get away with also storing the UTC additionally and schedule recalculate on timezone rule change.
But even then the ambiguity of which timezone to actually apply remains as mentioned in other comments.
"My point was that you are always calculating it dynamically."
My point is that you aren't. Location -> Time Zone -> Offset is something that only has to be updated infrequently. The results are then stored in a temp or work table (whatever optimizes better). Now I have a table with (Location, Local Event Time) which I can do a range on the offset query to get the Local Times. I would write the schema and queries, but this depends on which database you use. I've done this on Sybase 12.5 and it worked just fine.
"But even then the ambiguity of which timezone to actually apply remains as mentioned in other comments."
No, I have the wanted local time at a location.
My point is that you aren't. Location -> Time Zone -> Offset is something that only has to be updated infrequently. The results are then stored in a temp or work table (whatever optimizes better). Now I have a table with (Location, Local Event Time) which I can do a range on the offset query to get the Local Times. I would write the schema and queries, but this depends on which database you use. I've done this on Sybase 12.5 and it worked just fine.
"But even then the ambiguity of which timezone to actually apply remains as mentioned in other comments."
No, I have the wanted local time at a location.
That's one way to handle it, but it still doesn't perform nearly as well simple greater/less/between where clauses, especially when you hit really large tables where you're going to want to index date values.
Since it's a fairly easy fix to update the way you do timezone conversions, it seems easier to put the logic there, where unit testing is easy, rather than in temp tables, stored procedures and query logic where testing is difficult and you've got state that needs to be carefully managed.
Since it's a fairly easy fix to update the way you do timezone conversions, it seems easier to put the logic there, where unit testing is easy, rather than in temp tables, stored procedures and query logic where testing is difficult and you've got state that needs to be carefully managed.
"but it still doesn't perform nearly as well simple greater/less/between where clauses"
I would have to write it, but I think the performance would be fine on a large dataset.
I have found that stored procedure and queries are quite easy to unit test. Particularly in this scenario where you can build simple input data and get out specific row(s). It is actually a lot easier than testing some Classes since you need to mock up sets of objects. With stored procedures and queries you can test the code in a harness without mocking up anything.
I would have to write it, but I think the performance would be fine on a large dataset.
I have found that stored procedure and queries are quite easy to unit test. Particularly in this scenario where you can build simple input data and get out specific row(s). It is actually a lot easier than testing some Classes since you need to mock up sets of objects. With stored procedures and queries you can test the code in a harness without mocking up anything.
I think it would be impossible to get good performance using normal (one dimensional) indices.
The problem is, when your time-zone information changes, you'd need to recalculate all your materialized views. Most popular databases lock the index/materialized view when it is being rebuild, blocking all access. When your tables are in the order of 1B rows, you can't really afford that.
The problem is, when your time-zone information changes, you'd need to recalculate all your materialized views. Most popular databases lock the index/materialized view when it is being rebuild, blocking all access. When your tables are in the order of 1B rows, you can't really afford that.
All too often, [Location] is something like a conference call, and is in fact in multiple different countries with different time zones and different rules. Deciding which location is the "primary" location should zone rules change is a social problem; whomever the other parties do not want to inconvenience the most wins.
[Location] -> [Time Zone] isn't quite right either; you need a [Location, Date-Range] -> [Time Zone] table.
For sure, UTC is a beguiling trap for a programmer-oriented view of the world.
[Location] -> [Time Zone] isn't quite right either; you need a [Location, Date-Range] -> [Time Zone] table.
For sure, UTC is a beguiling trap for a programmer-oriented view of the world.
Someone scheduled the conference call an that person would determine the location (or I will log in at X local time on Y date which should be attached to the event noticed and then figured out by my calendar).
Well, to get from [Location] -> [Time Zone] is a group of rules including the date.
UTC is nice for a lot of calculations, but it really isn't in a user's perspective.
Well, to get from [Location] -> [Time Zone] is a group of rules including the date.
UTC is nice for a lot of calculations, but it really isn't in a user's perspective.
DST is a whole stupid mess, as those doing business between the US and the UK have been painfully aware of over the past three weeks.
There is an offset for daylights savings time, so three weeks ago the time difference between NYC and London was 5 hours, but two weeks ago it became 4 hours when US changed to summer time, and it'll be back to 5 next week.
My 10 AM video conference from NYC with UK-based clients is on their schedule, so my meeting time changes to 11 AM in NYC.
However, the UK localization team reports to me, so my 9 AM meeting with them is still at 9AM for me. Their meeting time changes, not mine.
I schedule an in-person lunch meeting on Tuesday next week. I'm flying to London on Monday. The calender has to know my location has changed but that the lunch meeting is in-person, and to change the time appropriately.
The 'location' field could be set in the above cases to accommodate as a hack, but from a user experience perspective, the user's 'location' for a video conference is their conference room which is in their timezone, so 'location' isn't the best name.
All of the above can be handled with enough check-boxes and radio buttons in the user interface, but that makes your calender look ugly, complicated, and messy.
There is an offset for daylights savings time, so three weeks ago the time difference between NYC and London was 5 hours, but two weeks ago it became 4 hours when US changed to summer time, and it'll be back to 5 next week.
My 10 AM video conference from NYC with UK-based clients is on their schedule, so my meeting time changes to 11 AM in NYC.
However, the UK localization team reports to me, so my 9 AM meeting with them is still at 9AM for me. Their meeting time changes, not mine.
I schedule an in-person lunch meeting on Tuesday next week. I'm flying to London on Monday. The calender has to know my location has changed but that the lunch meeting is in-person, and to change the time appropriately.
The 'location' field could be set in the above cases to accommodate as a hack, but from a user experience perspective, the user's 'location' for a video conference is their conference room which is in their timezone, so 'location' isn't the best name.
All of the above can be handled with enough check-boxes and radio buttons in the user interface, but that makes your calender look ugly, complicated, and messy.
This is actually one of the fun problems because you have to be clear whose perspective and location is the important one.
"My 10 AM video conference from NYC with UK-based clients is on their schedule, so my meeting time changes to 11 AM in NYC"
The important location is the UK and the time on their schedule. You get floated to the proper local time.
"I schedule an in-person lunch meeting on Tuesday next week. I'm flying to London on Monday. The calender has to know my location has changed but that the lunch meeting is in-person, and to change the time appropriately."
The calendar has to know that the meeting is in London.
"The 'location' field could be set in the above cases to accommodate as a hack, but from a user experience perspective, the user's 'location' for a video conference is their conference room which is in their timezone, so 'location' isn't the best name."
Yeah, when talking to other programmers, location is fine, and location is fine for a lot of uses, but it does get murky on the communication aspect over the wire. Although the person scheduling it would probably be the location determinate for the appointment and the event notice would need to put that on the other participants calendar with the scheduler's location.
"All of the above can be handled with enough check-boxes and radio buttons in the user interface, but that makes your calender look ugly, complicated, and messy."
I don't think it actually would be that bad. It's more a rules engine thing. I think it might not be that bad. We just get mired in the complication when options aren't really that many from a user's point of view.
"My 10 AM video conference from NYC with UK-based clients is on their schedule, so my meeting time changes to 11 AM in NYC"
The important location is the UK and the time on their schedule. You get floated to the proper local time.
"I schedule an in-person lunch meeting on Tuesday next week. I'm flying to London on Monday. The calender has to know my location has changed but that the lunch meeting is in-person, and to change the time appropriately."
The calendar has to know that the meeting is in London.
"The 'location' field could be set in the above cases to accommodate as a hack, but from a user experience perspective, the user's 'location' for a video conference is their conference room which is in their timezone, so 'location' isn't the best name."
Yeah, when talking to other programmers, location is fine, and location is fine for a lot of uses, but it does get murky on the communication aspect over the wire. Although the person scheduling it would probably be the location determinate for the appointment and the event notice would need to put that on the other participants calendar with the scheduler's location.
"All of the above can be handled with enough check-boxes and radio buttons in the user interface, but that makes your calender look ugly, complicated, and messy."
I don't think it actually would be that bad. It's more a rules engine thing. I think it might not be that bad. We just get mired in the complication when options aren't really that many from a user's point of view.
Options are change the time or not.
Yet, your rules engine will be kilobytes long, and catch the users by surprise about half the time - because, let's face it, this is completely context sensitive and thus can not be predicted by your software.
Yet, your rules engine will be kilobytes long, and catch the users by surprise about half the time - because, let's face it, this is completely context sensitive and thus can not be predicted by your software.
This comes from mismatched expectations of what users are storing. Often they think, or assume without thinking, that they are storing a localized time. For ease of implementation, or just because the developer doesn't know any better, we often store in absolute time (UTC) with an offset and call it done.
The problem is, nobody really thinks about this stuff. It's easy to pick out an error case and say "The users expect local (relative) time, we need to deliver that." That's not always right. There are plenty of cases where the user really wants absolute time. Fro example, when scheduling an international meeting where the other side is dictating the time. When timing a broadcast from another country. Really, any time when you need to deal with events scheduled outside your local time, or have components (or people) that run on times that are not local to you.
It gets trickier. What if you are tracking that international event, and that locality changes their time rules? For example, what if I'm in the US, and I wanted to schedule a time to watch a sporting event in Chile, and I scheduled it before the changed mentioned in the article? Whether I store it as a local time or an absolute time, it's wrong.
Really, you need three pieces of information. The UTC time representation, the timezone offset it applies to, and whether it should be treated as an absolute or localized time.
Edit: Corrected what type of stored offset needs to be kept
The problem is, nobody really thinks about this stuff. It's easy to pick out an error case and say "The users expect local (relative) time, we need to deliver that." That's not always right. There are plenty of cases where the user really wants absolute time. Fro example, when scheduling an international meeting where the other side is dictating the time. When timing a broadcast from another country. Really, any time when you need to deal with events scheduled outside your local time, or have components (or people) that run on times that are not local to you.
It gets trickier. What if you are tracking that international event, and that locality changes their time rules? For example, what if I'm in the US, and I wanted to schedule a time to watch a sporting event in Chile, and I scheduled it before the changed mentioned in the article? Whether I store it as a local time or an absolute time, it's wrong.
Really, you need three pieces of information. The UTC time representation, the timezone offset it applies to, and whether it should be treated as an absolute or localized time.
Edit: Corrected what type of stored offset needs to be kept
If you stored it as your local time, it would be wrong. If you stored it as Chile's local time, it would be correct. The trick is to simply record the appropriate time zone along with the timestamp.
There is still the problem of handling the effects of DST on repeating events. You have to know the base UTC offset and any additional seasonal offset for the local zone. Then it becomes an issue if you travel to a different locality that doesn't observe DST.
Absolutely correct. I was thinking that, even if we words said something else. :/ I've updated the last paragraph to reflect that.
I never realised this problem despite having to work with some datetime problems before. But as you said the solution is not full proof. The biggest problem with your (what I believe is theoretically foolproof solution) is that it will for practical consideration. Most users will be unable to understand the complexity of the issue to properly choose whether to set the absolute/localized time or even which offset to apply in a lot cases. In case of some events one might not even know or find it hard to decide which timezone should be the primary timezone for the event or it might even change.
I think in terms of user understanding is to use one timezone (also shown to the user) to generate the UTC time and later if any changes happen to the timezone of the event or the corresponding user's timezone warn the user and ask for each event (maybe even in bulk) whether to
1. Keep the original walltime in the timezone (in case this is the deciding timezone) this will imply changes for users in other timezone.
And at this point some calculation figures out how to adjust the UTC.
2. Ignore the timezone change and keep the original offset.
3. Manually update.
I think in terms of user understanding is to use one timezone (also shown to the user) to generate the UTC time and later if any changes happen to the timezone of the event or the corresponding user's timezone warn the user and ask for each event (maybe even in bulk) whether to
1. Keep the original walltime in the timezone (in case this is the deciding timezone) this will imply changes for users in other timezone.
And at this point some calculation figures out how to adjust the UTC.
2. Ignore the timezone change and keep the original offset.
3. Manually update.
> There are plenty of cases where the user really wants absolute time. Fro example, when scheduling an international meeting where the other side is dictating the time.
Nope. Then you want local time _on the other party's side_, which has the same problematic relationship to 'absolute time'. I'm not sure there's any case (except scientists doing science) where the user would really want "absolute time" (and then we could ask _what_ absolute time, there isn't even neccesarily such a thing as One True Absolute Time).
> It gets trickier. What if you are tracking that international event, and that locality changes their time rules?
So, did you read the OP? That's in fact what the article is about?
Nope. Then you want local time _on the other party's side_, which has the same problematic relationship to 'absolute time'. I'm not sure there's any case (except scientists doing science) where the user would really want "absolute time" (and then we could ask _what_ absolute time, there isn't even neccesarily such a thing as One True Absolute Time).
> It gets trickier. What if you are tracking that international event, and that locality changes their time rules?
So, did you read the OP? That's in fact what the article is about?
Another solution is "floating time" which disregards timezones entirely. This is an option in iCalendar datetime formats.
In floating time, 10:00 in Chile is "10:00 in Chile". No timezone offset.
https://www.ietf.org/rfc/rfc2445.txt
https://scottworldblog.wordpress.com/2009/02/20/how-to-miss-...
In floating time, 10:00 in Chile is "10:00 in Chile". No timezone offset.
https://www.ietf.org/rfc/rfc2445.txt
https://scottworldblog.wordpress.com/2009/02/20/how-to-miss-...
There is a great (humorous) Computerphile video about this topic: "The Problem with Time & Timezones" [0]
[0] https://www.youtube.com/watch?v=-5wpm-gesOY
[0] https://www.youtube.com/watch?v=-5wpm-gesOY
Don't most time zone databases store the date of rule changes? http://en.wikipedia.org/wiki/Tz_database#File_formats
Yeah, this was always my understanding - time zones, DST participation and dates, etc all change more frequently than you'd expect, and time zone databases keep track of when things happen precisely for this reason. If that weren't happening, I think people would have found that many of their timestamps become obsolete / inaccurate relatively quickly.
That said, it does tie the stability of your datetimestamp to the stability of the tz database. I think that's a pretty good bet, but it's nice to be aware of where your abstractions can leak.
That said, it does tie the stability of your datetimestamp to the stability of the tz database. I think that's a pretty good bet, but it's nice to be aware of where your abstractions can leak.
That's what I thought too. Based on the discussion surrounding this example[0], I believe that's the case.
Still, date/time is a tricky subject to get right.
0. http://stackoverflow.com/questions/6841333/why-is-subtractin...
0. http://stackoverflow.com/questions/6841333/why-is-subtractin...
That doesn't help if you don't save the time zone rules that were in effect when you converted the original timestamp to UTC. The problem is that you've already converted to UTC using the wrong rules, so to make things right you need to unconvert then reconvert again. But there's no way to know which timestamps need correcting and which ones don't.
The databases keep track of the zone changes so you can use the proper rules for conversions in the past. They don't protect you from changes in the future.
The databases keep track of the zone changes so you can use the proper rules for conversions in the past. They don't protect you from changes in the future.
Yes. But in this case the timeline is something like this:
1) You arrange a meeting 2) Then, after you have arranged the meeting, politicians/bureaucrats decide to change the time zone rules 3) Your meeting takes place
At 1) the rules are different from at 2). At 1) you cannot see into the future and anticipate how the rules will be at 2), because you don't have a time machine. So you have to be ready for it by implementing in a way that is resistant to timezone rule changes. For instance like the described solution.
1) You arrange a meeting 2) Then, after you have arranged the meeting, politicians/bureaucrats decide to change the time zone rules 3) Your meeting takes place
At 1) the rules are different from at 2). At 1) you cannot see into the future and anticipate how the rules will be at 2), because you don't have a time machine. So you have to be ready for it by implementing in a way that is resistant to timezone rule changes. For instance like the described solution.
The prescribed solution does not solve for all use cases though.
Imagine if the meeting included a conference call to someone in Australia. A day before the meeting, the bureaucrats change the DST rule. The meeting invite for the Australian now has incorrect time. Storing as local time will work against events applicable across timezones.
The problem isn't with UTC, it is unexpected rule changes that the converting agent (software) did not predict. That logic will have to be coded in.
Imagine if the meeting included a conference call to someone in Australia. A day before the meeting, the bureaucrats change the DST rule. The meeting invite for the Australian now has incorrect time. Storing as local time will work against events applicable across timezones.
The problem isn't with UTC, it is unexpected rule changes that the converting agent (software) did not predict. That logic will have to be coded in.
Yeah, it seems like there's no general solution to this. If I put in '10AM in Chile' for grabbing coffee with a friend, I probably want it to stay in wall time no matter what happens to the offset.
If I'm making a note of a solar eclipse, I need it to stick with physical time, and if the offset changes the reported time changes.
If I'm scheduling a conference call to Australia, then when the offset changes, one of us is going to have to reschedule, and my calendar can't know which. (We might both have to reschedule, if neither of us can move it by a single hour.)
It might be able to say "you scheduled this at 10AM, but the DST rules changed, and that physical time is now 11AM wall time, what to do?" My Australian colleague isn't going to get that warning, unless her calendar knows that she's talking to someone in Chile, but as long as one of us knows about the problem, we should be okay. But I'm not sure I trust this to cover all bases.
If I'm making a note of a solar eclipse, I need it to stick with physical time, and if the offset changes the reported time changes.
If I'm scheduling a conference call to Australia, then when the offset changes, one of us is going to have to reschedule, and my calendar can't know which. (We might both have to reschedule, if neither of us can move it by a single hour.)
It might be able to say "you scheduled this at 10AM, but the DST rules changed, and that physical time is now 11AM wall time, what to do?" My Australian colleague isn't going to get that warning, unless her calendar knows that she's talking to someone in Chile, but as long as one of us knows about the problem, we should be okay. But I'm not sure I trust this to cover all bases.
Let's say the meeting is supposed to take place at 15:00 "wall time" in Sydney on March 30th. Someone changes the DST rules. The meeting still takes place at the specified time. Remember we specify the time at Australia. So far so good.
Usually the time from a new update is released at least a week before the change takes place. So your software might alert you about the time on the same day or the day before of the meeting. Or even half an hour before. Do the calculations at that time of the alert with the newest timezone data. And if the software is up to date with timezone database changes, users in other time zones will be alerted about the correct time. The calculation will be made before the meeting from 15:00 Sydney to whatever other time zone someone might be in.
Usually the time from a new update is released at least a week before the change takes place. So your software might alert you about the time on the same day or the day before of the meeting. Or even half an hour before. Do the calculations at that time of the alert with the newest timezone data. And if the software is up to date with timezone database changes, users in other time zones will be alerted about the correct time. The calculation will be made before the meeting from 15:00 Sydney to whatever other time zone someone might be in.
Why would the time for the Australian be incorrect? If the meeting organizer is in Chile, then the meeting time would be ruled by Chile's local clock. If the rule change affects the difference between Australia and Chile, then simply loading the updated rules on both sides should readjust things automatically - if the time was stored as Chile local, not UTC.
The fact that the meeting organizer in Chile does not imply that the key constraint for the meeting time was in Chile, rather than in the schedule of the Australian attendees.
(Of course, its actually possible that there were essential constraints on both sides, in which case, the problem is more difficult, or that the key constraint was that the call needed to immediately precede or follow an event in a timezone different from Australia or Chile.)
Really, determining the key intent here is hard, and there is no one rule that makes it painless in all cases. There are lots of different possible intents with timing, and there is no single general rule that handles all possible changes in local time rules between the time an event is scheduled and the time it occurs.
(Of course, its actually possible that there were essential constraints on both sides, in which case, the problem is more difficult, or that the key constraint was that the call needed to immediately precede or follow an event in a timezone different from Australia or Chile.)
Really, determining the key intent here is hard, and there is no one rule that makes it painless in all cases. There are lots of different possible intents with timing, and there is no single general rule that handles all possible changes in local time rules between the time an event is scheduled and the time it occurs.
If I enter "10:00 in Chile" in a calendar app I expect it to stay "10:00 in Chile". And not magically change to "11:00 in Chile". That is what the example was.
Would it be possible to avoid any ambiguity at all by saving the timezone rules themselves as separate most-previous and current versions within the application context itself?
Then when a new set of rules is detected (perhaps notification of updates at the OS level should be turned into a standardized publish-subscribe API to avoid too many applications polling for changes all the time, especially wasteful on battery-powered devices), move the current version to the previous version, scan for timezone rule differences, then scan your collection of records that use those timezones, parse them down to only those records affected by the new rules, and finally apply corrections to those records affected by the new rules.
Extending further, if this approach works, perhaps an OS-level, perhaps git-backed storage of versions of timezone rules could avoid duplication of effort. Combined with a publish-subscribe service, it can auto-prune itself based upon the list of subscribed applications and when the oldest subscriber last hit the version store.
Then when a new set of rules is detected (perhaps notification of updates at the OS level should be turned into a standardized publish-subscribe API to avoid too many applications polling for changes all the time, especially wasteful on battery-powered devices), move the current version to the previous version, scan for timezone rule differences, then scan your collection of records that use those timezones, parse them down to only those records affected by the new rules, and finally apply corrections to those records affected by the new rules.
Extending further, if this approach works, perhaps an OS-level, perhaps git-backed storage of versions of timezone rules could avoid duplication of effort. Combined with a publish-subscribe service, it can auto-prune itself based upon the list of subscribed applications and when the oldest subscriber last hit the version store.
This post describes examples of ambiguity and non-existing times: http://www.creativedeletion.com/2015/01/28/falsehoods-progra...
Note non-existing times mostly happen when you go from winter time to summer time. You set the clock ahead an hour at let's say 2:00 in the morning. So you go directly from 2:00 to 3:00. 2:30 does not exist.
So let's say that you plan a meeting at 2:30 in the morning in a country that does not use DST. Then they decide to use DST and set the clocks forward one hour at 2:00. Your meeting was supposed to take place at 2:30 local time, but that time no longer exists! As I see it, the right thing to do is to alert the user of the problem.
Note non-existing times mostly happen when you go from winter time to summer time. You set the clock ahead an hour at let's say 2:00 in the morning. So you go directly from 2:00 to 3:00. 2:30 does not exist.
So let's say that you plan a meeting at 2:30 in the morning in a country that does not use DST. Then they decide to use DST and set the clocks forward one hour at 2:00. Your meeting was supposed to take place at 2:30 local time, but that time no longer exists! As I see it, the right thing to do is to alert the user of the problem.
As pointed out in another post, the way timezone rule changes are stored actually does effectively what you are saying. For each time zone / locale, when there is a rule change, it is appended to the existing rules along with the date of the change. Example from the tzdata2015b file for the New_York time zone:
# Zone NAME GMTOFF RULES FORMAT [UNTIL]
Zone America/New_York -4:56:02 - LMT 1883 Nov 18 12:03:58
-5:00 US E%sT 1920
-5:00 NYC E%sT 1942
-5:00 US E%sT 1946
-5:00 NYC E%sT 1967
-5:00 US E%sTI believe this is currently done, i.e. timezone databases track not only the current timezone information but store the valid dates for which a set of timezone rules apply.
This would be necessary not only for the example provided in the link, but also for larger changes, like several years ago when the US extended DST hours, and several other countries followed suit. (i.e. DST now begins on the second Sunday of March, rather than later in April)
This would be necessary not only for the example provided in the link, but also for larger changes, like several years ago when the US extended DST hours, and several other countries followed suit. (i.e. DST now begins on the second Sunday of March, rather than later in April)
This is the right solution. You don't check for any changes every single time you read out a timestamp - that's just unnecessary overhead and removes the ability to write simple queries to search for timestamps within a certain range.
You store the timestamp, and a foreign key to the timezone as it was defined at the time the timestamp was inserted (orginal revision). You then schedule a sweep (every day, say) that simple detects which timezones have received an actual update, then select/update any rows which were created with the previous timezone entry. Not very difficult to implement, and generates the minimal amount of overhead possible.
The only exception is the one raised by laut regarding an event scheduled for say 02:30, and where 02:30 simply does not exist after a rules update. The sweep can detect these and in theory should alert the user to the inconsistency. An annoying edge case to be sure. :/
You store the timestamp, and a foreign key to the timezone as it was defined at the time the timestamp was inserted (orginal revision). You then schedule a sweep (every day, say) that simple detects which timezones have received an actual update, then select/update any rows which were created with the previous timezone entry. Not very difficult to implement, and generates the minimal amount of overhead possible.
The only exception is the one raised by laut regarding an event scheduled for say 02:30, and where 02:30 simply does not exist after a rules update. The sweep can detect these and in theory should alert the user to the inconsistency. An annoying edge case to be sure. :/
Am I missing something, or does their solution not actually solve their specific example problem case at all?
Saving local time plus offset is _exactly_ the same information as saving GMT, isn't it?
You'd really need to save local datetime plus location... which they don't seem to actually be recommending?
Of course, for actually triggering an alarm or whatever, you'd still need to plot this on the actual timeline somehow, and deal with periodically checking to see if it should be changed because of a DST or timezone rule change or whatever.
Saving local time plus offset is _exactly_ the same information as saving GMT, isn't it?
You'd really need to save local datetime plus location... which they don't seem to actually be recommending?
Of course, for actually triggering an alarm or whatever, you'd still need to plot this on the actual timeline somehow, and deal with periodically checking to see if it should be changed because of a DST or timezone rule change or whatever.
The important part is local time plus timezone.
The offset is there only for the very rare case that you save a time that you know is ambiguous when you save it. And when DST rules don't change. Usually it happens when you change from DST to non-DST in the autumn and a certain hour happens twice.
In the example, the offset will not be used. Only local time plus time zone.
edit: I have updated the article to explain this part.
The offset is there only for the very rare case that you save a time that you know is ambiguous when you save it. And when DST rules don't change. Usually it happens when you change from DST to non-DST in the autumn and a certain hour happens twice.
In the example, the offset will not be used. Only local time plus time zone.
edit: I have updated the article to explain this part.
This doesn't really seem to solve the problem, just makes some of the error modes less surprising to the user.
Surely the right approach to handling scheduling like this is (sticking with the calender example) a) initiating calender event is canonical (but may or may not be in the calenders local timezone) , but b) metadata about when the schedule was made and last updated is stored, and c) every future computation takes this metadata and looks up canonical (i.e. IANA) timezone/change information every time it touches the event, and d) a warning is given to any user, at any time their localization has a change in rules that might affect their apparent wall time, plus e) any changes in the localization of the initiating/owning calender trigger a warning for everyone.
Am I missing something? [edit: besides the way to format this list...]
(added - I should have been more clear that the server is only responsible for keeping track of the canonical event and updating clients if and when that changes. The clients will always be responsible for resolving locally apparent changes)
Surely the right approach to handling scheduling like this is (sticking with the calender example) a) initiating calender event is canonical (but may or may not be in the calenders local timezone) , but b) metadata about when the schedule was made and last updated is stored, and c) every future computation takes this metadata and looks up canonical (i.e. IANA) timezone/change information every time it touches the event, and d) a warning is given to any user, at any time their localization has a change in rules that might affect their apparent wall time, plus e) any changes in the localization of the initiating/owning calender trigger a warning for everyone.
Am I missing something? [edit: besides the way to format this list...]
(added - I should have been more clear that the server is only responsible for keeping track of the canonical event and updating clients if and when that changes. The clients will always be responsible for resolving locally apparent changes)
Yes, whether the location/context of the event is relative to you or some other location. I.e. Someone outside Chile creates an event to track the start of a football game in Chile before the change mentioned in the article.
What you really want is an absolute time, and a localized timezone that applies to the event (which may not be your own).
What you really want is an absolute time, and a localized timezone that applies to the event (which may not be your own).
Ah, I wasn't clear enough ... I was thinking in that case the canonical calender event would be encoded in time in Chile, even if the local calender was in Germany or whatever. Looking back that could have been written much better so I've added a parenthetical.
What about saving the present time (the datetime when the information is recorded) and the time offset as calculated at that moment? In theory then it doesn't matter what happens, you're still able to adjust. The only ambiguity I can think of is if leap (seconds|hours|days) are introduced, but surely you just add them on to the delta too?
Of course, this system fails when you suddenly travel anything approaching relativistic speeds, but I guess you could save your time delta as a velocity-time delta :)
Edit: I'm not actually advocating this as a solution, I just found it an interesting thought experiment.
Of course, this system fails when you suddenly travel anything approaching relativistic speeds, but I guess you could save your time delta as a velocity-time delta :)
Edit: I'm not actually advocating this as a solution, I just found it an interesting thought experiment.
Time offset by itself isn't sufficient to know if the rules for calculating that time offset have changed. You need to know the rules themselves, and the easiest way to record that is with a time zone.
It seems that the example provided is a poor example. Software systems compute based on some rules. If you change the rules, you must change the software. Are there other more meaningful examples?
Author here. When the timezone rules change, the software updates its timezone database. This is how it should be. You don't have to change the software, the software should be able to handle the changes to the timezone database.
The updates are available here: http://www.iana.org/time-zones So far there has been two new releases of the database in 2015. Last year there were about 10 changes.
The updates are available here: http://www.iana.org/time-zones So far there has been two new releases of the database in 2015. Last year there were about 10 changes.
sorry, thats what I meant by the software (change its ability to understand the rules). IANA link is helpful!
The example is a good one, you are just misunderstanding what the problem is. Here is the synopsis:
1. User makes an appointment for 11am local time when he meets his friend in Chile. Using current timezone rules (GMT-3), the program saves this in the database as 1400 UTC. 2. Chile decides to change the rules for their time zone. Software gets updated with new table. Now, the appointment is at 10am local time in Chile, since they are GMT-4 now (1400 UTC = 10am GMT-4)
1. User makes an appointment for 11am local time when he meets his friend in Chile. Using current timezone rules (GMT-3), the program saves this in the database as 1400 UTC. 2. Chile decides to change the rules for their time zone. Software gets updated with new table. Now, the appointment is at 10am local time in Chile, since they are GMT-4 now (1400 UTC = 10am GMT-4)
The example is perfect. It isn't enough to change the software, you must also change the existing data, because with the rule change it is now wrong. Or you can take the advice and store your data in a form that doesn't become obsolete when the rules change.
If you store it in UTC, it becomes obsolete when the rules change for that location. If you store it in the local time, it becomes obsolete for other timezones (think the timezone of the traveler before they leave). If you store both UTC and the local, then one (of the 2) becomes obsolete, but recoverable assuming you can scrub the data. However, you must know which is correct based on the rules change itself.
> Instead of saving the time in UTC along with the time zone, developers can save what the user expects us to save: the wall time. Ie. what the clock on the wall will say.
That's not at all foolproof, although it works for the case the author describes and probably works for most people most of the time. It wouldn't work if, for some reason, I'm entering a calendar event for something I know will happen exactly n hours from now.
That's not at all foolproof, although it works for the case the author describes and probably works for most people most of the time. It wouldn't work if, for some reason, I'm entering a calendar event for something I know will happen exactly n hours from now.
In that case, go ahead and convert to UTC and save UTC as the relevant zone. In no case is timestamp+zone less flexible than UTC timestamp by itself.
The article is about saving datetimes. Usually calendars use datetime too.
If you need to count down X hours you are not looking to save a datetime. Maybe use an egg timer or the countdown feature of a smartphone.
If you need to count down X hours you are not looking to save a datetime. Maybe use an egg timer or the countdown feature of a smartphone.
Unfortunately, there's an ambiguity here. Suppose that you're going to watch the Super Bowl with your friends in Chile. So you set the appointment for 1830 local time in Chile. Then Chile changes their timezone, and your local time is wrong since the actual event is based on Eastern time, and not Chile time.
You simply need to observe the rule that event times should be specified in the time zone of the event itself.
If the event is a meeting, then the meeting organizer will be in charge of setting the appropriate time zone.
If the event is a meeting, then the meeting organizer will be in charge of setting the appropriate time zone.
That doesn't sound simple for the user at all. If I'm meeting locally with friends to see the game/oscars/wtv, I don't know or care about the time in the original timezone. My local TV guide / newspaper shows it in local time, and that's what everyone in the meeting cares about, so why would I set it to a completely different TZ?
Admittedly setting a time zone different than your local zone is an edge case. 99% of the time your local zone is the correct default. It would come up if you're scheduling a meeting for your trip to a different zone, or setting your TV to record the football game being broadcast from Chile.
That is outside of the scope of the article. In this case you have to ask yourself. Do you want to save an appointment for 18:30 in Chile? Or for the Superbowl? You have to choose one.
If you really want to be sure tell your friends to use the timezone of the event. Maybe say "let's meet at 19:00 New York time" if the game starts at 20:00 in New York and you want to meet an hour before.
If you really want to be sure tell your friends to use the timezone of the event. Maybe say "let's meet at 19:00 New York time" if the game starts at 20:00 in New York and you want to meet an hour before.
I just did a project that implemented the same solution that author mentioned.
For those who need a web fron-tend implementation, I would like to recommend Moment Timezone (http://momentjs.com/timezone/).
For those who need a web fron-tend implementation, I would like to recommend Moment Timezone (http://momentjs.com/timezone/).
If I understand the objection here the problem isn't that the computer has the time wrong, it's that our understanding of time is wrong.
The computer still has the calendar entry at the right instant in time identified when the user input the entry.
The computer still has the calendar entry at the right instant in time identified when the user input the entry.
The problem is that the meeting time is defined in terms of the "wall time" in Chile.
So if you store the time in terms of UTC, then you are going to have problems when the relation between UTC and the Santiago/Chile timezone changes.
The computer in the example has the time wrong because it stored the time in UTC.
So if you store the time in terms of UTC, then you are going to have problems when the relation between UTC and the Santiago/Chile timezone changes.
The computer in the example has the time wrong because it stored the time in UTC.
Pegging against "wall time" in Chile causes it's own problems. What if the meeting is with a person in "Europe/Berlin" via Skype and the appointment is in a shared calendar. How would the new Berlin time be calculated for the unfortunate Berliner after the Chilean bureaucrats have had their fun?
The Berliners should have the meeting entered in their calendar as 10 AM Chilean time. At any given moment when the meeting time must be evaluated or displayed on their own calendars, it can be converted to European time using the current time zone conversion rules.
In the example in the blog post the meeting takes place in Chile, so Berlin time is irrelevant.
But let's say you are arranging a conference call between someone in Chile and someone in Berlin. When you arrange the meeting you have to define when the meeting takes place. So you have to choose a time zone along with a time for when the meeting starts. You could choose UTC, Berlin or Santiago. But you have to choose one.
But let's say you are arranging a conference call between someone in Chile and someone in Berlin. When you arrange the meeting you have to define when the meeting takes place. So you have to choose a time zone along with a time for when the meeting starts. You could choose UTC, Berlin or Santiago. But you have to choose one.
If it was done properly, the target date in the future should be based on the UTC offset it will have at that time in the future before being converted to UTC for storage. The error is to convert to UTC based on the current UTC offset instead of the "political" timezone that would take in to account proper daylight saving changes.
There are problems when dealing with timezones and daylight saving changes but the example is just a case where the implementation was too naïve.
There are problems when dealing with timezones and daylight saving changes but the example is just a case where the implementation was too naïve.
The problem is not naive software, the problem is that you can't predict the future. There will always be dates in the future that will be impacted by some rule change that hasn't been officially enacted yet.
Author here. Yes, the error was to save any other time than the local time where the meeting is supposed to take place.
The point of the post was to demonstrate that when storing future events that is supposed to happen in a certain timezone, save the local time (along with timezone) instead of converting to UTC.
The point of the post was to demonstrate that when storing future events that is supposed to happen in a certain timezone, save the local time (along with timezone) instead of converting to UTC.
Why store the UTC offset too, then? Couldn't you get that with the timezone? Then you've got the wall time for the meeting saved, and if you need to convert it to UTC for some reason, you could do it with the most current rules for the timezone.
If the offset becomes meaningless in the event of a rule change anyway, why save it when the most current rules will help you get the offset?
If the offset becomes meaningless in the event of a rule change anyway, why save it when the most current rules will help you get the offset?
Good question!
It is optional to store the UTC offset. The reason to store the UTC offset is to avoid ambiguity there might be when the timezone rules do not change.
If the timezone rules don't change and you happen to be storing a datetime that is during changing from DST to non-DST, you specify which specific time you are talking about. In autumn, clocks could be set back from 3:00 to 2:00. 2:30 happens twice. When you schedule the time you can ask the user "2:30 summer time or 2:30 winter time"?
So you only use the offset for anything in rare case there is an ambiguity (usually because of going off of DST), otherwise you just ignore it. In most cases even if the rules change like in the Chile example you would ignore it as well and use the offset provided by the timezone database.
It is optional to store the UTC offset. The reason to store the UTC offset is to avoid ambiguity there might be when the timezone rules do not change.
If the timezone rules don't change and you happen to be storing a datetime that is during changing from DST to non-DST, you specify which specific time you are talking about. In autumn, clocks could be set back from 3:00 to 2:00. 2:30 happens twice. When you schedule the time you can ask the user "2:30 summer time or 2:30 winter time"?
So you only use the offset for anything in rare case there is an ambiguity (usually because of going off of DST), otherwise you just ignore it. In most cases even if the rules change like in the Chile example you would ignore it as well and use the offset provided by the timezone database.
Well, it has it wrong, depending on what you are using it for. If you are putting in a calendar entry for the next predicted Lunar eclipse storing them in anything but UTC is wrong.
TAI would be better than UTC for the next Lunar eclipse in case you want to be exact to the second ;)
But yes, if you schedule the meeting in UTC, use UTC. The point is not to convert from non-UTC to UTC.
But yes, if you schedule the meeting in UTC, use UTC. The point is not to convert from non-UTC to UTC.
UTC just provides a labeling for TAI/TT seconds, so a count-of-UTC-seconds timestamp is identical to a count-of-TAI-seconds timestamp (assuming the same epoch). Both are different from a count-of-non-leap-UTC-seconds aka POSIX time.
Why not just use a unix timestamp and convert to the local time format of all users? Is that a poor solution for this? Most unix timestamp to Date converters already take DST into account.
This also fixes the "MM/DD/YYYY", "DD/MM/YYYY", and "YYYY/MM/DD" fiasco as you can rely that the users computer is configured to the correct timezone/localization settings.
This also fixes the "MM/DD/YYYY", "DD/MM/YYYY", and "YYYY/MM/DD" fiasco as you can rely that the users computer is configured to the correct timezone/localization settings.
Because the Unix timestamp is based on UTC and may not accurately capture the user's intent if the DST rules change in between when the timestamp is created and when the timestamp is read.
That is, suppose I schedule an event on 2016 April 1, at 10am in San Francisco, California. Under current DST rules, this translates into 2016 April 1, 5pm UTC or a Unix timestamp of 1459530000.
Now suppose DST is abolished in California between now and the event occurring. A Unix timestamp of 1459530000 would then correspond to 2016 April 1, at 9am in San Francisco, which is one hour too early.
Another way to think about this: You have to apply the DST conversion rules applicable at the time the event was created, NOT the (potentially different) rules at the time the event occurs. But maintaining different sets of conversion rules based on when an event is scheduled is a lot more cumbersome than simply recording the local time and location.
That is, suppose I schedule an event on 2016 April 1, at 10am in San Francisco, California. Under current DST rules, this translates into 2016 April 1, 5pm UTC or a Unix timestamp of 1459530000.
Now suppose DST is abolished in California between now and the event occurring. A Unix timestamp of 1459530000 would then correspond to 2016 April 1, at 9am in San Francisco, which is one hour too early.
Another way to think about this: You have to apply the DST conversion rules applicable at the time the event was created, NOT the (potentially different) rules at the time the event occurs. But maintaining different sets of conversion rules based on when an event is scheduled is a lot more cumbersome than simply recording the local time and location.
this assumes both users use calendar apps that work the same way.
so if google fixes gcal on mobile, but the other user puts it in (unfixed) outlook, they will still miss each other.
date/time is hard.
so if google fixes gcal on mobile, but the other user puts it in (unfixed) outlook, they will still miss each other.
date/time is hard.
This article is way overthinking it. It's better to simply store the UTC time of the event, and nothing else. Local time zone conversions can be done when displaying/editing. Presumably at the time you are reading your calendar, your local software knows the current rules to offset from GMT.
We have Skype meetings all the time involving parties in different US timezones and/or different countries. There's no "place" or local time zone for the meeting. We schedule in UTC and our local software or calendar program knows what time zone we are in to display the correct local time.
We have Skype meetings all the time involving parties in different US timezones and/or different countries. There's no "place" or local time zone for the meeting. We schedule in UTC and our local software or calendar program knows what time zone we are in to display the correct local time.
You missed the whole bit about the timezone rule changing in between when the event was last edited and when it was displayed.
I think you're missing the point of the article.
What he's getting at is that timezone rules change, so the function to go from local to UTC when you save the meeting is not the same as the function to go from UTC back to local at the time of the meeting.
What he's getting at is that timezone rules change, so the function to go from local to UTC when you save the meeting is not the same as the function to go from UTC back to local at the time of the meeting.
I do get the point, but simply preserving the local time isn't the right answer. I'd argue that when you change your local timezone offset, then your future schedule is indeterminate and all events have to be reverified. What if the event is for an international flight -- is it still leaving or arriving at the same local time? How about that standing 9AM meeting -- is at 9 because of the corporate office in a different time zone calls in, so your local time breaks their calendar?
"It's better to simply store the UTC time of the event, and nothing else."
The whole point of the article is that /you cannot know with certainty the UTC time of a future event/ if you are starting with a wallclock time in a particular location.
In this article, a 10:00 am meeting in Santiago has one UTC value prior to a new law, and a different UTC value after the new law.
If you are saying everyone should "schedule in UTC" like you do, good luck imposing that on the world!
The whole point of the article is that /you cannot know with certainty the UTC time of a future event/ if you are starting with a wallclock time in a particular location.
In this article, a 10:00 am meeting in Santiago has one UTC value prior to a new law, and a different UTC value after the new law.
If you are saying everyone should "schedule in UTC" like you do, good luck imposing that on the world!
I don't think you understood the described problem with storing the time in UTC. In the example in the article it is assumed and your software gets the timezone update. But because the meeting is stored in UTC, it displays the wrong time.
The reason it works for you is because you schedule in UTC. That's great, but for people that don't schedule meetings in UTC, you are vulnerable to the problem described if you just save the time in UTC.
Do you schedule meetings with your dentist in UTC too? I'd wager that most meetings are not scheduled in UTC.
The reason it works for you is because you schedule in UTC. That's great, but for people that don't schedule meetings in UTC, you are vulnerable to the problem described if you just save the time in UTC.
Do you schedule meetings with your dentist in UTC too? I'd wager that most meetings are not scheduled in UTC.
You haven't actually read the article have you?
> It's better to simply store the UTC time of the event, and nothing else. Local time zone conversions can be done when displaying/editing.
That timezone conversion is the problem, the UTC offset of a physical location can change drastically with surprisingly little notice. You schedule a meeting on your calendar for 10AM, it's stored as XUTC, the rules changes now your calendar tells you your meeting is at 11AM local time, you've missed your meeting (because you were physically meeting your VC who uses their own calendar which does not have that specific issue).
And being an hour off is pretty tame, back in 2011 Samoa flipped across the international date line, if you had scheduled a meeting after the flip and it was stored in UTC but edited or viewed in local time you would now go to it a day off.
> It's better to simply store the UTC time of the event, and nothing else. Local time zone conversions can be done when displaying/editing.
That timezone conversion is the problem, the UTC offset of a physical location can change drastically with surprisingly little notice. You schedule a meeting on your calendar for 10AM, it's stored as XUTC, the rules changes now your calendar tells you your meeting is at 11AM local time, you've missed your meeting (because you were physically meeting your VC who uses their own calendar which does not have that specific issue).
And being an hour off is pretty tame, back in 2011 Samoa flipped across the international date line, if you had scheduled a meeting after the flip and it was stored in UTC but edited or viewed in local time you would now go to it a day off.
Until today, I thought the way you did and advocated the same principle whenever I could. This article changed my mind very quickly, all with a simple easy-to-understand example.
For any event you generally have a location or time zone that the event time is specified in. If that's UTC then great, you don't have a problem. Most people don't work in UTC; if the time zone laws change, then the event time in UTC will change too.
For any event you generally have a location or time zone that the event time is specified in. If that's UTC then great, you don't have a problem. Most people don't work in UTC; if the time zone laws change, then the event time in UTC will change too.
When I create a repeating event in Calendar for Mac in a Google calendar, the scheduled time changes after a DST transition. Really annoying.
So, I would for future events record [Local Time], [Date], and [Location]. Then make sure I have a [Location] -> [Time Zone] table. [Local Time] and [Date] would probably be combined into one field. The crux of the problem is shifting rules on time zone (and location is my time zone determinant), so recalculation for other time zones is going to have to be dynamic. Storing UTC or UTC offset doesn't help the problem and actually creates a false solution.