Time Difference

Alteryx_Geek

I am looking to find the delta between below timestamps (string format)

Example 1:

2022-07-01 12:19:15.700
2022-07-01 12:19:15.800

The answer should be 0.1 sec as the output.

Example 2:

2022-07-01 12:19:15.700
2022-07-01 12:19:20.700

The answer should be 5.0 sec as the output.

Datasets

Developer

Date Time

In Database

Database Connection

In-Database

Accepted answers

OllieClarke

@Alteryx_Geek I rounded everything to 1dp based on your question, but if you remove the Round(.....,0.1) from the formula then that will no longer be the case:

So:

datetimediff(LEFT([time],19),LEFT([row-1:time],19),'seconds')
+
tonumber(substring([time],19))-tonumber(substring([row-1:time],19))

millisecs.yxmd

All comments

Andreina

Hello @Alteryx_Geek

I think one of the solutions of the following post might help you:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-get-datetimediff-with-milli-seconds-in-Alteryx/td-p/797269

Hope it helps!!!

Regards,

Alteryx_Geek

@Andreina - Thanks for sharing the post, I tried this method but it doesn't work when there is vertical Timestamp column like below,

2022-07-05 12:17:48.003
2022-07-05 12:17:52.066
2022-07-05 12:19:02.919
2022-07-05 12:19:18.586
2022-07-05 12:19:34.857
2022-07-05 12:20:04.996
2022-07-01 12:21:30.717

Alteryx_Geek

@binu_acs / @OllieClarke - Any idea ?

binu_acs

@Alteryx_Geek Can you provide the expected output for the above DateTime values?

OllieClarke

@Alteryx_Geek

how about this:

IF 
Tonumber(Substring([Time],19))-ToNumber(substring([row-1:Time],19))>=0.5 
THEN
datetimediff(LEFT([time],19),LEFT([row-1:time],19),'seconds')+1
ELSE
datetimediff(LEFT([time],19),LEFT([row-1:time],19),'seconds')
ENDIF