Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Time Difference

Alteryx_Geek
8 - Asteroid

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.

 

 

11 REPLIES 11
Andreina
Alteryx
Alteryx

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-sec...

 

Hope it helps!!!

 

Regards,

Alteryx_Geek
8 - Asteroid

@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
8 - Asteroid

@binuacs / @OllieClarke - Any idea ?

binuacs
16 - Nebula

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

OllieClarke
14 - Magnetar
14 - Magnetar

@Alteryx_Geek 

 

how about this:

OllieClarke_0-1659521866534.png

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

 

So we add 1 second if the millisecond difference is more than 0.5

Hope that helps,

 

Ollie

Andreina
Alteryx
Alteryx

Hello @Alteryx_Geek , 

 

Maybe with a combination of some MultiRow and Formula transformations  you can get what you need. Here you have an example that might help you:

 

Andreina_0-1659523762374.png

 

 

Try to calculate Time Differences betweeen rows and then make the transformations in order to get days, minutes, secs and millisecs. 

 

Check the workflow that I attached. 

 

Hope it helps. 

 

Andreina

 

OllieClarke
14 - Magnetar
14 - Magnetar

Hey @Alteryx_Geek 

 

Apologies, I'd misread your actual question. This I think gives what you want:

OllieClarke_0-1659537321158.png

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

It outputs the number of seconds between a row and the row above to 0.1 of a second.

 

Hope that helps,

 

Ollie

 

Alteryx_Geek
8 - Asteroid

@OllieClarke - Apologize for late reply. Thanks for sharing Ollie. looks like your output is coming along but when you have answer in msec, its rounding off the number.

 

As highlighted below, the output should have been "0.11" instead of 1

 

Alteryx_Geek_0-1659715167275.png

 

 

Alteryx_Geek
8 - Asteroid

@binuacs - I just share the expected output in above snap. 

Labels