4 ms·

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 remi

by curun1r 12y ago

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.

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

by curun1r 12y ago

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