3 Replies Latest reply: Apr 4, 2018 12:56 PM by Josefina Fasoli RSS

    can't calculate time difference correctly

    Dmitrii Zhavoronkov

      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:

      idstart datehow many months should worktermination datedelta timestate
      101-03-2017 10:19:091201-03-2018 10:19:091487:59:594
      101-03-2017 10:19:241201-03-2018 10:19:241487:59:594
      101-03-2018 10:19:091201-03-2019 10:19:091487:59:593
      101-03-2018 10:19:241201-03-2019 10:19:241487:59:593

      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:


       

      idstart dateHow many hours should worktermination datedelta timestate
      101-03-2017 10:19:091201-03-2018 10:19:09743:59:594
      101-03-2018 10:19:091203-03-2019 10:19:09743:59:593


      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

        • Re: can't calculate time difference correctly
          Josefina Fasoli

          What if you add ,state after , id. I've a similar problem today (asked here) and got that answer (and worked!)

            • Re: can't calculate time difference correctly
              Dmitrii Zhavoronkov

              sorry, but I didn't understand what you mean. Could you, please, explain it a bit clearly?

                • Re: can't calculate time difference correctly
                  Josefina Fasoli

                  I'd try this:

                   

                  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, state)

                   

                  Aggr on a graph - incorrect values