Re: Does Change in Daylight Savings Time affect MySQL

[James Rawlings]

Steven;

I've been looking at this as well. Your OS is in control of this one, and will be needing the patch. A few systems I maintain have had the OS patched and to test
the DST rules, try the following: (this was off a 5.0 system)

mysql> SELECT UNIX_TIMESTAMP('2006-04-02 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2006-04-02 02:00:00') |
+---------------------------------------+
| 1143972000 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2006-04-02 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2006-04-02 03:00:00') |
+---------------------------------------+
| 1143972000 |
+---------------------------------------+
1 row in set (0.00 sec)

Retard 1 hr (3600 secs)

mysql> SELECT UNIX_TIMESTAMP('2006-10-29 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2006-10-29 00:00:00') |
+---------------------------------------+
| 1162105200 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2006-10-29 01:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2006-10-29 01:00:00') |
+---------------------------------------+
| 1162112400 |
+---------------------------------------+
1 row in set (0.00 sec)

Advance 1hr (7200 secs) ie get an extra hour

<<<<<<<<<<<<<<<<<< OS Patch >>>>>>>>>>>>>>>>>

Old Rule:

mysql> SELECT UNIX_TIMESTAMP('2007-04-01 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2007-04-01 02:00:00') |
+---------------------------------------+
| 1175418000 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2007-04-01 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2007-04-01 03:00:00') |
+---------------------------------------+
| 1175421600 |
+---------------------------------------+
1 row in set (0.00 sec)

New Rule:


mysql> SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2007-03-11 02:00:00') |
+---------------------------------------+
| 1173607200 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2007-03-11 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2007-03-11 03:00:00') |
+---------------------------------------+
| 1173607200 |
+---------------------------------------+
1 row in set (0.00 sec)

Jim Rawlings