Calculate the timedifference between two date_time measures

Looker Q&A Looker
1

Hi

I have a table looking like the one below. Start Time and End time being measures, and days, hours and minutes being tablecalcs to calculate the time between the start and end times. Im using the diff_days/hours/minutes for this.

Is it possible to create a table calc to calculate the time difference on a dd:hh:mm format? so it would, for this case have an additional column saying 01:01:36 ?

2

Have a look at the dimension_group type: duration here https://docs.looker.com/reference/field-params/dimension_group#type_duration

This way you don’t have to create any table calculations

3

Attempting to create the dimension group type:duration, but continue to get an error that the expression is incomplete. Working on calculating elapsed time between start and end date/time.

4

You could use this for a table calculation, but it does not pad zeros in the case of single digits (d:h:m:s format):

concat(
round(floor(diff_seconds(<your start time>,<your end time>)/60/60/24),0)
,":",
round(floor(mod(diff_seconds(<your start time>,<your end time>)/60/60,24)),0)
,":",
round(floor(mod(diff_seconds(<your start time>,<your end time>)/60,60)),0)
,":",
floor(round(mod(diff_seconds(<your start time>,<your end time>),60),0))
)