[MDEV-10285] Summer Time Timezone and Timestamps comparison Created: 2016-06-24  Updated: 2021-11-17

Status: Open
Project: MariaDB Server
Component/s: Locale Settings, Time zones
Affects Version/s: 10.0.25, 10.1.14
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: user578 Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: DST, summertime, timestamp, timezones
Environment:

Ubuntu 16.04
Mariadb server 10.1.14
System timezone: Europe/Moscow

Issue Links:
Relates
relates to CONJ-280 Wrong data returned Open

 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?

 Comments   
Comment by Elena Stepanova [ 2016-06-25 ]

You can find the explanation why it is happening in the last comment to MDEV-9608 (comment by serg).
To verify that it applies to your case, you can run 

SELECT UNIX_TIMESTAMP(`Timestamp`), UNIX_TIMESTAMP('2014-10-26 01:11:11') FROM test.tab WHERE `Name` = 'n2';

You'll see that they are different, with 1 hour difference.
In your query comparison happens in timestamp context, so the result is what it is. If the values were compared as strings, they would match, e.g.

MariaDB [test]> SELECT * FROM test.tab WHERE  Name = 'n1' AND CAST(Timestamp AS CHAR(19)) = '2014-10-26 01:11:11';
 
+------+---------------------+
 
| Name | Timestamp           |
 
+------+---------------------+
 
| n1   | 2014-10-26 01:11:11 |
 
+------+---------------------+
 
1 row in set (0.00 sec)

That said, even if I know the reason by now, it still doesn't look quite right to me, and certainly confusing. I'll assign it to serg with the following question:
If the server is so smart to know that two different unix timestamps can correspond to the same datetime value, is it maybe possible to use this knowledge while comparing timestamps?

Comment by Sergei Golubchik [ 2016-06-26 ]

Okay, I tend to agree with that. Let's keep the bug open and fix ti eventually.

Generated at Thu Feb 08 07:41:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.