OQL Group By Day

6
Hi there, TLDR: Can I group by the day of a date using OQL? Group by DATEPART(DAYOFYEAR, attr) is not sufficient as the date is required... We have a database with an enormous amount of entities – we are looking at a couple of millions – for load testing an application. We have made significant performance optimizations by using OQL. The next part of our optimization revolves around graphs. I would like to retrieve the data using an OQL query and then display it using a helper entity. However, I require the daily counts, whereas my data’s timestamp is throughout the entire day. Is it possible to group by the day of a date (or even trim the date to days in the OQL query)? I require the date for display purposes in my graph. I have attempted to group by the DATEPART(DAYOFYEAR, attr) functionality, however, this appears that this returns an integer, rather than a date. Below is my OQL query: SELECT E.Date Date, E.Name ItemName, COUNT(*) ItemCount FROM Module.Entity E WHERE DATEDIFF(HOUR,E.Date,''[%CurrentDateTime%]'')<=24 GROUP BY E.Date, E.Name ORDER BY E.Date DESC;  
asked
2 answers
0

Looking for the same thing myself now. Needed to create a graph showomg two dataserie for date and NrOfSomething, and for date and NrOfSomethingElse, all grouped by date, which may have several objects per day.

DateTime           NrOfSomething  NrOfSomethingElse 
20220918T01:01:01  5              1
20220918T22:22:22  4              1
20220919T21:00:00  2              2
20220919T22:22:22  1              2

Should give two lines having two dots:

- 20220918 value 9 and 20220919 value 3. 

- 20220918 value 2 and 20220919 value 4.

Solved this by creating a microflow that:

  • retrieved the information, using an OQL grouped by ‘DateTime’,
  • pass that to an entity having the three attributes DateTime, NrOfSomething, NrOfSomethingElse and an extra attribute ‘Date’,
  • loop over that list and change Date to trimToDays($MyObject/DateTime).

Endresult is a list of objects that I can pass to a lineChart that will sum the values having the same date:

answered
0

Potential Solutions:

  1. Use something like the following query and format the column labels yourself (perhaps formatDateTime(parseDateTime(toString($currentObject/Day) + '-' + toString($currentObject/Month), 'dd-MM') , 'd, MMM') if you need to display the month as a string):

    SELECT

            COUNT(*) AS Total,

            DATEPART ( DAY , Attribute ) AS DayFromAttribute,

            DATEPART ( MONTH , Attribute ) AS MonthFromAttribute

    FROM

            MyFirstModule.Entity

    GROUP BY

            DATEPART ( DAY , Attribute ), DATEPART ( MONTH , Attribute )

  2. Option if you must get a date type back, but this presumably has performance penalties:

    SELECT

            COUNT(*) AS Total,

            DATEPART ( DayOfYear , Attribute ) AS DayFromAttribute,

            MIN( Attribute ) AS Date

    FROM

            MyFirstModule.Entity

    GROUP BY

            DATEPART ( DAYOfYear , Attribute )

  3. Other options involving something like the query above but joined with a select distinct of the datepart

answered