On Tue, May 13, 2008 at 11:56 PM, Ed W <lists@stripped> wrote:
> Rob Wultsch wrote:
>>
>> On Tue, May 13, 2008 at 2:07 PM, Ed W <lists@stripped> wrote:
>>
>>>
>>> I had naively assumed that dates would always be stored in UTC in the
>>> database and the only effect of localtime would be for display purposes?
>>> Can anyone shed some light on what's happening here please?
>>>
>>
>> "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. (This occurs only for the TIMESTAMP data type, not for
>> other types such as DATETIME.)"
>>
>> http://dev.mysql.com/doc/refman/5.1/en/timestamp.html
>>
>>
>
> Sure - but I'm observing the opposite. My datetime is correct in UTC, but
> the timestamp col has definitely jumped forward one hour.
> Orig server:
> created_at: 2008-05-13 17:52:53
> updated_at: 2008-05-13 17:52:53
>
> New server where the localtime variable has been changed:
> created_at: 2008-05-13 17:52:53
> updated_at: 2008-05-13 18:52:53
>
> Using default mysql client settings on each server to examine the data, so
> possibly problem is related to client incorrectly adjusting values for
> display?
>
> I then changed the second servers localtime option, restored the same
> database as before and again replicated the same data across to catch up and
> this time they show the same values. So basically the value retrieved from
> the second database is influenced by the localtime options being different
> on each server *at the time replication occurs*
>
> Anyone shed some light on this?
>
> Ed W
>
This sounds like expected behavior to me. If you set the timezone one
hour forward a timestamp will be one hour forward. The data stored on
the server is the same, and will display the same if you change the
timezone. The timezone setting when the insert occurred should have no
effect.
mysql> CREATE TABLE `t1` (`c1` TIMESTAMP,`c2` DATETIME);
Query OK, 0 rows affected (0.05 sec)
mysql> SET time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES(NOW(),NOW());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+---------------------+---------------------+
| c1 | c2 |
+---------------------+---------------------+
| 2008-05-14 19:43:00 | 2008-05-14 19:43:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> SET time_zone = '+1:00';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES(NOW(),NOW());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+---------------------+---------------------+
| c1 | c2 |
+---------------------+---------------------+
| 2008-05-14 20:43:00 | 2008-05-14 19:43:00 |
| 2008-05-14 20:43:15 | 2008-05-14 20:43:15 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> SET time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1;
+---------------------+---------------------+
| c1 | c2 |
+---------------------+---------------------+
| 2008-05-14 19:43:00 | 2008-05-14 19:43:00 |
| 2008-05-14 19:43:15 | 2008-05-14 20:43:15 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
But I could be completely off the mark.
--
Rob Wultsch
wultsch@stripped
wultsch (aim)
