MySQL时区混乱 [英] MySQL Time zone confusion

问题描述

几天前，我发了一条帖子，并使用了回复中的建议.我有一个大型Web应用程序，该应用程序托管在我们时区的服务器上，在这里我将所有datetime/timestamps都使用到当前时区.我应该使用UTC并将其在代码级别转换为适当的时区.

I made a post a couple days ago and used the advice given from the responses. I have a large scale web application which was hosted on a server in our timezone, where I used all datetime/timestamps to the current timezone. I should have used UTC and converted it on the code level to the appropriate timezone.

好吧，我现在正在这样做，但是我遇到了问题.我现在使用的服务器不在我的时区中，并且TIMESTAMP(插入时)设置为他们的系统(O/S)时间.不是UTC时间.

Well I am doing that now but I am having issues. The server I am using now is not in my timezone and the TIMESTAMP (on insert) is set to their system (O/S) time. Not the UTC time.

为什么不:

SET time_zone = '+00:00';

固定插入的TIMESTAMP值?如何确保MySQL服务器将时间戳默认为UTC?

Fix the insert TIMESTAMP value? How can I ensure the MySQL server defaults the timestamps to UTC?

已解决:

这确实令人困惑，因为MySQL自动将数据库中的TIMESTAMP转换并显示为当前时区，因此我所有的TIMESTAMPS都已转换并显示为SYSTEMS时区.实际上，所有时间戳都存储为UTC.所以，如果你去:

This is really confusing because MySQL automatically converts and displays TIMESTAMP's in the database to the current timezone, so all my TIMESTAMPS were converted and displayed to the SYSTEMS timezone. When in reality ALL TIMESTAMPS are stored as UTC. So if you go:

SET SESSION time_zone = '+00:00'; SELECT * FROM what_ever_table;

它将显示该时区的时间戳.

It will display the timestamp in that timezone.

推荐答案

在插入/更新值时，MySQL并不关心时区.它所看到的只是时间/日期值和字符串.标准日期格式为yyyy-mm-dd hh:mm:ss，不包含时区数据.时区仅在您检索数据并希望格式化值时才进入图片.使用 convert_tz()函数

MySQL doesn't care about timezones when inserting/updating values. all it sees are time/date values and strings. There is NOT timezone data included with its standard date format: yyyy-mm-dd hh:mm:ss. Timezones only enter into the picture when you're retrieving the data and want to format the values, e.g. using the convert_tz() function

这篇关于MySQL时区混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！