Search code examples
sqlvertica

Converting Minutes Column to Days, Hours and Minutes SQL

I have a column like this -

XXX_2019
234
2142
1423
4634
7886
3143
3243

I want to output

XXX_2019
3 hours, 54 minutes
1 days, 11 hours, 42 minutes
23 hours, 43 minutes

This is not Date time. It's just a minutes column. I am using Vertica.

Solution

  • You can do this directly. Something like this:

    select trim(leading ', ' from
     (case when XXX_2019 > 24*60 then ', ' || floor(xxx_2019 / (24*60)) || ' days' else '' end) ||
     (case when XXX_2019 > 60 then ', ' || floor((xxx_2019 % (24*60)) / 60) || ' hours' else '' end) ||
     (', ' || xxx_2019 % 60 || ' minutes')
    )
from (values (12345), (123), (12)) v(xxx_2019);

    Here is a db<>fiddle (using Postgres).