Time between Google Data Studio

Question

I have two Fields:

ACCEPTED_DRIVER_HOUR	DELIVERY_HOUR
18:42:01	18:49:00

In Google Data Studio, these two fields are shown as Text Fields, so what I want to do is:

SUM(ACCEPTED_DRIVER_HOUR) - SUM (DELIVERY_HOUR)

This way, I can get how much time the driver took to deliver and order.

The problem is that Google Data Studio says that I can't do that because the field is Text and if I change to Number, it says NULL because the numbers have : between them; if I try to change to Date Hour it's not possible.

I already tried to create new fields using CAST and it shows the same error (NULL)

I think you can convert both into Time ISO Format and take the difference. Date.prototype.toISOString() — Krishnaraj Rajendran

Nimantha Nimantha · Accepted Answer · 2021-03-01T06:52:23

0) Summary

Use EITHER #1 or #2; If #1 (DATETIME_DIFF) doesn't seem to work, try #2 (CAST & RegEx)

1) DATETIME_DIFF

One way that it can be achieved is by creating Date Time fields and then using the DATETIME_DIFF function to find the difference in SECOND after which the type can be set to Duration (sec.); it can be achieved in a single Calculated Field as shown below where the Date used is 01 Jan 1970 (feel free to change the date as required, though ensure that both line 2 and line 3 below use the same date):

1.1) Calculated Field (Formula)

DATETIME_DIFF(
PARSE_DATETIME("%F%T", CONCAT("1970-01-01 ", DELIVERY_HOUR)), 
PARSE_DATETIME("%F%T", CONCAT("1970-01-01 ", ACCEPTED_DRIVER_HOUR)), 
SECOND)

1.2) Calculated Field (Type)

Numeric > Duration (sec.)

1.3) Calculated Field (Aggregation)

It's set to SUM by default, though it can be changed as required, e.g. AVG.

Editable Google Data Studio Report and a GIF to elaborate:

2) CAST & RegEx

2.1) Calculated Field (Formula)

(CAST(REGEXP_EXTRACT(DELIVERY_HOUR, "^([^:]+):") AS NUMBER)*60*60 +
CAST(REGEXP_EXTRACT(DELIVERY_HOUR, ":([^:]+):") AS NUMBER)*60 +
CAST(REGEXP_EXTRACT(DELIVERY_HOUR, ":(\\d+)$") AS NUMBER)) -
(CAST(REGEXP_EXTRACT(ACCEPTED_DRIVER_HOUR, "^([^:]+):") AS NUMBER)*60*60 +
CAST(REGEXP_EXTRACT(ACCEPTED_DRIVER_HOUR, ":([^:]+):") AS NUMBER)*60 +
CAST(REGEXP_EXTRACT(ACCEPTED_DRIVER_HOUR, ":(\\d+)$") AS NUMBER))

2.2) Calculated Field (Type)

Numeric > Duration (sec.)

2.3) Calculated Field (Aggregation)

It's set to SUM by default, though it can be changed as required, e.g. AVG.

Added a New Page to the Editable Google Data Studio Report and a GIF to demonstrate: