Convert minutes to Hours:Minutes in Oracle SQL-CodePudding

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

CodePudding user response：

You can create an INTERVAL form the minutes.

select numtodsinterval(120, 'minute') from dual

Or create a datetime from the minutes and convert its time part to a string of hours and minutes.

select to_char(trunc(sysdate)   interval '1' minute * 120, 'hh24:mi') from dual

CodePudding user response：

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.

CodePudding user response：

If u want to convert minutes to hours and minutes. If x is the number of minutes (such as 350):

TO_CHAR ( FLOOR (x / 60)) || ':'
                  || TO_CHAR ( MOD (x, 60)
                         , 'FM00'
                     )

If u want to convert hours and minutes to minutes.

SELECT  (TRUNC (x) * 60)   
    ( (MOD (x, 1)
    * 100 
    ) 
FROM    dual;

where x is a NUMBER. If you have a sting, s, instead, use TO_NUMBER (s) in place of x.