CODEWITHSUNDEEP
mysqldatediff
Josef
Josef

Reputation: 2726

MySQL calculate date diff if greater than 35 days

how to calculate datediff between two dates if difference is greater than 35 days?

This is my query:

SELECT CONCAT(IF('2017-08-01 10:00:00' < NOW(), '-', ''), FLOOR(HOUR(TIMEDIFF(NOW(), '2017-08-01 10:00:00')) / 24), ' days ',
MOD(HOUR(TIMEDIFF(NOW(),'2017-08-01 10:00:00')), 24), ' hours ', MINUTE(TIMEDIFF(NOW(),'2017-08-01 10:00:00')), ' minutes') AS TimeLeft

But it shows wrong calculation.

+------------------------------+
| TimeLeft                     |
+------------------------------+
| -34 days 22 hours 59 minutes |
+------------------------------+

It should be at least 100 day difference.

Upvotes: 1

Views: 722

Answers (2)

John Doe
John Doe

Reputation: 21

timediff has a limitation of max. 838:59:59 or about 35 days that you have found out. For queries with greater differences, use "TIMESTAMPDIFF" instead.

Example: SELECT id, tstamp, TIMESTAMPDIFF(MINUTE, tstamp, '2037-09-07 07:59:59') FROM work;

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562250

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timediff says:

The result returned by TIMEDIFF() is limited to the range allowed for TIME values.

https://dev.mysql.com/doc/refman/5.7/en/time.html says:

TIME values may range from '-838:59:59' to '838:59:59'.

Then we realize that

FLOOR(838 hours / 24 hours) = 34

Here's an alternative that works (at least until 2038):

mysql> select concat(
  ceil(tdiff/24/60/60), ' days, ',
  floor(mod(abs(tdiff)/60/60, 24)), ' hours, ',
  floor(mod(abs(tdiff)/60, 60)), ' minutes') as t
from (select unix_timestamp('2017-08-01 10:00:00') - unix_timestamp(now()) as tdiff) as t;
+---------------------------------+
| t                               |
+---------------------------------+
| -100 days, 13 hours, 10 minutes |
+---------------------------------+

Here's another idea:

mysql> select to_days('1970-01-01')-to_days(td) as days, time(td) as hhmmss
 from (select from_unixtime(abs(unix_timestamp('2017-08-01 10:00:00') - unix_timestamp(now()))); 
+------+----------+
| days | hhmmss   |
+------+----------+
| -100 | 13:18:12 |
+------+----------+

Upvotes: 1

Related Questions