I have a simple mySQL database on dedicated server..

In last two weeks I experienced strange behaviour resulting in changes in
all my timestamp fields (in all tables in database)!

Even though my admin says he didn't change any timezone settings or similar
things, after server reboot all my timestamps were moved for -06:00
Resulting in complete confusion of my customers data

How is that possible?
Any setting that could be disabled?
I just want to store timestamp data I insert from my clients (their local
times)...

On Tue, 20 Dec 2011 20:06:13 +0100, Matija wrote:
Do you know what time the OS is running in? Do you know what time the
hardware clock is set in? Do you know what timezone MySQL thinks it's
in?

Timestamps are stored in UTC, but the server's concept its own
relationship to actual UTC can be different in lots of different places
even if the time is "correct".

--
Kyle J Cardoza <admin (AT) zetachannel (DOT) com> sigged:
dynamite. -- Chris Suslowicz in the Monastery

On 20-12-2011 20:06, Matija wrote:
mysql> show variables like 'time_zone';
+---------------+--------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone ='-08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
1 row in set (0.00 sec)

mysql>


Find the correct value for this 'time_zone', and you should be OK again.

--
Luuk

My admin informed me (at first problem appearance) that there were no time
zone changes (since that's first thing I would thought of)...

So, I manually restored from backup at first issue etc.
However, the issue reappeared today and I lost patience...
It must be server related change of timezone... mySQL time zone is SYSTEM,
so basically system time zone change invokes changes to all timestamps
obtained from db....

As much I understand, it can only happen due system time zone change.. But
admin claims he didn't make any changes recently...


So, I will have to add SET time_zone to all my application queries (desktop
app connecting to remote server) in order to get proper values... Maybe that
should be some proper principle always when using mySQL server.

Matija wrote:
pulling it out for British time..
store all data in UTC, and pull it to whatever locale you are in.

But, I still have problems...
I want to "restore" data that is displayed incorrect, but using convert_tz
still have issues regarding summer/winter time... obviously system "update"
when time zone is changed takes summer/winter time in account, so that
simple restoration is hard - convert_tz has some "knowledge" about it, since
the same select convert_tz(myDat, X, Y), where X and Y are UTC and required
change (similar to one that appeared with system changes), returns different
results

e.g. in my current phpMyAdmin view I have following (all were 00:00 before
system update)
18-09-2011 06:00:00
02-11-2011 05:00:00
23-12-2011 06:00:00

when I use convert_tz (select myDat, convert_tz(myDat,X,Y) from table....)
converted column results are:
18-09-2011 00:00:00
02-11-2011 00:00:00
23-12-2011 01:00:00

So, basically I am stuck and am unable to make "restoration" simple way...
I will try to make restore from backup + analyse both databases and add
newely inserted rows since the time of "failure"