Rails and postgres group_by by date considering time zone

Question

Rails and postgres group_by by date considering time zone

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

How do I group records by date where the field is of type datetime? But that's considering timezone.

For example:

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

My bank data:

|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 do I make timezone in my select and group_by?

ruby-on-rails postgresql active-record

asked by anonymous 08.11.2014 / 13:31

2 answers

1

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")

10.11.2014 / 15:10

Regular expression to find word exception How do I use the OpenSSL functions in VB.Net?

score 1 · Accepted Answer

Your bank probably has timezone 'UTC', if you want to get the time zone dynamically as it is set in , 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")