topic Re: Date Difference Calculation in App Development

Date Difference Calculation

jpjust — Wed, 13 Oct 2021 15:09:08 GMT

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

Re: Date Difference Calculation

abhijitnalekar — Wed, 13 Oct 2021 18:54:23 GMT

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.

Re: Date Difference Calculation

stevejoyce — Wed, 13 Oct 2021 19:01:21 GMT

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

Re: Date Difference Calculation

jpjust — Wed, 13 Oct 2021 19:49:31 GMT

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'