Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert seconds to human readable time duration

How would I best convert 90060 (seconds) to a string of "25h 1m"?

Currently I'm doing this in SQL:

SELECT 
  IF(
    HOUR(
      sec_to_time(
        sum(time_to_sec(task_records.time_spent))
      )
    ) > 0, 
    CONCAT(
      HOUR(sec_to_time(sum(time_to_sec(task_records.time_spent)))), 
      'h ', 
      MINUTE(sec_to_time(sum(time_to_sec(task_records.time_spent)))),
      'm'
    ), 
    CONCAT(
      MINUTE(sec_to_time(sum(time_to_sec(task_records.time_spent)))),
      'm'
    )
  ) as time
FROM myTable;

But I'm not sure it's the most convenient method :-)

I'm open to suggestions on doing this both in SQL (differently than I'm already doing) or in PHP.

EDIT:

Examples of desired strings: "5m", "40m", "1h 35m", "45h" "46h 12m".

like image 352
Henno Avatar asked Nov 19 '11 11:11

Henno


People also ask

How do you convert seconds to HH MM SS?

Find the number of whole hours by dividing the number of seconds by 3,600. The number to the left of the decimal point is the number of whole hours. The number to the right of the decimal point is the number of partial hours.

How do I convert seconds to hours?

Since there are 3,600 seconds in an hour, the easiest way to convert seconds to hours is to divide by 3,600. For instance, 4,500 seconds divided by 3,600 is 1.25 hours. If you end up with a fraction of an hour and want to convert it into minutes, multiply the fraction by 60.

How do you calculate time from seconds?

time in seconds = time in minutes * 60 = time in hours * 3600. time in minutes = time in seconds / 60 = time in hours * 60. time in hours = time in minutes / 60 = time in seconds / 3600.


2 Answers

TIME_FORMAT(SEC_TO_TIME(task_records.time_spent),'%Hh %im')

Documentation is your friend:

  • http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

According to comment:

DROP FUNCTION IF EXISTS GET_HOUR_MINUTES;
CREATE FUNCTION GET_HOUR_MINUTES(seconds INT)
  RETURNS VARCHAR(16)

  BEGIN
  DECLARE result VARCHAR(16);
  IF seconds >= 3600 THEN SET result = TIME_FORMAT(SEC_TO_TIME(seconds),'%kh %lm');
  ELSE SET result = TIME_FORMAT(SEC_TO_TIME(seconds),'%lm');
  RETURN result;
  END

DELIMETER ;

Usage:

SELECT GET_HOUR_MINUTES(task_records.time_spent) FROM table
like image 119
Peter Avatar answered Oct 03 '22 14:10

Peter


you can use predefined function sec_to_time()

sec_to_time(number_of_seconds)

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sec-to-time

like image 29
rrawat Avatar answered Oct 03 '22 14:10

rrawat