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

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.