Separate 2 Date Time Stamps from 1 String

I’m trying to calculate step time from start to finish. I’ve created a calculated field to create a date/time stamp in YYYY/MM/DD HH:mm:ss format. The single string has the date/time for creation as well as last update time stamp for each individual step.

Is there a way to separate the two dates? I want to find total time from first time stamp to the second time stamp.

Hi @Jon_D,

you should be able to do it by using the function dateDiff and pass the granularity at seconds level to get the difference between the 2 dates in seconds. Here is the link for more information: dateDiff - Amazon QuickSight

Hi @Srikanth_Baheti - To use date diff the date fields should be in the separated columns. Here both the date fields are in the same columns. I believe date diff will not work here, we have to use LAC approach here.

Hi @Biswajit_1993 - I know we have similar approach in one of the use case, can you please advise.

Regards - Sanjeeb

1 Like

Hi Jon_D, if you have one string with both dates, can you do a LEFT function to get the first date and RIGHT for the second? or use SPLIT if there is a separator between the 2.
The results will still be strings, but then you can do a parseDate on them to get the date value
parseDate(dateString, ‘yyyy/MM/dd HH:mm:ss’)

Once you have them as dates, you can apply the dateDiff function that Srikanth mentioned above.

1 Like

Hi Asem thanks for your support, this doesn’t seem to work. Looking at the Database the its a single date per line. When I map it against the task it’s populating two (I assume there are two separate strings for each “Task” and it’s pulling both when added to pivot table).

I do have a “closedtime” string. When trying to edit it to a date the database it gives me an error. I’m assuming because there are NULL or empty lines in the Database for this string. Is there a workaround for this? If I can change that string to a date then I can do a date diff between the create time stamp and the closed string.