Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database with two DATETIME values, and then I generate some new values from them:

tdiff = timediff(d1,d2)+0
day1 = truncate(tdiff/60.0/60.0/24.0,0)
day2 = datediff(d1,d2)+0

All three of those "variables" are FLOAT fields in the (temporary) database. I would expect that day1 and day2 would never differ by more than 1 or 2. However, there are certain values that seem to generate a wild answer - and they tend to be for certain values of tdiff. Calculating the value from tdiff shows that the calculations are right.

Conceptually, day2 should be difference between the dates from the DATETIME values, and day1 should be the number of 24-hour periods in the same time frame.

The biggest differences are at the maximum value for timediff: using calculations, the number of 24-hour periods is just over 97. However, using the DATEDIFF function shows a time span of as low as 60 days. This makes no sense.

There are also big differences when TIMEDIFF results in 26 24-hour periods: DATEDIFF reports 10 days instead - or when TIMEDIFF results in 30 24-hour periods, and DATEDIFF reports 11 days. There are also differences when TIMEDIFF reports 79 days, but DATEDIFF gives 29 days.

Here is example output - from a Ruby script (and in Ruby array format) - with columns d1,d2,TIMEDIFF,24-hr periods,DATEDIFF:

["2012-04-11 06:15:21", "2012-02-09 22:11:21", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:22", "2012-02-09 22:13:24", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:25", "2012-02-09 22:15:29", "8.38596e+06", "97", "62"]
["2012-04-11 05:47:25", "2012-03-31 05:07:01", "2.64402e+06", "30", "11"]
["2012-04-11 05:47:26", "2012-03-31 05:09:03", "2.64382e+06", "30", "11"]
["2012-04-11 05:47:27", "2012-03-31 05:27:21", "2.64201e+06", "30", "11"]
["2012-04-11 05:48:43", "2012-01-27 16:39:32", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:00", "2012-04-01 19:58:00", "2.2551e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:00:02", "2.25486e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:02:04", "2.25466e+06", "26", "10"]
["2012-04-11 05:49:05", "2012-04-01 20:04:04", "2.2545e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-04-01 20:06:06", "2.2543e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-01-27 17:36:07", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:07", "2012-04-01 20:08:10", "2.25406e+06", "26", "10"]
["2012-04-11 06:15:06", "2012-03-13 12:23:24", "6.89514e+06", "79", "29"]
["2012-04-11 06:15:07", "2012-03-13 12:25:25", "6.89494e+06", "79", "29"]

Why are these calculations so different? I am assuming that it is a lack of understanding of TIMEDIFF, but what am I missing?

share|improve this question
    
the 2012-04-11 is 10 days after 2012-04-01. There are not 26 24-hour periods between a point int time at 20122-04-01 and a point in time at 2012-04-11. –  miracle173 Apr 13 '12 at 8:12
    
True. But that's my question - why is it off? The calculation appears to be accurate (x/60/60/24) but the number of minutes (returned by TIMEDIFF) appears to be off quite badly. –  Mei Apr 13 '12 at 13:35

1 Answer 1

up vote 2 down vote accepted

The answer lies in fully understanding the TIMEDIFF function and what it returns as an integer.

First, let's consider TIMEDIFF in its native element and what it returns:

mysql> select timediff('0:0:0','1:0:0') as timediff;
+-----------+
| timediff  |
+-----------+
| -01:00:00 | 
+-----------+
1 row in set (0.00 sec)

Second, what does TIMEDIFF return in an integer context - done by adding zero:

mysql> select timediff('0:0:0','1:0:0')+0 as timediff2;
+--------------+
| timediff2    |
+--------------+
| 10000.000000 | 
+--------------+
1 row in set (0.01 sec)

Now, we use the TIME_TO_SEC function to return what we actually want:

mysql> select time_to_sec(timediff('0:0:0','1:0:0')+0)/60 as timediff3;
+-----------+
| timediff3 |
+-----------+
|   60.0000 | 
+-----------+
1 row in set (0.00 sec)

The integer value returned by TIMEDIFF is not minutes or seconds, but rather a base-10 representation of the actual time - so 01:00:00 becomes 10000, and 05:05:01 becomes 50501.

Instead of using TIME_TO_SEC one can also use TIMESTAMPDIFF instead of TIMEDIFF and set the units to whatever is desired:

mysql> select timestampdiff(MINUTE,'2012-04-13 0:0:0','2012-04-13 1:0:0') as timediff4;
+-----------+
| timediff4 |
+-----------+
|        60 | 
+-----------+
1 row in set (0.00 sec)

Instead of MINUTE, you can also use FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The time functions in MySQL (in this case, version 5.1) are all described in the manual. The online manual also links to other versions (such as MySQL 5.5) as well.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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