Rails and postgres group_by data considering time zone

Question

How do I group records by date where the field is datetime type? But that’s considering Timezone.

For example:

When the user type 12/10/2014 23:30:00, it is recorded in the bank 13/10/2014 01:30:00, because our fuzo time is currently -2:00.

My data in bank:

|Descrição | created_at           |
|Reg 1     |  10/10/2014 17:00:00 |
|Reg 2     |  11/10/2014 01:30:00 | <-- Gostaria que fosse considerado como o dia 10/10/2014
|Reg 3     |  09/10/2014 05:30:00 |

When I have the following code:

Movimento.select("date(created_at) as dia, count(*) as qtd").group("dia")
-> [Movimento({:dia => '09/10/2014', :qtd => 1 }), Movimento({:dia => '10/10/2014', :qtd => 1 }), Movimento({:dia => '11/10/2014', :qtd => 1 }) ]

Eu gostaria que o resultado fosse:
-> [Movimento({:dia => '09/10/2014', :qtd => 1 }), Movimento({:dia => '10/10/2014', :qtd => 2 })]

How I make Timezone considered in my select and group_by?

Answer 1

Your bank probably has the Timezone 'UTC', if you want to take the time zone dynamically as it is set on ruby-on-Rails, use the following:

Movimento.select("date(created_at AT TIME ZONE 'UTC' AT TIME ZONE '#{Time.zone.tzinfo.name}') as dia, count(*) as qtd").group("dia")

Answer 2

For daylight saving time use BRST.

select(
    "date(created_at at time zone 'UTC' at time zone 'BRST') as dia, count(*) as qtd"
).group("dia")