How to present a duration (in seconds) stored in a BIGINT in the format "hh:mm:ss"?

[João Pedro Gomes Ramalho]

I've already tried modifying the query to fetch the data using SQLite with something like this:
printf('%02d:%02d:%02d', AVG(time_in_status) / 3600, (AVG(time_in_status) % 3600) / 60, AVG(time_in_status) % 60)

but without much success.

I'm using Atlassian Datalake/Analytics to try to display this duration.
Has anybody already went through this process? I'm open to suggestions :)

[Pears Whims · Accepted solution]

Hi @João Pedro Gomes Ramalho ,

Can You please try one of those approaches, and let me know if any of them work.

strftime

SQL lite can support a built-in function strftime:

SELECT
strftime('%H:%M:%S', AVG(time_in_status), 'unixepoch') AS formatted_duration
FROM your_table;

casting

Your original query may not be working exactly due to casting issues. Consider using explicit casting

SELECT
printf('%02d:%02d:%02d',
CAST(AVG(time_in_status) AS INTEGER) / 3600,
(CAST(AVG(time_in_status) AS INTEGER) % 3600) / 60,
CAST(AVG(time_in_status) AS INTEGER) % 60
) AS formatted_duration
FROM your_table;

In case of any issues, may You please provide a test value for the timestamp as well as the error message?

Thank You!

Pears.

[Pears Whims]

Hello, @João Pedro Gomes Ramalho .

May you please let me know if the solution help You to progress on Your case?

Best of luck,
Pears.

[João Pedro Gomes Ramalho]

Hello, @Pears Whims 
The second approach results by creating a custom column and appying it.
Best regards,
João