title: Android room aggregating data by day month or year
body: <p>I'm trying to graph data that I've stored using Room. For this I have a Session entity and then 1 to many relations for the session such as number of steps, blood pressure and oxygen levels that are recorded during the session.</p>
  
  <p>For the session I store the start time and I'd like to be able to group sessions by this date (into either day/month/year bins). For each time interval I then need to get the averages for steps, blood pressure and oxygen levels to show on the graph. For example, multiple sessions could happen on 4/23/2018, but I would like to just get the average for the day.</p>
  
  <p><strong>Session</strong></p>
  
  <pre><code>@Entity
  public class Session implements Serializable {
      public final static String TABLE = "Session";
  
      @PrimaryKey(autoGenerate = true)
      private long sid;
  
      @ColumnInfo(name = "startTimestamp")
      private Date startTimestamp;
  
      @ColumnInfo(name = "endTimestamp")
      private Date endTimestamp;
  
      public long getSid() { return sid; }
      public Date getStartTimestamp() { return startTimestamp; }
      public Date getEndTimestamp() { return endTimestamp; }
      public long getDuration() { return endTimestamp.getTime() - startTimestamp.getTime(); }
  
      public void setSid(long sid) { this.sid = sid; }
      public void setStartTimestamp(Date timestamp) { this.startTimestamp = timestamp; }
      public void setEndTimestamp(Date timestamp) { this.endTimestamp = timestamp; }
  
      @android.arch.persistence.room.Dao
      public interface SessionDao {
  
          @Query("SELECT * FROM " + TABLE)
          List&lt;Session&gt; getAll();
  
          @Query("SELECT * FROM " + TABLE)
          List&lt;SessionWithRelations&gt; getAllWithRelations();
  
          @Query("SELECT * FROM " + TABLE + " ORDER BY :orderBy")
          List&lt;SessionWithRelations&gt; getAllWithRelations(String orderBy);
  
          @Insert
          long insert(Session session);
  
          @Update
          void update(Session session);
  
          @Delete
          void delete(Session session);
      }
  }
  </code></pre>
  
  <p><strong>SessionOxygen</strong></p>
  
  <pre><code>@Entity
  public class SessionOxygen implements Serializable {
      public final static String TABLE = "SessionOxygen";
  
      @PrimaryKey(autoGenerate = true)
      private long id;
  
      @ColumnInfo(name = "sid")
      private long sid;
  
      @ColumnInfo(name = "timestamp")
      private Date timestamp;
  
      @ColumnInfo(name = "oxygen")
      private int oxygen;
  
      public long getId() { return id; }
      public long getSid() { return sid; }
      public Date getTimestamp() { return timestamp; }
      public int getOxygen() { return oxygen; }
  
      public void setId(long id) { this.id = id; }
      public void setSid(long sid) { this.sid = sid; }
      public void setTimestamp(Date timestamp) { this.timestamp = timestamp; }
      public void setOxygen(int oxygen) { this.oxygen = oxygen; }
  
      @android.arch.persistence.room.Dao
      public interface OxygenDao {
  
          @Query("SELECT * FROM " + TABLE + " WHERE sid=:sid")
          List&lt;SessionOxygen&gt; getAllForSession(int sid);
  
          @Insert
          long[] insertAll(List&lt;SessionOxygen&gt; oxygens);
  
          @Insert
          long insert(SessionOxygen oxygen);
      }
  
  }
  </code></pre>
  
  <p><strong>Session POJO</strong></p>
  
  <pre><code>public class SessionWithRelations {
      @Embedded
      public Session session;
  
      @Relation(parentColumn = "sid", entityColumn = "sid", entity = SessionBloodPressure.class)
      public List&lt;SessionBloodPressure&gt; bloodPressureList = new ArrayList&lt;&gt;();
  
      @Relation(parentColumn = "sid", entityColumn = "sid", entity = SessionWeight.class)
      public List&lt;SessionWeight&gt; weightList = new ArrayList&lt;&gt;();
  
      @Relation(parentColumn = "sid", entityColumn = "sid", entity = SessionOxygen.class)
      public List&lt;SessionOxygen&gt; oxygenList = new ArrayList&lt;&gt;();
  
      public int averageOxygen() {
          int sum = 0;
          if (oxygenList.size() &gt; 0) {
              for (SessionOxygen oxygen : oxygenList) {
                  sum += oxygen.getOxygen();
              }
              return sum / oxygenList.size();
          }
          return 0;
      }
  
      public int averageWeight() {
          int sum = 0;
          if (oxygenList.size() &gt; 0) {
              for (SessionWeight weight : weightList) {
                  sum += weight.getWeight();
              }
              return sum / weightList.size();
          }
          return 0;
      }
  
      public int averageDiastolic() {
          int sum = 0;
          if (oxygenList.size() &gt; 0) {
              for (SessionBloodPressure bloodPressure : bloodPressureList) {
                  sum += bloodPressure.getDiastolic();
              }
              return sum / weightList.size();
          }
          return 0;
      }
  
      public int averageSystolic() {
          int sum = 0;
          if (oxygenList.size() &gt; 0) {
              for (SessionBloodPressure bloodPressure : bloodPressureList) {
                  sum += bloodPressure.getSystolic();
              }
              return sum / weightList.size();
          }
          return 0;
      }
  }
  </code></pre>
  
  <p>I just switched my entire app over to using Room but I feel like this would be pretty easy with a raw query. I'm not sure if that's even possible with Room however. I looked at the RawQuery docs but that looks to only allow queries to be generated at run time but still return only entities and I need custom columns returned.</p>
  
  <pre><code>// Group by month and get average Oxygen level for each month with data
  select s.startTimestamp, AVG(o.oxygen) as oxygenAverage
      from Session s
      left join SessionOxygen o on s.sid = o.sid
      group by DATE_FORMAT(s.startTimestamp, "%m-%Y");
  </code></pre>
  
  <p>I have a feeling I have to do something with a POJO class but I'm not sure how to go about that.</p>
  
  <p>I think my other option is to aggregate it in code but that would be highly inefficient and messy.</p>