Convert time stored as six digit number to time stamp and find difference in hrs of two such time

[RAKS]

I have a dataset where date stored in format yyyyMMdd and time stores as hhmmss (see image)

 i need to calculate the difference in hrs between Datetime combined of both type of columns , can someone help, already tried through various methods regex, python code no success.

---

Operating system used: windows 11

[RoyE · Accepted solution]

Hi RAKS,

Assuming that there are no issues with the rows in your dataset, this can be accomplished through a prepare recipe. Please see below for more details.

Step 1: Concatenate columns

This will output a string with the format of yyyyMMddHHmmss. You will use this in the next step.

Step 2: Format the string into a proper timestamp

This will output a new column, timestamp_start_formatted in a proper date format. 

Do the above for your start time and stop time before continuing to calculate the time between columns.

Step 3: Calculate the time between two columns.

As you can see, this step will calculate the time in minutes from column timestamp_start_formatted until column timestamp_stop_formatted. 

You can change the column names to whatever your column's names are/want to be.

A sample output from the dataset that you provided:

Hope this helps!

[RAKS]

Thanks RoyE, it works