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`)



source to share

1 answer

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.



All Articles