CODEWITHSUNDEEP
mysql
v4nz
v4nz

Reputation: 115

Subtracting timestamp discrepancy

I have a persistent process which examines a row in a database table and spawns a child process on some interval, based on the last time the child process was spawned (basically, a pretty simple CRON replacement).

I had originally implemented it, simply subtracting the current timestamp from the value stored in the last_start column. CURRENT_TIMESTAMP - last_start. This seemed to work, however, closer inspection revealed that the timestamp subtraction was acting rather strangely.

It seemed that as we crossed the minute barrier (so, the current time ticked over to a new minute), the "difference" that was calculated would jump up by 40 (eg. from 59 up to 100). This looks like "1:00" - sort of - until we get to a state like that shown below, where what would be the "seconds" part of it goes above 60 (it's 95 in the example below).

I solved the problem by using the TIMESTAMPDIFF method to do the subtraction (thanks Stack Overflow!). But it's not clear to me why this fell short 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 anyone explain to me what's happening when I just subtract timestamps, as I was doing?

Edit: The table schema is as follows:

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

Upvotes: 3

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Why is this going wrong? Well, you would expect that a database (or any other software product) that had a data type called TIMESTAMP and a "constant" called CURRENT_TIMESTAMP would represent the latter using the former.

But no. That is not MySQL. CURRENT_TIMESTAMP is a synonym for now(), whose type is context-dependent. Happily, the documentation explains this quite clearly:

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

Of course, you have to figure out what the different contexts are. A hint. The use of - is a "numeric context".

What happens is the following. MySQL sees CURRENT_TIMESTAMP and puts in the current time. But how? It sees the - and determines that it goes in as a number. Then it comes across last_start. Well, this now has to be converted to a number too. And, guess what? You get funky behavior.

Upvotes: 2

Related Questions