Calculated Field to Calculate Fraction of Hours between Two Timestamps
I am trying to calculate the number of hours (w/fraction, e.g. 36.25) between two datetime stamps, one of these fields may or may not be populated. I have tried multiple "TIMEDIFF()" fuctions but I cannot simply get the number of hours between the two datetime stamps. I've come close but cannot seem to get negative hours (i.e. 12:00 - 16:00 = -4).
Both fields are recognized by Domo as a date. There are times when the two values may be the same, although my attempts to filter these out has been unsuccessful.
Any suggestions woudl be appreciated. I've been working on different approaches for over 2 hours. Thanks in advance!
Below is my code thus far, I think I am close...
CASE
WHEN `start_datetime` IS NOT NULL
AND `end_datetime` IS NOT NULL
AND DATE_FORMAT(`end_datetime`, '%H:%i:%s') < DATE_FORMAT(`start_datetime`, '%H:%i:%s')
THEN ((DATEDIFF(`end_datetime`, `start_datetime`) - 1)*24)
+ ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
WHEN `start_datetime` IS NOT NULL
AND `end_datetime` IS NOT NULL
AND DATE_FORMAT(`end_datetime`, '%H:%i:%s') > DATE_FORMAT(`start_datetime`, '%H:%i:%s')
THEN ((DATEDIFF(`end_datetime`, `start_datetime`)*24)
+ ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
END
Best Answer
-
In case anyone is reading this down the road, I ended-up using "TO_SECONDS()" on both dates, calculating the difference, and then converting to hours. It was much cleaner.
TIMEDIFF() was behaving differently in BeastMode (was maxing out at 23:59:59) vs. a mySQL dataflow. Additionally by design (for some unknown reason) it maxes out at 838:59:59 (see http://www.mysqltutorial.org/mysql-timediff/), so if you are calculating differences that exceed ~35 days you'll run into problems with TIMEDIFF().
3
Answers
-
This code seems to be working, although I have a little more testing to do (i.e. when the times are equal):
CASE
WHEN `start_datetime` IS NOT NULL
AND `end_datetime` IS NOT NULL
THEN
CASE
WHEN DATE_FORMAT(`end_datetime`, '%H:%i:%s') < DATE_FORMAT(`start_datetime`, '%H:%i:%s')
THEN ((DATEDIFF(`end_datetime`, `start_datetime`)-1)*24) +
ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
ELSE
(DATEDIFF(`end_datetime`, `start_datetime`)*24) +
ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
END
END1 -
Thanks for sharing @byusteve!
Dani aka "Mr.Dojo"
Dojo Admin
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.0 -
I'm trying to do the same with the timestamps, however when the values are negative it is not substracting it properly. Any idea on how this can be solved?
e.g.
07 May 2018 16:00 - 08 May 2018 00:52 the result is -0.89 instead of -8.9hrs
Thanks,
Jessica.
0 -
The code below will produce negative fractional hours if Start Date > End Date and positive fractional hours if End Date > Start Date.
(UNIX_TIMESTAMP(`End Date`) - UNIX_TIMESTAMP(`Start Date`)) / 3600
NOTE: UNIX_TIMESTAMP() returns the number of seconds since 1970 and will therefore only work on dates after that time.
Jacob Folsom
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 259 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有