how to group historical data in cumulocity cep query

Question

I have to group the last week data per day .I was able to retrieve the historical data every 10 sec but not able to group by date

insert into AllMeasurementsOfWeek 
select findAllMeasurementByFragmentTypeAndSourceAndTimeBetween('Avg_Load','764697',(current_timestamp().minus(7 day)).toDate(),current_timestamp().toDate()) as m,current_timestamp().toDate() as time
from pattern[every timer:interval(10 sec)];


@Name("Occupancy")
@Resilient 
select  * from AllMeasurementsOfDay ;

Above code will output the response as below

{
  "m": [
    {
      "time": "2018-04-14T17:03:14.000+02:00",
      "id": "17345353",
      "source": "764697",
      "type": "Avg_Load",
      "Average_Load": {
        "loading_time": {
          "unit": "min",
          "value": 25
        }
      }
    },
    {
      "time": "2018-04-15T17:03:14.000+02:00",
      "id": "17345194",
      "source": "764697",
      "type": "Avg_Load",
      "Avg_Load": {
        "loading_time": {
          "unit": "min",
          "value": 25
        }
      }
    },
    {
      "time": "2018-04-15T17:03:14.000+02:00",
      "id": "17345194",
      "source": "764697",
      "type": "Avg_Load",
      "Avg_Load": {
        "loading_time": {
          "unit": "min",
          "value": 25
        }
      }
    }
]

How should i access the above response and group by date ?

score 0 · Answer 1 · answered Apr 19 '18 at 11:29

Why don't you directly query the day you want? So doing 7 queries (one per day).

Otherwise whenever you do a query to the API with a date range it will be sorted by date. So you can also just add a small expression (like javascript) to split it into the 7 days. Should be pretty simple as the response is already sorted by date.

how to group historical data in cumulocity cep query

1 Answers1