Menu
This is my query:
SELECT TIMEDIFF(end_time,start_time) AS "total" FROM `metrics`;
which gives me:
116:12:10
meaning 116 hours, 12 minutes and 10 seconds.
Instead, I want it to say 4 days 20 hours, 12 minutes etc
833
MySQL SEC_TO_TIME() Function The SEC_TO_TIME() function returns a time value (in format HH:MM:SS) based on the specified seconds.
mysql> SELECT t1, -> TIME_TO_SEC(t1) AS 'seconds', -> TIME_TO_SEC(t1)/60 AS 'minutes', -> TIME_TO_SEC(t1)/(60*60) AS 'hours', -> TIME_TO_SEC(t1)/(24*60*60) AS 'days' -> FROM time_val; +----------+---------+---------+-------+------+ | t1 | seconds | minutes | hours | days | +----------+---------+---------+-------+------ ...
MySQL SEC_TO_TIME() returns a time value by converting the seconds specified in the argument. The return value is in hours, minutes and seconds. The range of the result is in the time data type.
Change the curdate() (current date) format in MySQL The current date format is 'YYYY-mm-dd'. To change current date format, you can use date_format().
SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')) / 24), ' days ',
MOD(HOUR(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')), 24), ' hours ',
MINUTE(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')), ' minutes')
Use your end_time and start_time for the fixed datetime values in my example
As per the two comments below, this solution only works for date differences within 35 days. If you know there are more than 35 days between start and end, i.e. differences over a month, don't use it. Other answers here using TIMESTAMPDIFF will work.
91
SELECT
CONCAT(
TIMESTAMPDIFF(day,'2001-01-01 00:00:00','2001-01-02 23:10:00') , ' dagen ',
MOD( TIMESTAMPDIFF(hour,'2001-01-01 00:00:00','2001-01-02 23:10:00'), 24), ' uren ',
MOD( TIMESTAMPDIFF(minute,'2001-01-01 00:00:00','2001-01-02 23:10:00'), 60), ' minuten '
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
