Convert seconds to days, hours, minutes, seconds (MySQL)

Question

Example:

seconds ="1015557";

Result should be:

11days 18h:05m:57s

How can do this in MySQL?

possible duplicate: http://stackoverflow.com/questions/8193868/convert-seconds-to-human-readable-time-duration — Aleksandar Varicak, Dec 26 '16 at 11:55

score 11 · Answer 1 · answered Dec 26 '16 at 12:05

select concat(
    format(floor(s / @day),0),
    'days ',
    time_format(sec_to_time(s % @day),'%Hh:%im:%ss') 
  ) formatted_date
from (select 1015557 s) t, (select @day = 3600 * 24);

produces:

+--------------------+                                                                                                                               
| days               |                                                                                                                               
+--------------------+                                                                                                                               
| 11days 18h:05m:57s |                                                                                                                               
+--------------------+

score 11 · Answer 2 · answered Dec 26 '16 at 12:24

11

You can use a query like this:

SELECT
  DATE_FORMAT(date('1970-12-31 23:59:59')
   + interval 1015557 second,'%j days %Hh:%im:%ss') as result;

sample

mysql>     SELECT
    ->       DATE_FORMAT(date('1970-12-31 23:59:59')
    ->        + interval 1015557 second,'%j days %Hh:%im:%ss') as result;
+----------------------+
| result               |
+----------------------+
| 011 days 18h:05m:57s |
+----------------------+
1 row in set (0,00 sec)

mysql>

answered Dec 26 '16 at 12:24

Bernd Buffen

14,525
2
24
39

1

Hello @Bernd, i thank you a lot for this solution. But i found an error with this value: 81865. I get: 365 days 22h:44m:25s - I think that this is an error. Do you have an idea? Here the fiddle: http://sqlfiddle.com/#!9/9eecb/89478 – achillix May 12 '19 at 08:06

score 9 · Answer 3 · answered Dec 26 '16 at 14:36

You can try this out:

SET @seconds = 1015557;
SELECT CONCAT(
            FLOOR(TIME_FORMAT(SEC_TO_TIME(@seconds), '%H') / 24), 'days ',
            MOD(TIME_FORMAT(SEC_TO_TIME(@seconds), '%H'), 24), 'h:',
            TIME_FORMAT(SEC_TO_TIME(@seconds), '%im:%ss')
        )
AS Result

Result should be:

11days 18h:05m:57s

Hopefully this helps!.

yangkwang · Answer 4 · 2022-06-10T03:35:04.997

3

SELECT *, 
CONCAT( FLOOR(sec/3600),"hour ", FLOOR(MOD(sec,3600)/60),"min",MOD(sec,60),"sec") 
FROM table_1

edited Jun 10 '22 at 03:35

answered Sep 24 '20 at 06:56

yangkwang

33
5

2

Hi, please edit your answer to put the code preferably in a Stack Snippet. – Inazense Sep 24 '20 at 09:03
2

Please take a few minutes to read [answer] to get some useful tips on answering questions. Esp. the part "fuller explanations are better". Also, you need a very good reason to answer an old, well answered question. Without any explanation in that direction answers like this tend to get deleted in the review process. – Gert Arnold Sep 24 '20 at 20:23
2

This does not include "days". – ashleedawg Jan 14 '21 at 13:22
Sorry, I just edited it – yangkwang Jun 10 '22 at 03:36

score 1 · Answer 5 · answered Apr 26 '22 at 14:00

If one is interested in fraction of seconds, the following combines the responses from Kent Aguilar and Steven Yang.

SELECT CONCAT(
  FLOOR(TIME_FORMAT(SEC_TO_TIME(@seconds), '%H') / 24), 'days ',
  MOD(TIME_FORMAT(SEC_TO_TIME(@seconds), '%H'), 24), 'h:',
  FLOOR(MOD(@seconds,3600)/60), 'm:',
  MOD(@seconds,60), 's'
) AS Result

Convert seconds to days, hours, minutes, seconds (MySQL)

5 Answers5

Linked

Related