Может ли MySQL преобразовать сохраненное время в формате UTC в местный часовой пояс?

Может ли MySQL преобразовать сохраненное время в формате UTC в местное время с привязкой к часовому поясу непосредственно в обычном операторе выбора?

Допустим, у вас есть данные с отметкой времени (UTC).

CREATE TABLE `SomeDateTable` (
  `id`    int(11) NOT NULL auto_increment,
  `value` float NOT NULL default '0',
  `date`  datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
)

Тогда, когда я

"select value, date from SomeDateTable";

Я, конечно, получаю все даты, как в их сохраненной форме UTC.

Но предположим, что я хотел бы иметь их в другом часовом поясе (с DST), могу ли я затем добавить немного волшебства в запрос выбора, чтобы я получил все даты обратно в выбранный часовой пояс?

"select value, TIMEZONE(date, "Europe/Berlin") from SomeDateTable";

Или я должен сделать это на каком-то другом слое сверху, например, в каком-то php-коде? (похоже, так большинство людей решило эту проблему).

Если ваша установка MySQL позволяет использовать CONVERT_TZ, это очень чистое решение, этот пример показывает, как его использовать.

SELECT CONVERT_TZ( '2010-01-01 12:00', 'UTC', 'Europe/Stockholm' )

Однако я не знаю, хороший ли это способ, поскольку в некоторых установках MySQL эта функция отсутствует, используйте с осторожностью.


mysql sql timezone convert-tz
person Johan    schedule 02.02.2010    source источник

Ответы (7)


arrow_upward
50
arrow_downward

Ага, есть _1 _ функция.

person Stephen Fischer    schedule 02.02.2010
comment
Кажется, что это правильно, но я обнаружил эту ошибку напрямую: bugs.mysql.com/bug .php? id = 12445 на моем сервере отсутствовали данные о часовом поясе ... - person Johan; 03.02.2010
comment
mysql_tzinfo_to_sql / usr / share / zoneinfo | mysql -u корень mysql - person Johan; 03.02.2010

arrow_upward
30
arrow_downward

Для тех, кто не может настроить среду mysql (например, из-за отсутствия доступа SUPER) для использования удобных для человека имен часовых поясов, таких как «America / Denver» или «GMT», вы также можете использовать функцию с числовыми смещениями, например:

CONVERT_TZ(date,'+00:00','-07:00')
person tmsimont    schedule 23.08.2013
comment
Это не сработает, если в этом часовом поясе установлено летнее время (timeanddate.com/time/dst ), так как у вас будет разное смещение летом и зимой, а также есть несколько примеров с 30-минутным летним временем ... - person Johan; 02.09.2013
comment
интересно ... в моем случае я должен выполнить несколько запросов из PHP и не хочу выполнять какую-либо обработку TZ с результатами в PHP, но я мог бы легко изменить числовое значение в запросе выбора. Благодарность - person tmsimont; 03.09.2013

arrow_upward
12
arrow_downward 

select convert_tz(now(),@@session.time_zone,'+03:00')

Для получения времени используйте только:

time(convert_tz(now(),@@session.time_zone,'+03:00'))
person Jestin    schedule 11.07.2016

arrow_upward
6
arrow_downward

Можно легко использовать

CONVERT_TZ(your_timestamp_column_name, 'UTC', 'your_desired_timezone_name')

Например:

CONVERT_TZ(timeperiod, 'UTC', 'Asia/Karachi')

Плюс это также можно использовать в инструкции WHERE, и для сравнения отметки времени я бы использовал следующее в предложении Where:

WHERE CONVERT_TZ(timeperiod, 'UTC', '{$this->timezone}') NOT BETWEEN {$timeperiods['today_start']} AND {$timeperiods['today_end']}
person MR_AMDEV    schedule 31.07.2019
comment
Когда я выполнил ваш запрос WHERE CONVERT_TZ в mysql, он провел сканирование всей таблицы, хотя я использовал индексированный столбец для своего «периода времени» и абсолютных значений временной метки для «Между началом и концом». Я читал, что обычно не рекомендуется использовать функции столбцов в предложениях «WHERE» или «HAVING». - person McAuley; 13.02.2020
comment
Вы могли бы быть правы, но с другой стороны, если вы не используете его в классе where, то как мы будем сравнивать значения? - person MR_AMDEV; 25.04.2020
comment
Хороший момент, и единственное, что у меня есть для этого, - быть очень осторожным с предложением WHERE, чтобы минимизировать / смягчить сканирование табличного пространства. В этой области может оказаться полезным разделение таблицы по «временному периоду». Или серверный язык можно использовать для преобразования часовых поясов, но это, вероятно, выходит за рамки вопроса OP. - person McAuley; 29.04.2020

arrow_upward
4
arrow_downward

1. Правильно настройте свой сервер:

На сервере su выполните root и сделайте следующее:

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

(Обратите внимание, что команда в конце, конечно же, mysql, и вы отправляете ее в таблицу, которая имеет то же имя : mysql.)

Далее, теперь вы можете # ls /usr/share/zoneinfo.

Используйте эту команду, чтобы увидеть всю информацию о часовых поясах на ubuntu или почти на любом unixish сервере.

(Кстати, это удобный способ найти точное официальное название какого-либо часового пояса.)

2. В mysql это тривиально:

Например

mysql> select ts, CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') from example_table ;
+---------------------+-----------------------------------------+
| ts                  | CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') |
+---------------------+-----------------------------------------+
| 2020-10-20 16:59:57 | 2020-10-20 06:59:57                     |
| 2020-10-20 17:02:59 | 2020-10-20 07:02:59                     |
| 2020-10-20 17:30:08 | 2020-10-20 07:30:08                     |
| 2020-10-20 18:36:29 | 2020-10-20 08:36:29                     |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20                     |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20                     |
| 2020-10-20 19:00:18 | 2020-10-20 09:00:18                     |
+---------------------+-----------------------------------------+
person Fattie    schedule 21.10.2020

arrow_upward
2
arrow_downward

Предлагаю использовать

SET time_zone = 'proper timezone';

выполняется один раз сразу после подключения к базе данных. и после этого все временные метки будут автоматически конвертированы при их выборе.

person zerkms    schedule 14.02.2010
comment
Важно отметить, что это не приведет к автоматическому преобразованию полей DATE, TIME и DATETIME. Это приведет к автоматическому преобразованию только полей TIMESTAMP и повлияет на результаты функций NOW () и CURDATE (). - person Tom; 19.05.2012
comment
Если вы хотите, чтобы это было постоянно, вы также можете изменить параметр time_zone. - person jamsandwich; 27.11.2017

arrow_upward
-1
arrow_downward

Я не уверен, какие математические расчеты можно выполнить с типом данных DATETIME, но если вы используете PHP, я настоятельно рекомендую использовать временные метки на основе целых чисел. По сути, вы можете сохранить 4-байтовое целое число в базе данных с помощью функции PHP time (). Это значительно упрощает выполнение математических расчетов.

person TheBuzzSaw    schedule 02.02.2010
comment
Convert_tz был ответом на мой вопрос, но, возможно, я все равно буду использовать для этого php ... поскольку на сервере, на котором я размещаю свой сайт, также отсутствовали данные этого часового пояса (и там я не могу это исправить ...) - person Johan; 03.02.2010