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.

[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 
I've tried both approaches but without success.
For the first one the error thrown is simple. It basically says "strftime()" not supported

The second approach throws an enormous error message describing where the query failed at the source: 
Error running query:
org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 90737.0 failed 4 times:
java.util.IllegalFormatConversionException: d != java.lang.Double at java.util.Formatter$FormatSpecifier.failConversion(Formatter.java:4302) at java.util.Formatter$FormatSpecifier.printInteger(Formatter.java:2793)

Basically issues casting into integer.

Thanks in advance!
Best regards,
João