I need to run a group_by query in Ruby on Rails, but I first want to adjust all records in the created_at column by a certain hour amount before running the query. So, for example, adding 9 hours to every record in the created_at field, and then grouping by date.
Something like the following (which is incorrect):
@foo = Bar.group("date(created_at + 9.hours)").count
How can I accomplish this in Rails?
PostgreSQL has excellent support for manipulating dates and times (see Date/Time Functions and Operators). You can express '9 hours' as an interval
, add it to a timestamp
, and cast to a date
:
=> select (now()::timestamp + '9 hours'::interval)::date;
date
------------
2012-09-22
(1 row)
This ends up strikingly similar to your original pseudocode:
@foo = Bar.group("date(created_at + '9 hours'::interval)").count