MySQL: интересно, как выбрать записи на основе дня, с учетом часового пояса

Question

Я хочу выбрать записи, которые "в настоящее время доступны" на основе специфики в таблицах. Я думаю, что это должно быть легко, но, конечно, я этого не вижу. Я надеюсь, что этот вопрос заставит меня встряхнуть голову, и, возможно, я смогу ответить на свой вопрос!

У меня есть 2 таблицы:

table_1  
    id  
    time_zone_name (eg, "US/Eastern")

table_2  
    tab1_id  
    day_of_week (1=Monday, 7=Saturday)

Учитывая, что я знаю, что текущая временная метка GMT, скажем, "2012-07-08 13:35:00 GMT", я должен иметь возможность выбирать записи из таблицы table_1, где table_2.day_of_week - это совпадение после корректировки часового пояса.

Функция MySql CONVERT_TZ выглядела многообещающе!

CONVERT_TZ ('2004-01-01 12:00:00', 'GMT', 'MET')
CONVERT_TZ ('2004-01-01 12:00:00', '+ 00:00', '+ 10:00');

Я мог бы попробовать это:

SELECT id из table_1 внутреннее соединение table_2 ON (table_1.id = table_2.table_1_id)
WHERE DAYOFWEEK (CONVERT_TZ (UTC_TIMESTAMP, 'GMT', table_1.time_zone_name)) = table_2.day_of_week

но функция CONVERT_TZ не принимает имена зон, такие как "US/Eastern" на MySql, установленном на моем хосте. Согласно документу MySql:

Чтобы использовать именованные часовые пояса, такие как "MET" или "Europe/Moscow", необходимо правильно настроить таблицы часовых поясов.

Видимо они не правильно настроены.

Есть ли лучший способ сделать это? Любые предложения будут ценны.

Answer 1

Если вы используете таблицу поиска, чтобы получить разницу, я уверен, что вы спрашиваете пользователя сайта, где они находятся. Как только они войдут в свое местоположение, вы можете легко использовать PHP для настройки их локализации через ваши скрипты. Это довольно легко с локализацией php. Список часовых поясов действительно довольно большой. Несмотря на то, что он содержит ваш пример US/Eastern, не рекомендуется применять t, поскольку он поддерживается (в разделе "Прочие") для обратной совместимости.

Тогда вы можете использовать PHP дату, используя это date('Z') чтобы получить их смещение в считанные секунды. Если вы храните свои поля даты и времени как метки времени Unix, вам будет несложно добавить или вычесть их смещение. Таким образом, вы нормализуете данные, которые ВЫ сохраняете, и сохраняете их с нулевым смещением (GMT).

Когда вы запрашиваете данные в базе данных, просто добавьте их смещение, которое вы получили на шаге выше, к полученному результату. Вы даже можете использовать date() снова, чтобы отформатировать даты / время в их локальный формат.