3 ms·

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 y

by tmd83 12y ago

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.

protomyth 12y ago
"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.