MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Tatiana A. Nurnberg Date:October 23 2008 3:08am
Subject:bzr commit into mysql-5.0-bugteam branch (azundris:2708) Bug#38455
View as plain text  
#At file:///misc/mysql/forest/38455/50-38455/

 2708 Tatiana A. Nurnberg	2008-10-23
      Bug#38455 - Timezone shifts cause bad query results for TIMESTAMP fields
      
      Not a bug; ChangeSet adds a test-case to ensure useful behaviour (which
      should provide workaround for customer who felt this was a bug) does not
      accidentally go away in future versions.
modified:
  mysql-test/r/func_time.result
  mysql-test/t/func_time.test

per-file messages:
  mysql-test/r/func_time.result
    test results
  mysql-test/t/func_time.test
    TIMESTAMP fields are kept in UTC, but are converted from current timezone
    on write and to it on read. This may cause several UTC-times to be folded
    into the same localtime (-string) due to DST. Add test to make sure we
    don't inadvertantly lose workaround using UNIX_TIMESTAMP().
=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result	2008-02-25 10:25:57 +0000
+++ b/mysql-test/r/func_time.result	2008-10-23 03:08:40 +0000
@@ -1308,4 +1308,54 @@ SELECT '2008-02-18' + INTERVAL 1 FRAC_SE
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1
 SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1
+SELECT @@LOCAL.TIME_ZONE INTO @tz;
+SET LOCAL TIME_ZONE = 'UTC';
+CREATE TABLE t1 ( f1 INT, f2 TIMESTAMP );
+INSERT INTO t1 VALUES (1,'2008-10-26 00:00:00');
+INSERT INTO t1 VALUES (2,'2008-10-26 00:30:00');
+INSERT INTO t1 VALUES (3,'2008-10-26 01:00:00');
+INSERT INTO t1 VALUES (4,'2008-10-26 01:30:00');
+INSERT INTO t1 VALUES (5,'2008-10-26 02:00:00');
+INSERT INTO t1 VALUES (6,'2008-10-26 02:30:00');
+UTC, 00:00, 00:30, 01:00, 01:30, 02:00, 02:30
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1;
+f1	f2	UNIX_TIMESTAMP(f2)
+1	2008-10-26 00:00:00	1224979200
+2	2008-10-26 00:30:00	1224981000
+3	2008-10-26 01:00:00	1224982800
+4	2008-10-26 01:30:00	1224984600
+5	2008-10-26 02:00:00	1224986400
+6	2008-10-26 02:30:00	1224988200
+SET LOCAL TIME_ZONE = 'MET';
+UTC, 02:00, 02:30, (Daylight Savings!), 02:00, 02:30, 03:00, 03:30
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1;
+f1	f2	UNIX_TIMESTAMP(f2)
+1	2008-10-26 02:00:00	1224979200
+2	2008-10-26 02:30:00	1224981000
+3	2008-10-26 02:00:00	1224982800
+4	2008-10-26 02:30:00	1224984600
+5	2008-10-26 03:00:00	1224986400
+6	2008-10-26 03:30:00	1224988200
+show that WHERE gets the correct rows despite of DST
+SELECT * FROM t1 WHERE f2 > '2008-10-26 02:00:00';
+f1	f2
+2	2008-10-26 02:30:00
+4	2008-10-26 02:30:00
+5	2008-10-26 03:00:00
+6	2008-10-26 03:30:00
+two source times are folded onto this, so we should get two rows here
+SELECT * FROM t1 WHERE f2 = '2008-10-26 02:30:00';
+f1	f2
+2	2008-10-26 02:30:00
+4	2008-10-26 02:30:00
+give local time, but compare as UTC, get one row (result in localtime!)
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1 WHERE UNIX_TIMESTAMP(f2)=UNIX_TIMESTAMP('2008-10-26 02:30:00');
+f1	f2	UNIX_TIMESTAMP(f2)
+2	2008-10-26 02:30:00	1224981000
+give UTC, compare as UTC, get one row (result in localtime!)
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1 WHERE UNIX_TIMESTAMP(f2)=UNIX_TIMESTAMP(CONVERT_TZ('2008-10-26 00:30:00','UTC','MET'));
+f1	f2	UNIX_TIMESTAMP(f2)
+2	2008-10-26 02:30:00	1224981000
+DROP TABLE t1;
+SET LOCAL TIME_ZONE = @tz;
 End of 5.0 tests

=== modified file 'mysql-test/t/func_time.test'
--- a/mysql-test/t/func_time.test	2008-02-27 15:12:08 +0000
+++ b/mysql-test/t/func_time.test	2008-10-23 03:08:40 +0000
@@ -829,4 +829,42 @@ SELECT '2008-02-18' + INTERVAL 1 FRAC_SE
 --error ER_PARSE_ERROR
 SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND;
 
+#
+# Bug #38455 - Timezone shifts cause bad query results for TIMESTAMP fields
+#
+
+SELECT @@LOCAL.TIME_ZONE INTO @tz;
+SET LOCAL TIME_ZONE = 'UTC';
+
+CREATE TABLE t1 ( f1 INT, f2 TIMESTAMP );
+
+INSERT INTO t1 VALUES (1,'2008-10-26 00:00:00');
+INSERT INTO t1 VALUES (2,'2008-10-26 00:30:00');
+INSERT INTO t1 VALUES (3,'2008-10-26 01:00:00');
+INSERT INTO t1 VALUES (4,'2008-10-26 01:30:00');
+INSERT INTO t1 VALUES (5,'2008-10-26 02:00:00');
+INSERT INTO t1 VALUES (6,'2008-10-26 02:30:00');
+
+--echo UTC, 00:00, 00:30, 01:00, 01:30, 02:00, 02:30
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1;
+
+SET LOCAL TIME_ZONE = 'MET';
+--echo UTC, 02:00, 02:30, (Daylight Savings!), 02:00, 02:30, 03:00, 03:30
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1;
+
+--echo show that WHERE gets the correct rows despite of DST
+SELECT * FROM t1 WHERE f2 > '2008-10-26 02:00:00';
+
+--echo two source times are folded onto this, so we should get two rows here
+SELECT * FROM t1 WHERE f2 = '2008-10-26 02:30:00';
+
+--echo give local time, but compare as UTC, get one row (result in localtime!)
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1 WHERE UNIX_TIMESTAMP(f2)=UNIX_TIMESTAMP('2008-10-26 02:30:00');
+
+--echo give UTC, compare as UTC, get one row (result in localtime!)
+SELECT f1,f2,UNIX_TIMESTAMP(f2) FROM t1 WHERE UNIX_TIMESTAMP(f2)=UNIX_TIMESTAMP(CONVERT_TZ('2008-10-26 00:30:00','UTC','MET'));
+
+DROP TABLE t1;
+SET LOCAL TIME_ZONE = @tz;
+
 --echo End of 5.0 tests

Thread
bzr commit into mysql-5.0-bugteam branch (azundris:2708) Bug#38455Tatiana A. Nurnberg23 Oct