Bug #42175 backup database from the different timezone server
Submitted: 16 Jan 2009 23:22 Modified: 19 Jan 2009 7:20
Reporter: Oliver Cai Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:Server version: 5.0.67-community OS:Windows
Assigned to: CPU Architecture:Any
Tags: Backup, timestamp, timezone

[16 Jan 2009 23:22] Oliver Cai 
Description:

When I backup the table with timestamp field from my live server (timezone: mst) to my local server(timezone:pst). the value of timestamp field will be changes automatically.

How to repeat:
The live server and local server exist at the different timezone.

The live server timestamp field value = '2009-01-15 00:10:57'

When I directly download the database files to my local server, 

the timestamp field value will be changed to "2009-01-14 22:10:57".

Suggested fix:
Backup data don't need change the value.This is very confused.
[18 Jan 2009 8:35] Hema Sridharan 
This defect looks like duplicate of bug#37146
[19 Jan 2009 7:20] Sveta Smirnova 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.0/en/timestamp.html:

TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. 

So this is correct you have different representation of the same time for different time zones. Solution in your case: use DATETIME which doesn't convert its values.