How to save datetimes for future events

Re:How to save datetimes for future events by protomyth on Monday March 23, 2015 @03:15PM (#9252815)

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.

[ Parent | on HN ]

Re:How to save datetimes for future events by tmd83 on Monday March 23, 2015 @03:48PM (#9252965)

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.

[ Parent | on HN ]

Re:How to save datetimes for future events by protomyth on Monday March 23, 2015 @03:56PM (#9253010)

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

[ Parent | on HN ]

Re:How to save datetimes for future events by curun1r on Monday March 23, 2015 @04:21PM (#9253140)

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.

[ Parent | on HN ]

Re:How to save datetimes for future events by protomyth on Monday March 23, 2015 @04:58PM (#9253317)

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

[ Parent | on HN ]

Re:How to save datetimes for future events by edejong on Monday March 23, 2015 @07:28PM (#9253982)

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.

[ Parent | on HN ]