Converting duration (integer) to date format (hh:mm)

Hi Fynn,

I don't know in which scenario such a logic would make sense (maybe you can give some details on this. Would be interesting for me) and especially I am not sure what will for example happen if your minute value is > 1440 (so more than 24 hours) but you can try it like this:

CASE WHEN <%=input%>/60 >= 24 THEN 'Input too High'

  WHEN <%=input%> >= 60 THEN (CASE WHEN ROUND(<%=input%> / 60) < 10 THEN '0' || TO_STRING(ROUND(<%=input%>/60)) ELSE TO_STRING(ROUND(<%=input%>/60)) END) ||':'|| (CASE WHEN ROUND(<%=input%> % 60) < 10 THEN '0' || TO_STRING(ROUND(<%=input%>%60)) ELSE TO_STRING(ROUND(<%=input%>%60)) END)  

  ELSE '00:' || (CASE WHEN ROUND(<%=input%> % 60) < 10 THEN '0' || TO_STRING(ROUND(<%=input%>%60)) ELSE TO_STRING(ROUND(<%=input%>%60)) END)

  END

The result will be a string that shows hh:mm based on your input variable.

In order to convert it into a timestamp you could add TO_TIMESTAMP function but then you also need some sort of date information. Only the time in hh:mm is not enough.

Hi Dennis,

I need this kind of logic since my activity table contains a column of process relevant processing times (in minutes). I would like to list each Case with its specific processing time in a table in an Analysis. The processing time should have a nice format (hh:mm).

Back to my question: I think I already solved the problem myself now: In cannot be solved that easily. By using the TO_DATE and Concat Functions I was able to "Datify" my formerly Integers into Strings and then Date. However, the problem is, that some processing times are larger than one day or even a month.

Given the example:

3680 minutes = 2 days 13 hours 20 minutes --> larger than one day

578 minutes = 0 days 9 hours 38 minutes --> less than a day

When transforming strings into a Date with To_Date I need to specify what format the Date should have (e.g. %d:%H:%M). But since some processing times are larger than a day and some are not, the To_Date sometimes throws an error. To_Date is unable to display Dates, where the month or day equals 0. Which makes sense because there is no 0th month or day in our calendar.

I hope this makes a little sense. I am currently trying to solve this by using a Case_When to determine if my processing time is larger than a day or not.

Greetings from Germany,

Fynn

Hi,

I still not understand why you want to "datify" a duration instead of showing the strings as in your given example:

3680 minutes = 2 days 13 hours 20 minutes

578 minutes = 0 days 9 hours 38 minutes

To show durations in minutes in the way as in your example you can still use my logic from above.

I adjusted it a little bit to also cover days:

CASE WHEN <%=input%>/60 >= 24 THEN ROUND(<%=input%>/60/24) || ' days ' || TO_STRING(ROUND((<%=input%>-(1440*ROUND(<%=input%>/60/24))) / 60)) ||' hours '|| TO_STRING(ROUND((<%=input%>-(1440*ROUND(<%=input%>/60/24))) % 60)) || 'minutes' 

  WHEN <%=input%> >= 60 THEN TO_STRING(ROUND(<%=input%>/60)) ||' hours '|| TO_STRING(ROUND(<%=input%>%60)) || 'minutes'

  ELSE TO_STRING(ROUND(<%=input%>%60)) || 'minutes'

  END

BR

Dennis

Sorry, I forgot to mention that my ultimate goal is to be able to sort the table, in which my nicely formatted (hh:mm) is located. I want to have the option to sort by that specific column.

It is not possible to sort a column that contains Strings like " 2 days 13 hours 20 minutes". Please find the screenshot below. I already implemented a logic that is similar to yours. When sorting the column descending, this is what I get:

The sorting rather impacts the first number instead of the duration. I am pretty sure that sorting a column that has Date-Values will give me the desired result.

Ok understand.

I think only way to sort strings correctly would be to have a defined format like:

000 days 00 hours 00 minutes

e.g:

125 days 04 hours 56 minutes

011 days 23 hours 10 minutes

002 days 10 hours 05 minutes

...

As you mentioned there are too many different scenarios that can happen so that your format logic will not work for all of it.

I don't have a solution to do it via TO_DATE or TO_TIMESTAMP.

But maybe someone else can help you now.

I think with the additional comments it is more clear now what you want to achieve.