title: How to group by date and calculate the averages at the same time
body: <p>I am quite new to this, so here it goes: I am trying to convert from unixtime to date format and then group by this by date while calculating the average on another column. This is in MariaDB.</p>
  
  <pre><code>CREATE OR REPLACE
  VIEW `history_uint_view` AS select
      `history_uint`.`itemid` AS `itemid`,
      date(from_unixtime(`history_uint`.`clock`)) AS `mydate`,
      AVG(`history_uint`.`value`) AS `value`
  from
      `history_uint`
  where
  ((month(from_unixtime(`history_uint`.`clock`)) = month((now() - interval 1 month))) and ((`history_uint`.`value` in (1,
      0))
      and (`history_uint`.`itemid` in (54799, 54810, 54821, 54832, 54843, 54854, 54865, 54876, 54887, 54898, 54909, 54920, 58165, 58226, 59337, 59500, 59503, 59506, 60621, 60624, 60627, 60630, 60633, 60636, 60639, 60642, 60645, 60648, 60651, 60654, 60657, 60660, 60663, 60666, 60669, 60672, 60675, 60678, 60681, 60684, 60687, 60690, 60693, 60696, 60699, 64610)))
  
      GROUP by 'itemid', 'mydate', 'value'
  </code></pre>