CODEWITHSUNDEEP
mysqldatetime
Alternatex
Alternatex

Reputation: 1552

MySQL datetime difference in days, hours and minutes

I need the difference between two dates in days, hours and minutes. A current date (NOW()) and an end_date. And I need them in two separate queries that work just a bit differently.

The first one needs to return them in the following format as a single column:

-------------------------
|       time_left       |
-------------------------
| x days x hours x min  |
-------------------------

The second one needs to return them in three separate columns, making sure it returns 0s fo each column if the end_date has surpassed the current date:

-------------------------------------------
|  days_left  |   hrs_left  |   min_left  |
-------------------------------------------
|      x      |      x      |      x      |
-------------------------------------------

I have a solution for all of this but it's horrible. I'm sure someone has found at least a relatively clean way of solving this. Thanks ahead!

Upvotes: 2

Views: 7260

Answers (2)

Bill Newton
Bill Newton

Reputation: 45

I use this for years, months and days, but it could be edited to include more:

concat(timestampdiff(YEAR,'2020-01-01 11:00:00','2020-04-04 13:22:45'), ' years, ',
       timestampdiff(MONTH,'2020-01-01 11:00:00','2020-04-04 13:22:45' - INTERVAL timestampdiff(YEAR,'2020-01-01 11:00:00','2020-04-04 13:22:45') YEAR),' months, ',
       timestampdiff(DAY,'2020-01-01 11:00:00','2020-04-04 13:22:45' - INTERVAL timestampdiff(YEAR,'2020-01-01 11:00:00','2020-04-04 13:22:45') YEAR - INTERVAL timestampdiff(MONTH,'2020-01-01 11:00:00','2020-04-04 13:22:45' - INTERVAL timestampdiff(YEAR,'2020-01-01 11:00:00','2020-04-04 13:22:45') YEAR) MONTH),' days'),

Upvotes: 2

user3123035
user3123035

Reputation:

The one before was inaccurate, displaying a one day and 3 hours difference on what should have been an hour.

This one should be accurate with any times

SELECT 
    @diff:=ABS( UNIX_TIMESTAMP("2014-05-09 21:24:25") - UNIX_TIMESTAMP() ) , 
    CAST(@days := IF(@diff/86400 >= 1, floor(@diff / 86400 ),0) AS SIGNED) as days, 
    CAST(@hours := IF(@diff/3600 >= 1, floor((@diff:=@diff-@days*86400) / 3600),0) AS SIGNED) as hours, 
    CAST(@minutes := IF(@diff/60 >= 1, floor((@diff:=@diff-@hours*3600) / 60),0) AS SIGNED) as minutes, 
    CAST(@diff-@minutes*60 AS SIGNED) as seconds;

Explained:

  • First we calculate the absolute difference in seconds and put that in a variable
  • then we check if we get one or more of each (day hour minute), and if so we calculate how any we get and put that in a variable
  • for units after the largest, we do the same, but first we subtract the seconds allotted to the previous unit from our overall difference
  • Lastly we put whatever is left as the smallest unit, seconds

Upvotes: 5

Related Questions