ActiveRecord query by user timezone dates

Question

I have a request for objects on the same date relative to the user (i.e. if in the user time zone 2016-11-11, they should see events that occur at 11-11 in their time zone).

My database stores datetime events (of type datetime) in UTC. When I try:

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

I expect it to pull events when the date-time is on the same date as the user's current time zone (which is set in around_actionvia 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 UTC the next day are not returned. For example, an event at 11/11 10PM Mountain Time is stored at 11/12 5 a.m. in UTC. It should still be returned to the Mountain Time user, as it happens on 11/11.

How do I query the database to receive events for the current date of the user? Many thanks!

Answer 1

Thanks to this question, I found the answer:

Getting date with timezone offset in postgres

I basically needed to convert the date-time to UTC first , and then to the user's 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)