Rails / Postgresql: How can I quickly group records using a datetime field by date in a specific time zone?

Question

I'm using postgresql with Ruby on Rails. Right now I'm using this to count the number of users who sign up over each day:

@users_signup_by_day = User.count(:order => 'DATE(created_at) DESC', :group => ["DATE(created_at)"])

This works, but the users are grouped according to UTC. How can I group the users according to EST?

Update

the users are grouped according to UTC. How can I group the users according to EST?

To clarify: A certain date that begins and ends in UTC is a different span of time than the same date that begins and ends in EST. Right now the query generated by the above command considers the datetime that the user was created to be in UTC, and groups users by the day that they were created according to that span of time. How can I compensate for this considering I would like to group by dates according to EST and not UTC?

I have tried the following (thanks Erwin Brandstetter):

User.count(:order => "DATE(created_at AT TIME ZONE 'EST') DESC", :group => ["DATE(created_at AT TIME ZONE 'EST')"])

But this doesn't give the correct result because the first result is

["2011-12-02", 276]

and it's not 12/02/2011 in EST (UTC - 5 hours) yet. I'd appreciate any help.

Answer 1

You can use the expression timestamp_column AT TIME ZONE 'EST' before you cast to date. Consider this demo:

SELECT t AS ts_utc
      ,t::date AS date_utc
      ,t AT TIME ZONE 'EST' AS ts_est
      ,(t AT TIME ZONE 'EST')::date AS date_est
FROM (
    VALUES
     ('2011-12-1 03:00'::timestamp) -- defaults to timestamp without time zone
    ,('2011-12-2 12:00')
    ,('2011-12-3 23:00')) t(t);

Result:

       ts_utc        |  date_utc  |         ts_est         |  date_est
---------------------+------------+------------------------+------------
 2011-12-01 03:00:00 | 2011-12-01 | 2011-12-01 09:00:00+01 | 2011-12-01
 2011-12-02 12:00:00 | 2011-12-02 | 2011-12-02 18:00:00+01 | 2011-12-02
 2011-12-03 23:00:00 | 2011-12-03 | 2011-12-04 05:00:00+01 | 2011-12-04

ts_est constitutes the literal given time at that time zone, which is automatically converted to the time zone of your current location for display, '+01' in my case, as my database server sits in Vienna, Austria and has matching locale settings.

It is not entirely clear from your question, what "according to EST" means. But one way or the other, you can achieve it with that construct.

Answer 2

I'm a little late to the party, but since I just ran into this issue myself I thought I would post something that appears to be working in my situation. I'm modifying the date field in the database by adding/subtracting the UTC offset based on the configured rails timezone. So for example:

offset = Time.zone.formatted_offset   
interval = "#{offset[0]} time '#{offset[1..-1]}'" 

@users_by_day = User.group("date(created_at #{interval})").select("count(id) as counter, date(created_at #{interval}) as created")

Not very pretty, but seems to get the job done.