How to save datetimes for future events
creativedeletion.com
creativedeletion.com
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.
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.
"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.
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.
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.
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.
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.
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.
Person I was responding to gave bad reasoning ("Pick the timezone that matters most") instead of echoing what the article said.
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.
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 should point out I'm in the SQL / Stored Procedure camp and not talking using some object mapping solution. That might get problematic.
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 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.
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.
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.
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.
[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.
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.
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
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.
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?
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-...
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.
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.
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.
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.
(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.
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.
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.
0. http://stackoverflow.com/questions/6841333/why-is-subtractin...
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.
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.
# 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%sTYou 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. :/
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)
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 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.
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)
What you really want is an absolute time, and a localized timezone that applies to the event (which may not be your own).
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.
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.
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)
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.
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 the event is a meeting, then the meeting organizer will be in charge of setting the appropriate time zone.
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.
For those who need a web fron-tend implementation, I would like to recommend Moment Timezone (http://momentjs.com/timezone/).
The computer still has the calendar entry at the right instant in time identified when the user input the entry.
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.
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.
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 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.
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?
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.
But yes, if you schedule the meeting in UTC, use UTC. The point is not to convert from non-UTC to UTC.
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.
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.
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.
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.
> 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.
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.
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.
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!
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.