How to alias a Date in a mySql group clause, using Ruby on Rails?

user_10015739 0
Viewed 101

I am trying to retrieve device readings between a start and end date. Then return an array of the date, sum of the counts, and m values from the results. However, I keep receiving a syntax error when attempting to alias the converted datetime in my group clause. As I need to store the datetime as a date object, how do I alias this properly in my group clause, so I can then pluck it?

ERROR:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as date' at line 1: SELECT date, sum(counts) as counts, ANY_VAUE(m) as m FROM device_readings WHERE device_readings.device_id = 1 AND (device_readings.datetime` BETWEEN '2021-04-01 00:00:00' AND '2021-05-01 00:00:00') GROUP BY DATE(CONVERT_TZ(datetime, 'GMT', 'America/New_York')) as date)

Record Columns

datetime: Time object, counts: number, m: number

Query:

    readings = @device.readings.
    where(:datetime => @start_date..@end_date).
    group("DATE(CONVERT_TZ(datetime, 'GMT', 'America/New_York')) as date").
    order("datetime ASC").
    pluck("date, sum(counts) as counts, ANY_VALUE(m) as m")
activerecord ruby-on-rails ruby mysql
2 Answers

You can't alias columns in the GROUP BY clause in SQL. Even if you could it would be pointless since you're not actually selecting the column and you can't reuse it anywhere else in the query either.

readings = @device.readings.
  where(datetime: @start_date..@end_date).

  group(:date).
  order(datetime: :asc).
  pluck(
    "DATE(CONVERT_TZ(datetime, 'GMT', 'America/New_York')) as date",
    "sum(counts)",
    "ANY_VALUE(m)"
  )

Note that if you're using .pluck aliasing the columns is meaningless unless you're referencing them in the query. The result of pluck is always an array of arrays. If you want hashes you want to zip the results:

readings.map {|r| Hash[[:date, :counts, :m].zip(r)] }

You could also create a SQL view and a corresponding model if you want to get fancy.

user_5448250

You can't define a Group by as as column and use it for your select or vice versa

readings = @device.readings.
where(:datetime => @start_date..@end_date).
group("DATE(CONVERT_TZ(datetime, 'GMT', 'America/New_York'))").
order("datetime ASC").
pluck("DATE(CONVERT_TZ(datetime, 'GMT', 'America/New_York')) as date1 , sum(counts) as counts, ANY_VALUE(m) as m")
user_51935360
Related