Convert A Column from Seconds to HH:MM:SS
If you're trying to convert a column that's a normal number of seconds, like the Average Session Duration from Google Analytics, you can use Postgres to convert that column to HH:MM:SS for cleaner reporting.
First, select the SQL transform. Next, copy and paste this code in and replace the bolded sections with your data:
SELECT *, TO_CHAR(("mycolumn" || ' second')::interval, 'HH24:MI:SS') AS "New Column Name"
FROM report;
The section of code you'll need to replace is the "mycolumn" and "New Column Name". The first piece you should replace with the name of the existing column that has the seconds data. For the next piece, add in your new column's name as you want it to appear in Grow. If we're using the Google Analytics example, this is what the end result will look like:
SELECT *, TO_CHAR(("ga:AvgSessionDuration" || ' second')::interval, 'HH24:MI:SS') AS "Avg Session Duration"
FROM report;
Now, click "Run", and Grow will create a new column with the name you've assigned, and the seconds should be converted to the HH:MM:SS format instead, making it easier to see how many hours, minutes, and seconds of time each cell represents.
Happy Building!
Please sign in to leave a comment.
Comments
0 comments