Date Difference Calculation

[jpjust]

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

[abhijitnalekar]

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. 

[stevejoyce]

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')

[jpjust]

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'