Transforming Average of Seconds to Time Format in Amazon Athena for BoldBI Dashboards

Published:

Overview

This article explains how to calculate the average of a column representing seconds in a data table from Amazon Athena, convert the average value to the #h:#m:#s format, and display the result on a Card widget in BoldBI.

Prerequisites

An Amazon Athena data source with a table containing a column representing seconds
An existing BoldBI dashboard or creating a new one

Steps

Step 1: Add the Column in Code View Query

Use the following function to calculate the average and convert the seconds to the #h:#m:#s format in the code view query.

SPLIT_PART(CAST(time '00:00:00' + (SELECT AVG("columnname") FROM "databasename"."tablename") * interval '1' second  AS VARCHAR), '.',1)

Replace columnname, databasename, and tablename with the appropriate values from your data table.

Step 2: Add and Configure the Number Card Widget

Add the ‘Number Card’ widget to your dashboard.
Configure the newly added column in the Series section.
Disable the ‘Show Measure’ property in the Property section, which will display the result in the #h:#m:#s format.

Conclusion

By following these steps, you can calculate the average of a column representing seconds in an Amazon Athena data table, convert the average value to the #h:#m:#s format, and display the result on a Card widget in BoldBI.