Minutes into hours or days depending on value-CodePudding

I have a value which represents minutes. I am creating another column and i want it to print the value like

1 minute
2 minutes
1 hour
2 hours
1 day
2 days

SELECT 
    @value:= (select 450), --450 is minutes
    CASE
        WHEN @value > 1440 THEN CONCAT(@value % 1440, ' days') -- number of days like 2 days
        WHEN @value > 59 THEN CONCAT(@value % 60), ' hours')
        WHEN @value > 1 THEN CONCAT(@value, ' minutes')
        WHEN @value > 0 THEN 'Less than a minute'
    END
FROM
    customer

My query is not working as expected

I got it working with the following query

set @minutes_variable= 2879;
SELECT 
    CASE
        WHEN
            @minutes_variable > 2879
        THEN
            CONCAT((FLOOR(@minutes_variable / 1440)),
                    ' days')
        WHEN
            @minutes_variable >= 1440
        THEN
            CONCAT((FLOOR(@minutes_variable / 1440)),
                    ' day')
        WHEN @minutes_variable > 119 THEN CONCAT(FLOOR(@minutes_variable / 60), ' hours')
        WHEN @minutes_variable > 1 THEN CONCAT(@minutes_variable, ' minutes')
        WHEN @minutes_variable >= 1 THEN CONCAT('1 minute')
        WHEN @minutes_variable >= 0 THEN 'Less than a minute'
    END;

CodePudding user response：

Your formulas are slightly off. Given that an input could have days, hours, and/or minutes components, I suggest just printing out all possible units.

SELECT 
    @value:= (select 450), --450 is minutes
    CONCAT_WS(', ', CONCAT(FLOOR(@value / 1440), ' days'),
                    CONCAT(FLOOR((@value % 1440) / 60), ' hours'),
                    CONCAT(FLOOR(@value % 60), ' minutes')) AS label
FROM customer;

For an input of 3001 minutes, the above logic generates:

2 days, 2 hours, 1 minutes

CodePudding user response：

The code below achieves what you want. Instead of minutes_variable, you can use the actual value selected from the table row.

    set @minutes_variable= 450 ;
SELECT 
    CASE
        WHEN @minutes_variable >= 1440 THEN (IF((@minutes_variable div 1440)=1, '1 day',CONCAT((@minutes_variable div 1440), ' days') ))  
        WHEN @minutes_variable > 59 THEN (IF((@minutes_variable div 60)=1,'1 hour',CONCAT((@minutes_variable div 60), ' hours'))) 
        WHEN @minutes_variable > 1 THEN CONCAT(@minutes_variable, ' minutes')
                 WHEN @minutes_variable = 1 THEN '1 minute'
        WHEN @minutes_variable = 0 THEN 'Less than a minute'
    END