Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10285

Summer Time Timezone and Timestamps comparison

    XMLWordPrintable

    Details

      Description

      Sometimes when timestamp is close to summer time begin/end point the exact comparison doesn't work. Sorry, I can't explain it well, here is the test case:

      DROP DATABASE IF EXISTS test ;
      CREATE DATABASE test DEFAULT CHARSET=utf8 ;
      USE test ;
       
      SET @@session.time_zone = 'SYSTEM' ;
      SET @OLD_TIME_ZONE=@@TIME_ZONE ;
      SET TIME_ZONE='+00:00' ;
       
      CREATE TABLE `tab` (
        `Name` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
        `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY `Name` (`Name`,`Timestamp`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      INSERT INTO `tab` VALUES 
      	('n1','2014-10-25 21:11:11'),
      	('n2','2014-12-22 21:22:22');
       
      SET TIME_ZONE=@OLD_TIME_ZONE ;
      

      When data is inserted:

      SELECT * FROM test.tab ;
       
      # Returns 2 rows:
      # +------+-----------------------+
      # | Name |       Timestamp       |
      # +------+-----------------------+
      # |  n1  |  2014-10-26 01:11:11  |
      # +------+-----------------------+
      # |  n2  |  2014-12-23 00:22:22  |
      # +------+-----------------------+
      

      SELECT * FROM test.tab WHERE 
      Name = 'n2' AND
      Timestamp = '2014-12-23 00:22:22' ;
       
      # Returns 1 row:
      # +------+-----------------------+
      # | Name |       Timestamp       |
      # +------+-----------------------+
      # |  n2  |  2014-12-23 00:22:22  |
      # +------+-----------------------+
      

      SELECT * FROM test.tab WHERE 
      Name = 'n1' AND
      Timestamp = '2014-10-26 01:11:11' ;
       
      # Returns 0 rows!
      # How so?
      

      My system timezone is Europe/Moscow. The daylight saving time ends on October 26, 2014 at 2:00 for this timezone.
      Small changes in the first group of queries make it possible to bypass this. But I don't understand why this even happened with many rows in my db. A bug probably?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              user578 user578
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: