PG::Error with group_date gem in rails

Question

I have been using this for getting the hourly data of one day, so I have tried like this

@getting_each_day = Order.group_by_hour_of_day("2014-08-22 00:00:00").count

but it's showing error with postgresql.....

PG::Error: ERROR: syntax error at or near "00"
LINE 1: ...UNT() AS count_all, EXTRACT(HOUR from 2014-08-22 00:00:00::...
^
: SELECT COUNT() AS count_all, EXTRACT(HOUR from 2014-08-22 00:00:00::timestamptz AT TIM
ZONE 'Asia/Muscat' - INTERVAL '0 hour')::integer AS hour_of_day FROM "orders" WHERE (2014- 
08-22 00:00:00 IS NOT NULL) GROUP BY EXTRACT(HOUR from 2014-08-22 00:00:00::timestamptz AT
TIME ZONE 'Asia/Muscat' - INTERVAL '0 hour')::integer

but it's working with Order.group_by_hour_of_day(:created_at).count

Is there anything wrong with my query? Also, I have tried with timezone, but getting the same error.

Answer 1

You can simply use group (without using the gem) specifying the your target day in the where clause:

Order.where('date(created_at) = ?', "2014-08-22").select('EXTRACT(HOUR from created_at)').group('EXTRACT(HOUR FROM created_at)').count