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".
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.
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.
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.
TIME_FORMAT(SEC_TO_TIME(task_records.time_spent),'%Hh %im')
Documentation is your friend:
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
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
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