Q:

Postgresql select on Rails UTC date time, then group by string and perform an average

In my Rails app, I'm trying to perform a query without ActiveRecord. Essentially what I want to do is select records whose created_at matches a given DateTime, then group the records by string type, then average their values. Note: I'm using PostgreSQL.
So for example, running the desired query on the Movie records below would yield something like:

{ Blah: 6, Hi: 2, Hello: 4}

 id | value | event_id | user_id |         created_at         |         updated_at         | type
----+-------+----------+---------+----------------------------+----------------------------+-------------
  1 | 1     |        1 |       1 | 2014-01-22 03:42:44.86269  | 2014-02-15 01:54:15.562552 | Blah
  2 | 10    |        1 |       1 | 2014-01-22 03:42:44.86269  | 2014-02-15 01:54:15.574191 | Blah
  3 | 1     |        1 |       1 | 2014-01-22 03:42:44.86269  | 2014-02-15 01:54:15.577179 | Hi
  4 | 2     |        1 |       1 | 2014-01-22 03:42:44.86269  | 2014-02-15 01:54:15.578864 | Hi
  5 | 7     |        1 |       1 | 2014-01-22 03:42:44.86269  | 2014-02-15 01:54:15.580517 | Hello
  6 | 1     |        1 |       1 | 2014-01-22 03:42:44.86269  | 2014-02-15 01:54:15.58203  | Hello
(6 rows)

I think I can piece together the group by and average points, but I'm running into a wall trying to match records based on the created_at. I've tried:
select * from movies where 'movies.created_at' = '2014-01-22 03:42:44.86269'

And a few other variations where I try to call to_char, including:
select * FROM movies WHERE 'movies.created_at' = to_char('2014-01-22 03:42:44.86269'::TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');

The ActiveModel record for the first record in the above looks like this:
=> #<Movie id: 1, value: "1", event_id: 1, user_id: 1, created_at: "2014-01-22 03:42:44", updated_at: "2014-02-15 01:54:15", type: "Blah">

Its created_at, which is an ActiveSupport::TimeWithZone class looks like:
=> Wed, 22 Jan 2014 03:42:44 UTC +00:00

I imagine it has something to do with UTC but I can't figure it out. If anyone has any ideas I'd greatly appreciate it.

A:

Single-quoted values are interpreted by Postgres as literal strings. So your first query is looking for records where the literal string movies.created_at is equal to the literal string 2014-01-22 03:42:44.86269 - none of which exist.
Quoted identifiers in Postgres are quoted with double-quotes; also note that references with explicit table references (movies.created_at) are correctly quoted with the dot outside the quotes ("movies"."created_at") - if the dot is inside the quotes, it is interpreted as part of the column name.
You may want to keep the Postgres SQL reference handy in the future. :)

