How to sort an event table by time (have different timezones)?

Using a modern ver of mysql Create table Tz_offsets: col Timezone, offset Data: ET, 0 CT, 1 Select EventName, LocalTime, Timezone From my_data_table JOIN Tz_offsets On Tz_offsets. Timeszone = my_data_table. Timeszone Where xxx Order By (LocalTime - offset) The key here is that Order By can take an expression See http://dev.mysql.com/doc/refman/5.5/en/select.html Optimization In the above example, MySql will create a temp table.

Depending on the size of your data, it might be much faster to pay a data insertion price vs a data retrieval price To do so: add another column to the data table, "time_gmt" and then sort on the new column. But if you can't, the above will work If you can't add the Tz_offsets table (if you can only use raw sql), then you have two choices: Add the Tz_offsets table as a temp table each time you run your sql. Your MySQL user will need temp table create permission Do the tz offset lookups within the order by expression by using Case expression Eg Select EventName, LocalTime, Timezone From my_data_table Where xxx Order By (LocalTime - (CASE Timezone WHEN 'ET' THEN 0 WHEN 'CT' THEN 1 WHEN 'PT' THEN 3 ELSE 0)).

Using a modern ver of mysql... Create table Tz_offsets: col Timezone, offset Data: ET, 0 CT, 1 Select EventName, LocalTime, Timezone From my_data_table JOIN Tz_offsets On Tz_offsets. Timeszone = my_data_table. Timeszone Where xxx Order By (LocalTime - offset) The key here is that Order By can take an expression.

See http://dev.mysql.com/doc/refman/5.5/en/select.html Optimization In the above example, MySql will create a temp table. Depending on the size of your data, it might be much faster to pay a data insertion price vs a data retrieval price. To do so: add another column to the data table, "time_gmt" and then sort on the new column.

But if you can't, the above will work. If you can't add the Tz_offsets table (if you can only use raw sql), then you have two choices: Add the Tz_offsets table as a temp table each time you run your sql. Your MySQL user will need temp table create permission.Do the tz offset lookups within the order by expression by using Case expression.

Eg Select EventName, LocalTime, Timezone From my_data_table Where xxx Order By (LocalTime - (CASE Timezone WHEN 'ET' THEN 0 WHEN 'CT' THEN 1 WHEN 'PT' THEN 3 ELSE 0)).

Awesome, thanks! Got it working now :) – Charlie Jul 15 '10 at 4:29.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.