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

robdmitchell
robdmitchell Member
in Magic ETL

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

Screenshot 2024-03-11 102804.png

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    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)

Answers

  • MichelleH
    MichelleH Coach
    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)

  • robdmitchell
    robdmitchell Member

    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

Categories