如何使用 Google Data Studio 以小时为单位计算持续时间 [英] How to Calculate Duration in Hours Using Google Data Studio

问题描述

我正在尝试使用存储在 Google 表格中的数据使用以下字段获取持续时间(以小时为单位)，并在 Google 数据洞察中显示数据.

我能够使用 =(H2-I2)*1440 在 Google 表格中获得我想要的结果.但是，我希望使用 Google Data Studio 管理计算.我厌倦了在 Google 数据洞察中使用 CAST(EndTime AS NUMBER) - CAST(StartTime AS NUMBER) 但这似乎不起作用.

解决方案

0) 总结

下面看2个问题:

1. 更新的问题(DATETIME_DIFF):找出两个时间字段NewStartTime 和NewEndTime 之间的差异，然后还包含一个 Date 字段；
2. 原始问题 (TIME_DIFF):查找 2 个时间字段 StatTime 和 EndTime 之间的差异.

1) 更新(2020 年 9 月 17 日日期和时间更新)

使用

1.2) DATETIME_DIFF

复制并粘贴下面的

2) 原帖

可以使用以下 3 个步骤来实现:

2.1) 类型(HH:MM 字段)

默认情况下，字段应被检测为文本字段，如果不确保它们在数据源中设置为文本字段，则如下所示:

2.2) Time_DIFF

复制并粘贴下面的

I'm trying to get the duration in hours using data stored in Google sheets using the following fields and display the data in Google Data Studio.

I was able to get the results I wanted in Google sheets using =(H2-I2)*1440. However, I want the managed the calulation using Google Data Studio. I tired using CAST(EndTime AS NUMBER ) - CAST(StartTime AS NUMBER ) in Google Data Studio but that didn't seem to work.

解决方案

0) Summary

The below looks at 2 questions:

1. Updated Question (DATETIME_DIFF): Find the difference between the two time fields, NewStartTime and NewEndTime and also incorporate a Date field;
2. Original Question (TIME_DIFF): Looks for the difference between 2 Time fields, StatTime and EndTime.

1) Update (17 Sep 2020 Dates & Time Update)

Updated the Answer with the solution using the Updated Date Time Functions which incorporates the PARSE_DATETIME and DATETIME_DIFF functions:

1.1) Upgrade the Date Field

Upgrade the Date field to the newer Date field type and ensure that the NewStartTime and NewEndTime fields are set to Text.

Added a GIF to elaborate:

1.2) DATETIME_DIFF

Copy-paste the Calculated Field below to create a value in seconds that shows the difference between the two fields:

DATETIME_DIFF(PARSE_DATETIME("%Y/%m/%d%I:%M:%S %p",CONCAT(Date,NewEndTime)), PARSE_DATETIME("%Y/%m/%d%I:%M:%S %p",CONCAT(Date,NewStartTime)), SECOND)

1.3) Type (DATETIME_DIFF)

Number > Duration (Sec.)

Added a New Page to the Report and a GIF to demonstrate:

2) Original Post

It can be achieved using the 3 steps below:

2.1) Type (HH:MM Fields)

By default, the fields should be detected as Text fields, if not ensure that they are set to Text fields at the Data Source, such that it looks like:

2.2) Time_DIFF

Copy-paste the Calculated Field below to create a value in seconds that shows the difference between the two fields:

((CAST(REGEXP_EXTRACT(EndTime,"^(\d+):")AS NUMBER)*60*60) + (CAST(REGEXP_EXTRACT(EndTime,"^\d+:(\d+)")AS NUMBER)*60) + NARY_MAX(CAST(REGEXP_REPLACE(EndTime,".*(PM)$","43200")AS NUMBER),0)) -
((CAST(REGEXP_EXTRACT(StatTime,"^(\d+):")AS NUMBER)*60*60) + (CAST(REGEXP_EXTRACT(StatTime,"^\d+:(\d+)")AS NUMBER)*60) + NARY_MAX(CAST(REGEXP_REPLACE(StatTime,".*(PM)$","43200")AS NUMBER),0))

2.3) Type (Time_DIFF)

Numeric > Duration (Sec.)

Google Data Studio Report and a GIF to elaborate:

这篇关于如何使用 Google Data Studio 以小时为单位计算持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！