CODEWITHSUNDEEP
mysql
Beginner
Beginner

Reputation: 191

Is it possible to change the hour value of sql to own define value?

I was using SEC_TO_TIME to convert second to (hour and second). However, my result was wrong as my result should not be divided by 24 hours. My intention was to divide the hour that was extracted from my database and not just divide by 24 only. For example, my hour in a full day may be 8 or 8.5 only. Thank you.

CREATE TABLE test (
id INT(6) PRIMARY KEY,
DAY DOUBLE,
hourinafullday DOUBLE
);

INSERT INTO test (id, DAY, hourinafullday)
VALUES ('1', '8', '8'),('2', '16', '8'),('3', '8.5', '8.5'),('4', '24', '8'),('5', '4', '8'),('6', '4', '8'),('7', '4', '8') ;

SELECT * FROM test; 


SELECT CONCAT(
    FORMAT(
        FLOOR(
            SUM(
            (DAY/hourinafullday * hourinafullday *60*60) / (3600 * hourinafullday))
            ),0), 'day',
    TIME_FORMAT(
        SEC_TO_TIME(
            SUM(
            (DAY/hourinafullday * hourinafullday *60*60) % (3600 * hourinafullday))
            ),'%Hh:%im')) AS TotalLeaveAMonthHour FROM test;

My expected result should be 8day4h:00m instead of 8day12h:00m.

Upvotes: 0

Views: 49

Answers (1)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can try something below. First find the total seconds. Once you find the seconds it will be easy in general. But since you are calculating the hours per day differently, you have to create your dividend accordingly. I'm not sure whether my logic is correct for your requirement but you can change accordingly.

set @seconds = (select SUM(
            (DAY/hourinafullday * hourinafullday *60*60)) from test);

set @dividend = (select FLOOR(SUM((DAY/hourinafullday * hourinafullday *60*60)
                                / (3600 * hourinafullday))) 
                 from test);            

SELECT CONCAT(
            FLOOR(TIME_FORMAT(SEC_TO_TIME(@seconds), '%H') / @dividend), 'days',
            MOD(TIME_FORMAT(SEC_TO_TIME(@seconds), '%H'), @dividend ), 'h:',
            TIME_FORMAT(SEC_TO_TIME(@seconds), '%im:%ss')
        )
AS Result

OUTPUT

Result
______
8days4h:30m:00s

CHECK DEMO HERE

Upvotes: 1

Related Questions