Due to the recent changes to the DayLight Savings Time (dst) from New Zealand (NZ), which makes the clock to move one hour ahead on Sep 30th rather than on October 6th. Even Venezuela had the similar change, which was supposed to be in effect since yesterday, Sep 23rd.

To get these new time zone changes to the MySQL server, I first updated my Redhat 4 server to have the latest tzdata package from Redhat. Redhat only included the changes for NZ but not for Venezuela in the recent release of tzdata-2007f-1 package. Once this tzdata package is installed through yum; I ran MySQL’s mysql_tzinfo_to_sql utility to get the latest changes and feeded the output back to server which inturn truncated all the timezone tables data and inserted new values.

1

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

After changes to timezone related tables in the ‘mysql’ database, I can see the changes after the server restart. Here is a simple case for NZ DST change, before and after updating the timezone tables:

After the NZ dst changes applied:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

mysql> SELECT CONVERT_TZ('2007-09-28 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-09-28 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-09-28 22:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT CONVERT_TZ('2007-09-29 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-09-29 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-09-29 23:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT CONVERT_TZ('2007-10-05 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-05 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-05 23:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT CONVERT_TZ('2007-10-06 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-06 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-06 23:00:00                          |
+----------------------------------------------+
1 row in set (0.01 sec)
 
mysql> SELECT CONVERT_TZ('2007-10-05 22:53:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-05 22:53:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-06 11:53:00                          |
+----------------------------------------------+
 
mysql> SELECT CONVERT_TZ('2007-10-06 22:53:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-06 22:53:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-07 11:53:00                          |
+----------------------------------------------+
1 row in set (0.01 sec)

Before the NZ dst changes applied:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

mysql> SELECT CONVERT_TZ('2007-09-28 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-09-28 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-09-28 22:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT CONVERT_TZ('2007-09-29 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-09-29 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-09-29 22:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT CONVERT_TZ('2007-10-05 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-05 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-05 22:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT CONVERT_TZ('2007-10-06 10:00:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-06 10:00:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-06 22:00:00                          |
+----------------------------------------------+
1 row in set (0.01 sec)
 
mysql> SELECT CONVERT_TZ('2007-10-05 22:53:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-05 22:53:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-06 10:53:00                          |
+----------------------------------------------+
 
mysql> SELECT CONVERT_TZ('2007-10-06 22:53:00','UTC','NZ');
+----------------------------------------------+
| CONVERT_TZ('2007-10-06 22:53:00','UTC','NZ') |
+----------------------------------------------+
| 2007-10-07 11:53:00                          |
+----------------------------------------------+
1 row in set (0.01 sec)