1

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  
  • Be careful if your servers observe daylight savings. – Michael Green Oct 10 '14 at 12:48
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

| improve this answer | |
  • Thank you for your complete answer and taking the time to do it. This has been very helpful as I am new to this. – srh Oct 13 '14 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.