MySQL Time zone confusion

问题:

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.

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.

Why doesn't:

SET time_zone = '+00:00';

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

SOLVED:

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.

回答1:

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