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 -
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 -
Thanks for sharing @byusteve!
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
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 618 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 741 Beast Mode
- 58 App Studio
- 40 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive