Rails 4: GROUP BY date with time zone

Question

I am trying to get the number of LaserSheets cut per day to use in a chart.

pages_controller.rb:

start_date = 7.days.ago
recent_cut_stats = LaserSheet.where('cut_at IS NOT NULL')
                             .where('cut_at > ?', start_date.beginning_of_day)
                             .group("DATE(cut_at)")
                             .count

I am running into problems because the rest of my site uses the time zone set in application.rb, but the above query returns results grouped by date in UTC time. After 5:00pm PDT (UTC -0700) I start seeing results for tomorrow. Is there a way to convert group("Date(cut_at)" to the application's time zone?

If you're using MySQL, take a look at http://stackoverflow.com/questions/28015068/how-to-group-by-date-accounting-for-timezones-and-dst — magni-, Apr 13 '16 at 03:33

score 3 · Accepted Answer · answered Apr 13 '16 at 03:57

As the comment above suggests, you'll need to cast the date using SQL your native database:

start_date = 7.days.ago
recent_cut_stats = LaserSheet.where('cut_at IS NOT NULL')
                             .where('cut_at > ?', start_date.beginning_of_day)
                             .group("DATE(CONVERT_TZ(cut_at, 'UTC','<name of time zone>'))")
                             .count

See https://en.wikipedia.org/wiki/List_of_tz_database_time_zones for a list of database time zones.

For the .where('cut_at > ?', start_date.beginning_of_day) statement, make sure your timezone is correctly set in Rails:

in application.rb:

config.time_zone  = '<name of time zone>'

score 0 · Answer 2 · answered Sep 20 '19 at 13:32

0

.group("DATE(created_at at time zone 'utc' at time zone 'Europe/Amsterdam')").count

Timezone names: https://popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql/

answered Sep 20 '19 at 13:32

Alex Freshmann

481
5
14

Rails 4: GROUP BY date with time zone

2 Answers2