Search code examples
sqloracle-databasedata-conversion

Convert minutes to Hours:Minutes in Oracle SQL


Morning Team,

I have an Oracle SQL script that is calculating from creation of an event and how many minutes old compared to the systimestamp. I need to convert the minutes, which are coming out as 120 for 2 hour for example, into Hours:Minutes version, i.e. 2:00

I'm struggling with that part and would like to ask if someone could help? My current code for the calculation is:

(ROUND(((cast(systimestamp as date) - cast(n.createdttm as date)))*1440,0)) "Minutes old",

I'm sure it's something simple but with all my fiddling I am not able to get it.

Thank you


Solution

  • It looks like createdttm is a timestamp, so you can just subtract:

    systimestamp - createdtm
    

    ... to get an interval value like +000000000 02:00:00.00000. You can't format that directly, but you can either extract the various elements and concatenate those back together, or treat it as a string and cut out the bits you want.

    If you only want the time part and it will always be less than a day you can just do:

    substr(systimestamp - createdtm, 12, 5)
    
    02:00
    

    But if it can go over 24 hours then you probably want the day part too, which you could still get just with substr (and maybe replace to change the space to another colon) if you know it can never be more than 2 days:

    substr(systimestamp - createdtm, 10, 7)
    
    0 02:00
    

    That's unlikely to be a safe assumption though, so instead you could extract the number of days and concatenate that:

    extract(day from (systimestamp - createdtm)) || ':' || substr(systimestamp - createdtm, 12, 5)
    
    0:02:00
    

    You could only show the number of days if it's non-zero, but that would probably be quite confusing to who/whatever is looking at the results; but if you really wanted to:

    case when extract(day from (systimestamp - createdtm)) > 0
         then extract(day from (systimestamp - createdtm)) || ':'
    end || substr(systimestamp - createdtm, 12, 5)
    
    02:00
    

    db<>fiddle with a few sample values.

    One thing to note is this effectively truncates the seconds off the time; your original attempt included round(), but that might not have been what you meant.