Подтвердить что ты не робот

MySQL CONVERT_TZ()

Я пытаюсь настроить базу данных, в которой хранится ежедневное время оповещения, указанное пользователями. Например, пользователь хочет получать оповещение, если какой-либо критерий выполняется каждый день с 7:00 до 7:30. В попытке реализовать это мне нужно учесть летнее время. Вот мое попытанное решение:

  1. Сохраните информацию о местном часовом поясе пользователей (в полной форме, например, "США/Восток") в одной таблице (например, userInfo), а время срабатывания будильника - в другой таблице (например, userAlarms).
  2. При запросе к таблице userAlarms конвертируйте время UTC в местное время пользователя, как указано в столбце tz, хранящемся в таблице CONVERT_TZ(UTC_TIME(), 'UTC', userInfo.tz) помощью CONVERT_TZ(UTC_TIME(), 'UTC', userInfo.tz).

Вопрос 1. Насколько я понимаю, при указании названия часового пояса (например, в США и на востоке) необходимо учитывать переход на летнее время. Например, вызов CONVERT_TZ('00:00:00', 'UTC', 'US/EASTERN') 1 января должен дать "19: 00: 00", но 1 июля вызов должен привести к "20: 00: 00'. Я прав?

Вопрос 2. Если Q1 правильный, мне нужно постоянно обновлять таблицу часовых поясов MySQL, чтобы поддерживать смещения UTC часового пояса в актуальном состоянии?

Вопрос 3. Пример, приведенный в документации MySQL SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') выдает " NULL " при запуске на моем сервере. Может ли это быть вызвано отсутствием настройки таблиц часовых поясов?

Как я могу это проверить?

4b9b3361

ОТВЕТЫ

Ответ 1

Если это дает значение null, таблицы TZ не были настроены:

SELECT CONVERT_TZ(now(),'US/Eastern','US/Central');

Если у вас нет настроенных таблиц часовых поясов, вы можете обновить час смещение в таблице пользователя, а затем выполните:

select utc_timezone() - interval user_timezone_offset_in_hours hour
from userinfo a
where user_id = 999;

Вам все равно нужен способ обновления часового пояса пользователя.

Если вы пишете это для веб-приложения, вы можете получить часовой пояс через javascript, здесь статья, которая описывает, как (не попробовал это, но похоже, что это сработает).

Немного объяснения относительно "интервала" выше...

Одна из наиболее сложных конструкций MySQL - использование INTERVAL ключевое слово, лучше всего показано в примере (числовое значение может быть выражением или значением поля)

select now() today, now() - interval 1 day yesterday;
+---------------------+---------------------+
| today               | yesterday           |
+---------------------+---------------------+
| 2011-05-26 13:20:55 | 2011-05-25 13:20:55 |
+---------------------+---------------------+

Вы можете добавить их и вычесть их так, как вам нравится, поэтому я никогда не беспокоиться о функциях добавления/вычитания/преобразования даты/времени

select now() a, now() - interval 1 day + interval 4 hour + interval 8 minute b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-05-26 13:24:16 | 2011-05-25 17:32:16 |
+---------------------+---------------------+

Вы можете использовать отрицательные числа (должны быть хорошими для отрицательных смещений часового пояса) они одинаковы:

select now() - interval 1 month a, now() + interval -1 month b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-04-26 13:38:05 | 2011-04-26 13:38:05 |
+---------------------+---------------------+

Ответ 2

Я нашел эту ветку полезной и решил поделиться страницей doc для импорта этой информации. Я сделал точно так, как указано ниже в CentOS и RHEL, и это работало безупречно. Теперь я могу использовать функцию CONVERT_TZ с такими аргументами, как "GMT" и "US/Eastern".

Из документации MySQL это как импортировать информацию таблицы часовых поясов MySQL:

http://dev.mysql.com/tech-resources/articles/4.1/time.html

Для всех пользователей, использующих MySQL 4.1.3 или новее в системе на основе Unix (вспомните, что это еще не работает в системах Windows):

Заполнение таблиц часовых поясов.

Для этого запустите программу mysql_tzinfo_to_sql, поставляемую с дистрибутивом MySQL. mysql_tzinfo_to_sql считывает файлы часовых поясов операционной системы и генерирует из них SQL-инструкции. Операторы SQL затем обрабатываются mysql для загрузки таблиц часовых поясов.

Чтобы успешно выполнить mysql_tzinfo_to_sql, нужно знать, где хранятся файлы часовых поясов операционной системы сервера; проверьте каталог с именем, похожим на /usr/share/zoneinfo. Передайте имя каталога в командной строке mysql_tzinfo_to_sql и отправьте вывод в программу mysql. Вот пример.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Примечание. Вышеупомянутая команда предполагает, что "mysql_tzinfo_to_sql" и "mysql" находятся на вашем пути. Если это не так, вам нужно будет предоставить полный путь для обоих при запуске команды или переключиться в папку bin mysql и использовать следующую команду:

shell> ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -u root mysql

Ответ 3

Q1: Да, CONVERT_TZ учитывает время летнего времени. Эта информация и время начала и окончания DST для каждого часового пояса сохраняются в таблицах time_zone_ *.

Q2: Да, как указано в документах mysql, информация о часовом поясе изменяется в зависимости от политики каждой области. Вам нужно обновлять таблицы time_zone_ * каждый раз, когда происходит изменение. Иногда это отстой, это один из них.

Q3: Это 5 таблиц часовой пояс, запросите их, чтобы увидеть, есть ли у них что-нибудь в них:

select * from mysql.time_zone_transition_type;
select * from mysql.time_zone_transition;
select * from mysql.time_zone_name;
select * from mysql.time_zone_leap_second;
select * from mysql.time_zone;