SEO

Hi, can anyone explain mysql 5.0.54 handling of replication between two
servers with inconstant /etc/localtime (but the same real clock time for
UTC)

On one server I inserted a new row with "created_at" and "updated_at" as
the same time. Server localtime is GMT+1, created col is a date,
updated_at col is a timestamp

When this replicated to the other server which had localtime set to GMT,
and then after it replicated I changed localtime to GMT+1 I find that
created_at is no longer the same as updated_at - now updated_at is 1
hour later...

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?


Thanks

Ed W

On Tue, May 13, 2008 at 2:07 PM, Ed W <lists@wildgooses.com> 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

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Rob Wultsch wrote:
> On Tue, May 13, 2008 at 2:07 PM, Ed W <lists@wildgooses.com> 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

On Tue, May 13, 2008 at 11:56 PM, Ed W <lists@wildgooses.com> wrote:
> Rob Wultsch wrote:
>>
>> On Tue, May 13, 2008 at 2:07 PM, Ed W <lists@wildgooses.com> 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@gmail.com
wultsch (aim)

Rob Wultsch wrote:
> 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.
>


OK, your example is clearly demonstrating the effect I am seeing -
however, by changing the server localtime option I appear to be
influencing the default mysql time offset.

I still don't understand the reality of what is happening here - your
example appears to show that datetime fields are correctly stored as GMT
and adjusted as desired, but that a timestamp is a function of localtime?

Either way they appear inconsistent...

The end result needs to be that I can get these dates out of the
database and correctly adjust them for the desired users localtime.
What you are demonstrating here is that I either need to ditch all my
timestamp columns (inconvenient) or switch the server to only run in UTC
(inconvenient in that I need to mentally adjust in order to make sense
of the log files). It would appear that if I run the server with a
correct localtime then I have a bag of trouble when I want to figure out
the time something happened (as you can see c1 and c2 should be the same
in all cases, but not in your example)

Can anyone shed some light on the best approach?

Thanks

Ed W

On Wed, May 14, 2008 at 12:55 PM, Ed W <lists@wildgooses.com> wrote:
> Rob Wultsch wrote:
>>
>> 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.
>>
>
>
> OK, your example is clearly demonstrating the effect I am seeing - however,
> by changing the server localtime option I appear to be influencing the
> default mysql time offset.
>
> I still don't understand the reality of what is happening here - your
> example appears to show that datetime fields are correctly stored as GMT and
> adjusted as desired, but that a timestamp is a function of localtime?
>
> Either way they appear inconsistent...
>
> The end result needs to be that I can get these dates out of the database
> and correctly adjust them for the desired users localtime. What you are
> demonstrating here is that I either need to ditch all my timestamp columns
> (inconvenient) or switch the server to only run in UTC (inconvenient in that
> I need to mentally adjust in order to make sense of the log files). It
> would appear that if I run the server with a correct localtime then I have a
> bag of trouble when I want to figure out the time something happened (as you
> can see c1 and c2 should be the same in all cases, but not in your example)
>
> Can anyone shed some light on the best approach?
>
> Thanks
>
> Ed W

The display of the timestamp is dependent on the local time zone.
Datetime is not adjusted for display.

I don't use timestamp because I think it is Voodoo or some other form
of black magic. I don't trust black magic that is not my own (or for
that matter anything I write involving pointers). If I want a record
to store NOW() then I tell it NOW().

For whatever it is worth I suggest ditching timestamp and going to datetime.

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)