Calculating difference between consecutive timestamps. Error when i reach the hour mark

Question

I am using MySQL

I have a table that contains among other things an id a time in the format date:time (YYYYhhmmss) and a readingvalue. The difference between each reading value is 300 seconds but this may vary slightly so i need to calculate the difference between each timestamp and then add the difference in readingvalue for that allotted time.

Below is what i have tried and this works until i get to the a timestamp of 10:55:00 to 11:00:00. The difference in seconds this gives is not 300 but 2700. Does anyone know how to fix this. Thank you

SELECT 
    a.id as id1,   
    b.id as id2,    
    a.rt1 as rt1,   
    b.rt1 as rt2,   
    a.avg_readingvalue as arv1,  
    b.avg_readingvalue as arv2,  
    time_to_sec(b.rt1-a.rt1) as rtdiff,  
    b.avg_readingvalue+a.avg_readingvalue as arv21  
FROM table1 a   
INNER JOIN table1 b   
    ON b.id=(a.id+1)   
ORDER BY by a.id  

Answer 1

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