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

问题描述

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

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.

我可以使用=(H2-I2)*1440在Google表格中获得所需的结果.但是，我希望使用Google Data Studio来管理计算.我在Google Data Studio中使用CAST(EndTime AS NUMBER ) - CAST(StartTime AS NUMBER )感到厌倦，但这似乎不起作用.

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)摘要

下面着眼于两个问题:

0) Summary

The below looks at 2 questions:

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

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)更新(2020年9月17日日期和时间更新)

使用更新了答案，并使用了解决方案日期时间函数，其中包含 PARSE_DATETIME 和 DATETIME_DIFF 函数:

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:

将Date字段升级为较新的日期"字段类型，并确保将NewStartTime和NewEndTime字段设置为文本.

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

添加了详细的GIF:

复制并粘贴下面的计算字段以创建一个以秒为单位的值，该值将显示这两个字段之间的区别:

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)类型(DATETIME_DIFF)

Number > Duration (Sec.)

添加了新建报表页面和一个GIF来演示:

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

可以通过以下3个步骤来实现:

It can be achieved using the 3 steps below:

默认情况下，如果不确保将字段设置为数据源"中的文本"字段，则应将其检测为"文本"字段，如下所示:

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:

复制并粘贴下面的计算字段以创建一个以秒为单位的值，该值将显示这两个字段之间的区别:

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)类型(Time_DIFF)

Numeric > Duration (Sec.)

Google Data Studio报告和一个详细的GIF :

Google Data Studio Report and a GIF to elaborate:

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