Subtracting timestamp mismatch
I have a persistent process that examines a row in a database table and spawns a child process at some interval, depending on when the child process was exited (basically a pretty simple CRON replacement).
I originally implemented it by simply subtracting the current timestamp from the value stored in the last_start column. CURRENT_TIMESTAMP - last_start
... This seems to have worked, however, closer inspection revealed that subtracting the timestamp was rather odd.
It seemed that when we crossed the minute barrier (so the current time was counted down to the new minute), the "difference" that was calculated would jump by 40 (for example, from 59 to 100). It's like "1:00" - sort of - until we get to the state shown below, where the "second" portion of it is above 60 (that's 95 in the example below).
I solved the problem by using a method TIMESTAMPDIFF
to perform the subtraction (thanks Stack Overflow!). But it's not clear to me why it missed it in the first place.
mysql db_name -e 'select CURRENT_TIMESTAMP, last_start, CURRENT_TIMESTAMP - last_start , TIMESTAMPDIFF(SECOND, last_start, CURRENT_TIMESTAMP) from tasks where id = 3' Thu Sep 11 09:49:17 2014
CURRENT_TIMESTAMP last_start CURRENT_TIMESTAMP - last_start TIMESTAMPDIFF(SECOND, last_start, CURRENT_TIMESTAMP)
2014-09-11 09:49:17 2014-09-11 09:37:22 1195 715
Can someone please explain to me what happens when I just subtract the timestamps as I did?
Edit: The table schema looks like this:
CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`task` char(128) NOT NULL,
`run_count` int(10) unsigned NOT NULL DEFAULT '0',
`domina` char(128) DEFAULT NULL,
`slave` char(128) DEFAULT NULL,
`last_start` timestamp NULL DEFAULT NULL,
`last_end` timestamp NULL DEFAULT NULL,
`avg_duration` int(10) unsigned NOT NULL DEFAULT '0',
`last_status` char(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
source to share
Why is this not happening? Well, you'd expect a database (or any other piece of software) that had a data type named TIMESTAMP
and a "constant" named CURRENT_TIMESTAMP
to represent the latter using the previous one.
But no. This is not MySQL. CURRENT_TIMESTAMP
is a synonym now()
, the type of which depends on the context. Fortunately, the documentation explains it pretty clearly:
Returns the current date and time as a value in 'YYYY-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric context.
Of course, you need to figure out what the different contexts are. Hint. Usage -
is "numeric context".
What happens is this. MySQL sees CURRENT_TIMESTAMP
and places the current time. But how? He sees -
and determines what he is among. Then comes last_start
. Well, now that needs to be converted to a number too. And guess what? You get funky behavior.
source to share