Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'm trying to calculate how many hours my servers have worked properly in march 2018.
For example for id 1 now I have the following:
| id | start date | how many months should work | termination date | delta time | state |
|---|---|---|---|---|---|
| 1 | 01-03-2017 10:19:09 | 12 | 01-03-2018 10:19:09 | 1487:59:59 | 4 |
| 1 | 01-03-2017 10:19:24 | 12 | 01-03-2018 10:19:24 | 1487:59:59 | 4 |
| 1 | 01-03-2018 10:19:09 | 12 | 01-03-2019 10:19:09 | 1487:59:59 | 3 |
| 1 | 01-03-2018 10:19:24 | 12 | 01-03-2019 10:19:24 | 1487:59:59 | 3 |
Where originally I have: ID, start date, how many months should work, state.
Termination date is calculated as
date(AddMonths(start_date, How_many_months_should_work), 'DD-MM-YYYY hh:mm:ss'), so I just add the quantity of months to a start date
And delta time is calculated in chart script as:
aggr(sum(if(interval(start_date) >= interval(MonthStart('01-03-2018')) and interval(Addmonths(start date, How_many_months_should_work)) >= interval(MonthEnd('2018-03-01')) and interval(start_date) <= interval(MonthEnd('01-03-2018')),
interval(MonthEnd('01-03-2018') - start_date), //starts somewhere in march
if(interval(start_date) <= interval(MonthStart('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) <= interval(MonthEnd('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) >= interval(MonthStart('01-03-2018')),
interval(Addmonths(start_date, How_many_months_should_work) - MonthStart('01-03-2018')), //starts before march, closed somewhere in march
if(interval(start_date) <= interval(MonthStart('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) >= interval(MonthEnd('01-03-2018')),
interval(MonthEnd('01-03-2018') - MonthStart('01-03-2018')))))), id) //full month
Some explainations to the code. As I have a time series and I want to calculate hours only for March, there are 3 cases:
1. my date starts in march and goes beyond this month. So delta time is monthend(march) - startdate
2. starts before and ends in march. delta time = termination date - monthstart(march)
3. It goes over the whole march(my case), so deltatime = monthend(march) - monthstart(march)
What I expect to reach is:
| id | start date | How many hours should work | termination date | delta time | state |
|---|---|---|---|---|---|
| 1 | 01-03-2017 10:19:09 | 12 | 01-03-2018 10:19:09 | 743:59:59 | 4 |
| 1 | 01-03-2018 10:19:09 | 12 | 03-03-2019 10:19:09 | 743:59:59 | 3 |
The main problem is that my algorithm calculates time differences for all 4 states(two states with 4, two with 3), while I need to take the minimum date by state and sum them.
Hope I explained it clearly.
Thanks in advance 