Your query is actually never technically correct, but it's across the top of the hour when you notice the error, and spanning midnight is likely to be even worse.
What your query is actually asking the server to do is to subtract the values of two integers (possibly after an initial implicit cast, if you're storing them as strings), and then implicitly casting result of that subtraction into the data type of TIME
, then and applying the TIME_TO_SEC()
function to it.
To illustrate the error:
mysql> SELECT 20140908110000 - 20140908105500;
+---------------------------------+
| 20140908110000 - 20140908105500 |
+---------------------------------+
| 4500 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST(20140908110000 - 20140908105500 AS TIME);
+-----------------------------------------------+
| CAST(20140908110000 - 20140908105500 AS TIME) |
+-----------------------------------------------+
| 00:45:00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIME_TO_SEC(CAST(20140908110000 - 20140908105500 AS TIME));
+------------------------------------------------------------+
| TIME_TO_SEC(CAST(20140908110000 - 20140908105500 AS TIME)) |
+------------------------------------------------------------+
| 2700 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
What you need to do, of course, is to properly convert both values to datetime values and then use a time function to calculate the difference.
Converting a string of digits into a proper DATETIME
value can be done with the DATE_FORMAT()
function:
mysql> SELECT STR_TO_DATE('20140908105500','%Y%m%d%H%i%s');
+----------------------------------------------+
| STR_TO_DATE('20140908105500','%Y%m%d%H%i%s') |
+----------------------------------------------+
| 2014-09-08 10:55:00 |
+----------------------------------------------+
1 row in set (0.00 sec)
Apply this to both values and then use TIMESTAMPDIFF()
to subtract. Note that the order of the arguments is counterintuitive; if you want a positive value, you put the larger value last, not first.
mysql> SELECT TIMESTAMPDIFF(
SECOND,
STR_TO_DATE('20140908105500','%Y%m%d%H%i%s'),
STR_TO_DATE('20140908110000','%Y%m%d%H%i%s')
);
+------+
| 300 |
+------+
1 row in set (0.00 sec)
In your query, it would look like this:
TIMESTAMPDIFF(SECOND,
STR_TO_DATE(a.rt1,'%Y%m%d%H%i%s'),
STR_TO_DATE(b.rt1,'%Y%m%d%H%i%s')) AS rtdiff
That's a much more correct approach. If your timestamps are in a time zone other than UTC, you still have a strange issue twice per year, which can't entirely be fixed, because in fall, when the clock goes backwards, there is a period of time when an hour is duplicated (the spring time change could be fixed by converting both values from their native timestamps to UTC before doing the subtraction using CONVERT_TZ()
but the fall is just stuck on ambiguous).
An even more correct approach would be to use proper TIMESTAMP
or DATETIME
columns instead of whatever you're currently doing, which I assume must be either VARCHAR
or BIGINT
... and to use UTC instead of local time, if you aren't already.
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html