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.

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.

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.

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.

"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.

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.

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.

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.

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.

"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.

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.

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.

"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.

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.

"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 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.

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.

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.