Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I would like to calculate the date difference.
Have a table like below.
Task Name Last Reload Next Execution Frequency
Task A 2021-10-13 06:55:27 13/10/2021 07:55:20 1 Hr
The expressions are below.
Last Reload - timestamp(Max(LogTimeStamp))
Next Execution =TimeStamp([Task Next Execution] -7/24 , 'DD/MM/YYYY hh:mm:ss')
Now, How can I calculate the Frequency field (round to 1 hr) that basically shows the difference between Last Reload and Next Execution?
Thanks
Hi @jpjust,
Please try below expression.
date(time(round( interval([New Date]-[Old Date],'hh:mm'),maketime(0,30))),'HH') &' Hr'
Hope you are looking the same result.
First you need to make sure both are loading as timestamps. then you can subtract and use Interval() to format as needed.
testdata:
load
[Task Name],
timestamp#([Last Reload], 'YYYY-MM-DD hh:mm:ss') as LastReload,
timestamp#([Next Execution], 'DD/MM/YYYY hh:mm:ss') as NextExecution
;
load * inline
[
Task Name, Last Reload, Next Execution, Frequency
Task A, 2021-10-13 06:55:27, 13/10/2021 07:55:20, 1 Hr]
;
expression:
interval(NextExecution-LastReload, 'hh:mm')
Thanks Abhijit.
It sort of worked but the output is wrong for the below. (Instead of 1 hr, it output as 23 hr)
Task Name Last Reload Next Execution Frequency
Task A 2021-10-13 06:55:27 13/10/2021 07:55:20 23 Hr
Below is my expression for the field, frequency.
date(time(round( interval(Max(LogTimeStamp))- TimeStamp([Task Next Execution] -7/24,'hh:mm'),maketime(0,30))),'HH') &' Hr'