time calculation with date

User_A1BVT

Hi!

I have records with dates and I need to calculate the time between dates.

In the dataset I created a field calculating the time in seconds through the function TIMESTAMPDIFF(SQL_TSI_SECOND,data1,data2).

In my workbook I need to take this time and make an average indicator, but in HH:MI:SS format

So I have the average avg(time) where the result is 100.0758965 and I need the format to be 00:01:40, but the format I got was 0:1:40 using the following formula in my calculations

concat(concat(concat(cast(floor(avg(tempo)/60/60) as varchar(5)),':'),concat(cast(floor(mod(avg((time)/60/60)),3600)/60) as varchar(5)),':')),cast(floor(mod(mod(avg((time)/60/60)),3600),60)) as varchar(5)))

One detail, using the table object and listing the records line by line, the average is 0:1:40, but if I leave only the average calculated field, my return is 372:51:58.

If anyone has any advice on this, I'd appreciate it.

Shantaram-Oracle

Is this still an issue? If yes, could you please log a service request with Oracle Analytics cloud support on My Oracle Support portal?

Mallikarjuna Kuppauru-Oracle

Hi User_A1BVT,

Welcome to the Oracle Analytics Online Forum!

To enhance your experience and foster meaningful interactions, we encourage you to personalize your profile by setting up a display name and uploading a profile picture. Your display name will be how others recognize and engage with you in discussions, while a profile picture adds a personal touch to your forum presence.Take a moment to update your profile with a display name and an image representing you. Let's create a vibrant and engaging community together!

you can refer below link -

How to get the difference between datetimes in days, hours, minutes, and/or seconds in Oracle Database

Regards,

Arjun