[MDEV-9608] FROM_UNIXTIME same date time from two different timestamp Created: 2016-02-22  Updated: 2016-02-22  Resolved: 2016-02-22

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.0.23-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Enne Fayen Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 64 bit

Attachments: PNG File image.png     PNG File image2.png    

 Description   

I am having a weird problem with mariadb. I am using FROM_UNIXTIME to convert timestamp to datetime. The problem is two different timestamp is resulting to same datetime. How can it be possible?

Here what I got from mariadb-

SELECT from_unixtime( '1456017720' ) , from_unixtime( '1456021320' )

Output on my timezone-

2016-02-20 23:22:00 2016-02-20 23:22:00

Another example:

SELECT from_unixtime( '1456016700' ) , from_unixtime( '1456020300' )

Output on my timezone-

2016-02-20 23:05:00 2016-02-20 23:05:00

This is wrong because no two different timestamp should return same date time.

I have attached screenshot proof of output that I got via phpmyadmin.

Version info-

innodb_version 5.6.26-76.0 
protocol_version 10 
slave_type_conversions version 10.0.23-MariaDB 
version_comment MariaDB Server 
version_compile_machine x86_64 
version_compile_os Linux 
version_malloc_library bundled jemalloc



 Comments   
Comment by Elena Stepanova [ 2016-02-22 ]

Did you try to run the same queries via MySQL client, not using phpmyadmin? It would be good to rule out that it gets messed up in the tool or connector.

Comment by Sergei Golubchik [ 2016-02-22 ]

This is normal and not a bug. TIMESTAMP values are defined as number of second since 1970-01-01 00:00:00 UTC. It always increases by one for every second that passed.

DATETIME is different. It's the time in your current time zone and it does not always increase. In particular, when you switch to Daylight Saving Time the time in your time zone goes one hour back. But TIMESTAMP value does not. So it is, indeed, possible to have two different TIMESTAMP values that correspond to the "same" DATETIME value. Because your clock have, indeed, displayed the same time twice.

You, apparently, have switched to DST at 2016-02-21 00:00:00, and have lived the hour of
2016-02-20 23:00:00–23:59:59 twice. This was the case, for example, in Brazil.

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