如何使用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:
- 更新后的问题( DATETIME_DIFF ):找出两个时间字段
NewStartTime
和NewEndTime
之间的时差,并且还包含一个Date
字段; - 原始问题( TIME_DIFF ):查找两个时间字段
StatTime
和EndTime
之间的差异.
- Updated Question (DATETIME_DIFF): Find the difference between the two time fields,
NewStartTime
andNewEndTime
and also incorporate aDate
field; - Original Question (TIME_DIFF): Looks for the difference between 2 Time fields,
StatTime
andEndTime
.
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屋!