SOLVED

Calculate Difference on Time

%3CLINGO-SUB%20id%3D%22lingo-sub-204091%22%20slang%3D%22en-US%22%3ECalculate%20Difference%20on%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204091%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20calculate%20difference%20between%202%20times%20to%20show%20for%20example%20how%20many%20days%20between%20the%20first%20and%20second%20value%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20image%20below%20provides%20the%20results%20and%20ideally%20I'd%20have%20another%20column%20that%20calculated%20that%20difference%2C%20just%20not%20sure%20what%20the%20operator%20would%20be%20to%20calculate%20that%20(can%20always%20remove%20the%20time%20portion%20to%20just%20get%20how%20many%20days%20it%20is).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F35984i10F705AE1E6B9B77%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Time%20Results.png%22%20title%3D%22Time%20Results.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-204091%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-204777%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20Difference%20on%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204777%22%20slang%3D%22en-US%22%3E%3CP%3EExactly%20what%20I%20was%20looking%20for%2C%20much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-204677%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20Difference%20on%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204677%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20could%20try%20this%20approach%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Edatatable%20(Date1%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E%2C%20Date2%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Edatetime(2018-06-11%2010%3A05%3A00)%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Edatetime(2018-06-11%2011%3A05%3A00)%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Edatetime(2018-06-11%2010%3A05%3A00)%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Edatetime(2018-06-12%2011%3A05%3A00)%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Edatetime(2018-06-11%2010%3A05%3A00)%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Edatetime(2018-06-13%2014%3A05%3A00)%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Edatetime(2018-06-11%2010%3A05%3A00)%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Edatetime(2018-06-15%2009%3A05%3A00)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3EDiff%3Dformat_timespan(Date2-Date1%2C%3C%2FSPAN%3E%3CSPAN%3E%22d%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%20Date1%2C%20Date2%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi All,

 

Is there a way to calculate difference between 2 times to show for example how many days between the first and second value?

 

The image below provides the results and ideally I'd have another column that calculated that difference, just not sure what the operator would be to calculate that (can always remove the time portion to just get how many days it is).

 

Time Results.png

2 Replies
Highlighted
Solution

You could try this approach:

 

datatable (Date1:datetime, Date2:datetime)
[
datetime(2018-06-11 10:05:00), datetime(2018-06-11 11:05:00),
datetime(2018-06-11 10:05:00), datetime(2018-06-12 11:05:00),
datetime(2018-06-11 10:05:00), datetime(2018-06-13 14:05:00),
datetime(2018-06-11 10:05:00), datetime(2018-06-15 09:05:00)
]
| project Diff=format_timespan(Date2-Date1,"d"), Date1, Date2
 
Highlighted

Exactly what I was looking for, much appreciated!