ETL calculation to work out hours taken from a start and finish timestamp

robdmitchell

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 2022-12-23T14:27:20.

So i am trying to generate a calculated column that gives me the hours taken per job, for example:

Clock on = 2022-12-23T14:27:20
Clock off = 2022-12-23T15: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

MichelleH

@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)

MichelleH

@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)

robdmitchell

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