Bug #76202 wrong time calculation

• Submitted: 6 Mar 2015 22:24
• Modified: 7 Mar 2015 8:12
• Reporter: Albert Bonomo
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: XAMPP for Linux 5.5.19-0!
• OS: Linux
• CPU Architecture: Any
• Tags: time, timestamp

[6 Mar 2015 22:24] Albert Bonomo

Description:
I worked with the following instruction:

SELECT FROM table CURRENT_TIMESTAMP-state_time

state_time being a value assigned by me with an instruction such as:

UPDATE table SET state_time = CURRENT_TIMESTAMP

Every so often, the difference gives a value 40 seconds greater than the calculated value.
I think this error occurs when between state_time and CURRENT_TIMESTAMP there is a change in the minutes.
examples:

06/03/2015 18:43:50
06/03/2015 18:44:06
obtained 56
calculated 16

06/03/2015 17:32:53
06/03/2015 17:33:01
obtained 48
calculated 8

Precisely, 40 seconds is what is missing from one minute to get to 100 seconds.
The SQL is considering the minutes as decimal numbers of 100 seconds,in such cases.

How to repeat:
UPDATE table SET state_time = CURRENT_TIMESTAMP

Loop every second or so
SELECT FROM table CURRENT_TIMESTAMP-state_time
and see what you get

You sould get 1,2,3,4,...
Eventualy you will get 1,2,3,44,45,46,....

Suggested fix:
I have no idea.

[7 Mar 2015 8:12] MySQL Verification Team

This is not a bug.  You cannot just subtract dates, as mysql tries to treat them as numbers.   You must use appropriate function.  

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

Try this to see the result:

drop table if exists t1;
create table t1(a datetime);
insert into t1 values(current_timestamp);
select a from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;
do sleep(3);
select current_timestamp-a,unix_timestamp()-unix_timestamp(a) from t1;