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

Solved!
Level 1
###### Convert time stored as six digit number to time stamp and find difference in hrs of two such time

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

1 Solution
Dataiker

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!

2 Replies
Dataiker

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!

Level 1
Author

Thanks RoyE, it works

Tags (3)