CODEWITHSUNDEEP
ruby-on-railspostgresqltimezone
Scott
Scott

Reputation: 172

querying postres with rails and time zones

I'm querying for results like this:

@logs.where("extract(dow from created_at at time zone ?) = ?", "America/New_York", day_of_week)

but it's not working. (I've also tried "Time.zone.tzinfo.identifier" in there, but used the result here for testing and clarity.) For example, someone may make a post at 10:30 Eastern time but since it's stored in UTC, it won't show up in a query that includes it.

I started a new question because I got this answer from here and it obviously didn't work for me even though it has an accepted answer: handling rails + postgres and timezones

Is there something I'm doing wrong with this method? Is there another current or better way?

Updated info:

created_at is stored as a datetime. My time zone setting is still UTC, but I'm trying to fetch the posts with "America/New_York" zone, which should still work. By doesn't work, I mean I'll query for the 15th and some posts will return, but some made later in the day on the 15th will still return on the 16th due to the UTC difference. The query that includes it is list above the the code block. I'm trying to fetch dow from created_at at the time zone "America/New_York".

Upvotes: 3

Views: 351

Answers (2)

Scott
Scott

Reputation: 172

@logs = @logs.where("extract(day from (created_at at time zone 'Etc/UTC') at time zone ?) = ?", Time.zone.tzinfo.identifier, params[:date].to_f)

finally figured it out! Mostly from this link, but with a few minor tweaks:

PostgreSQL: selecting rows that occur on a certain day of the week, in a specific time zone

Upvotes: 3

house9
house9

Reputation: 20614

Here is a blog post I wrote about rails and timezones, you might find it helpful? http://jessehouse.com/blog/2013/11/15/working-with-timezones-and-ruby-on-rails/

Try running your query like this

Time.use_zone("Eastern Time (US & Canada)") do 
  start_at = (Date.current - 1.day).beginning_of_day
  end_at = start_at.end_of_day
  @logs_from_yesterday = Log.where("created_at BETWEEN ? AND ?", start_at, end_at)
end

Rails will automatically offset the datetime filters to the timezone specified in the use_zone block and Date.current is timezone aware (alias for Time.zone.now.to_date)

Upvotes: 1

Related Questions