SELECT
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime`
FROM `table_name`
设置 mysql 时区表
为了使 CONVERT_TZ正常工作,需要填充时区表
SELECT * FROM mysql.`time_zone` ;
SELECT * FROM mysql.`time_zone_leap_second` ;
SELECT * FROM mysql.`time_zone_name` ;
SELECT * FROM mysql.`time_zone_transition` ;
SELECT * FROM mysql.`time_zone_transition_type` ;
如果它们是空的,那么通过运行以下命令来填充它们
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
(if the above gives error "data too long for column 'abbreviation' at row 1")
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql
echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql
cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql
mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql
SELECT
tzn.Name AS tz_name,
tztt.Abbreviation AS tz_abbr,
tztt.Is_DST AS is_dst,
tztt.`Offset` AS `offset`,
DATE_ADD('1970-01-01 00:00:00',INTERVAL tzt.Transition_time SECOND) AS transition_date
FROM mysql.`time_zone_transition` tzt
INNER JOIN mysql.`time_zone_transition_type` tztt USING(Time_zone_id, Transition_type_id)
INNER JOIN mysql.`time_zone_name` tzn USING(Time_zone_id)
-- WHERE tzn.Name LIKE 'Europe/Moscow' -- Moscow has weird DST changes
ORDER BY tzt.Transition_time ASC
CONVERT_TZ还应用任何必要的 DST 更改基于上述表中的规则和您使用的日期。
注:
根据 医生,你为 time _ zone 设置的值不会改变,例如,如果你将它设置为“ + 01:00”,那么 time _ zone 将被设置为与 UTC 的偏移量,这与 DST 不同,因此它将全年保持不变。