Convert Seconds to Hours

[margaret_abercr]

Knowledge Drop

Last tested: Sep 11, 2019
 

The Problem

Some data is best displayed in smaller time units, such as seconds, but with aggregation of large datasets, the seconds value gets so large it is relatively meaningless. In that regards, it would be easier to display the data in another format.

The Solution

We can use the value format parameter as listed in this document, but to properly use the Excel style formatting for time we need to first divide our aggregation by the number of seconds within a day.
https://community.looker.com/lookml-5/readable-times-from-seconds-1587

On either the raw measure or on a duplicate for formatting purposes

1. Divide your sql parameter by the number of seconds in a day (60sec/min *60min/hr *24hrs/day) = 86400.0
   1. Adding the decimal creates a floating point that accommodates for the decimals.
2. Add the value_format parameter to your field and use the appropriate level of detail.
   1. The most conservative option is [hh]:[mm]:ss
   2. If you only need to the minutes level: [mm]:ss

Viola! Easily readable aggregated time

This content is subject to limited support.