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.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 288 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 94 SQL DataFlows
 595 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 672 Beast Mode
 42 App Studio
 34 Variables
 645 Automate
 165 Apps
 434 APIs & Domo Developer
 42 Workflows
 4 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 378 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 5 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 23 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 95 Community Announcements
 4.8K Archive