3 ms·
For future events, you expect to schedule a meeting / appointment at [Local Time] on [Date] at [Location]. If a time change happens between now and the future
by protomyth 12y ago
For future events, you expect to schedule a meeting / appointment at [Local Time] on [Date] at [Location]. If a time change happens between now and the future event (e.g. daylight savings time), it doesn't matter from the perspective of the meeting because you have a fixed time.
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.
- logicuce 12y agoSpot 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.
- protomyth 12y ago"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.
- mark-r 12y agoYou'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.
- protomyth 12y agoI 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-time-zone-from-a-location-using-latitude-and-longitude-coordinates http://stackoverflow.com/questions/16086962/how-to-get-a-tim...
- angersock 12y agoNo 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.
- mark-r 12y agoDid 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.
- angersock 12y agoYeah, 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.
- curun1r 12y agoThe 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.
- protomyth 12y ago"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.
- tmd83 12y agoI 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.
- protomyth 12y agoI 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.
- barrkel 12y agoAll 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.
- protomyth 12y agoSomeone 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.
- fragmede 12y agoDST 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.
- protomyth 12y agoThis 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.