如何按时间对事件表进行排序（有不同的时区）？ [英] How to sort an event table by time (have different timezones)?

问题描述

我有一个表，其结构如下（我不能改变）：

I have a table that is structured the following way (that i can't change):

EventName，LocalTime，Timezone

EventName, LocalTime, Timezone

数据类似如下：

Event1, 10:00, ET
Event2, 11:00, ET
Event3, 12:00, ET
Event4, 10:00, CT

我怎么能写一个sql来排序这个实际的时间，因此结果如下：

how can i write a sql to sort this by actual time so result would be like:

Event1, 10:00, ET
Event2, 11:00, ET
Event4, 10:00, CT
Event3, 12:00, ET

请注意，Event2和Event4同时发生，所以无论哪一个都没关系。

Note that Event2 and Event4 happen at the same time so whichever comes first doesn't matter.

我的数据库是MySQL。

My database is MySQL.

同样，我不能修改源代码，所以我必须使用我处理的数据

Again, I can not modify the source so I have to work with the data I'm dealt with

感谢

推荐答案

ver of mysql ...

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)

Order By可以表达式。

The key here is that Order By can take an expression.

请参阅 http://dev.mysql.com/doc/refman/5.5/en/select.html

优化

在上面的示例中，MySql将创建一个临时表。根据数据大小，可能会更快地支付数据插入价格与数据检索价格。

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.

为此， ：向数据表中添加另一列time_gmt，然后在新列上排序。

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.

如果你不能添加Tz_offsets表（如果你只能使用raw sql），那么你有两个选项：

If you can't add the Tz_offsets table (if you can only use raw sql), then you have two choices:

1. 每次运行sql时，将Tz_offsets表添加为临时表。您的MySQL用户将需要临时表创建权限。

1. Add the Tz_offsets table as a temp table each time you run your sql. Your MySQL user will need temp table create permission.

使用Case表达式在表达式中执行tz偏移量查找。

Do the tz offset lookups within the order by expression by using Case expression.

例如

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))

这篇关于如何按时间对事件表进行排序（有不同的时区）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！