ActiveRecord query on dates in user's time zone

Question

I'm having querying objects on the same date relative to the user (ie if it is 2016-11-11 in the user's time zone, they should see events that occur on 11-11 in their time zone).

My database stores the event datetimes (of type datetime) in UTC. When I try:

Event.where(DATE(datetime) = ?", Time.zone.today)

I expect it to pull events where the datetime is on the same date as the current user's timezone (which is set in an around_action via Time.use_zone). I have this set in application.rb:

config.active_record.time_zone_aware_types = [:datetime, :time]

However, events that are stored in the next day UTC are not being returned. For example, an event at 11/11 10PM Mountain Time is stored at 11/12 5AM in UTC. It should still be returned to the Mountain Time user as happening on 11/11.

How do I query the database to get events for the current user's date? Many thanks!

Answer 1

Thanks to this question I found an answer:

Getting date with timezone offset in postgres

I basically needed to convert the datetime to UTC first, then to the user time zone.

Event.where("DATE(datetime AT TIME ZONE 'UTC' AT TIME ZONE ?) = ?", Time.now.in_time_zone(current_user.time_zone).strftime("%Z"), Time.zone.today)