ETL calculation to work out hours taken from a start and finish timestamp
Hi all and happy Monday
I have some data i am pulling in via a SQL workbench that gives us a time clocked onto a job and another column for clocked off the job.
The data is in a the format 20221223T14:27:20.
So i am trying to generate a calculated column that gives me the hours taken per job, for example:
Clock on = 20221223T14:27:20
Clock off = 20221223T15:27:20
Hours Taken = 1
Hours can be broken down, so 30mins will be 0.5 of an hour.
Have attached what the data looks like from the workbench.
Is there an easy calculation i can use?
Thanks
Rob
Best Answer

@robdmitchell There are a couple different ways you could do this in MagicETL:
 Date Operations Tile: Use the Difference between dates operation, with hours as the unit of measurement
 Formula Tile: Add a field with the following formula:
DATEDIFF(`DateTimeOff`,`DateTimeOn`)*24 + TIME_TO_SEC(TIMEDIFF(`DateTimeOff`,`DateTimeOn`))/(60*60)
3
Answers

@robdmitchell There are a couple different ways you could do this in MagicETL:
 Date Operations Tile: Use the Difference between dates operation, with hours as the unit of measurement
 Formula Tile: Add a field with the following formula:
DATEDIFF(`DateTimeOff`,`DateTimeOn`)*24 + TIME_TO_SEC(TIMEDIFF(`DateTimeOff`,`DateTimeOn`))/(60*60)
3 
Hi @MichelleH
Sorry for the late reply, have been off with a bug.
For this task i used the formula and this worked a treat and exactly what i needed, thanks for your assistance.
Thanks
Rob
0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 565 Datasets
 2K Magic ETL
 3.4K Visualize
 2.3K Charting
 593 Beast Mode
 13 App Studio
 28 Variables
 588 Automate
 143 Apps
 417 APIs & Domo Developer
 27 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 361 Distribute
 99 Domo Everywhere
 260 Scheduled Reports
 2 Software Integrations
 96 Manage
 93 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive