created_at in where statement and timezone

Question

I'm trying to retrieve all the entries of the day in my model:

where('date(created_at) = date(?)', Date.today)

But, because of the timezone (My local time is UTC+2), I don't have the expected result. For example, if an entry has been created one minute after midnight, in the database, it will be stored at 22:01 (so not "Date.today").

Is there a way to do that ?

EDIT:

As the admin of the website, I want to display the number of the entries on my local time "today" (It's only for statistics purpose!). So I want to keep timestamps store in utc, but convert them to my local timezone during this request!

Yes : `before_filter do Time.zone = ActiveSupport::TimeZone.new('Paris') end` — Mathieu Mahé, Sep 03 '12 at 20:27
Try `TimeZone.new('Paris')` and then `Time.zone.now` to get the changes. — Kleber S., Sep 03 '12 at 20:29

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

You can set the correct time zone in several ways. One is described in this answer:

https://stackoverflow.com/a/6118837/567126

adding following to application.rb works

config.time_zone = 'Eastern Time (US & Canada)'

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 03 '12 at 20:30

spaudanjo

764
7
13

1

Isn't it a good thing that activerecord stores timestamps in UTC ? – Mathieu Mahé Sep 03 '12 at 20:33
Yeah, I think you are right. Timestamps should be stored in a standardized way. – spaudanjo Sep 03 '12 at 21:41
But on the other hand, it seems that ActiveRecord stores the timestamps in the local timezone per default: http://api.rubyonrails.org/classes/ActiveRecord/Timestamp.html – spaudanjo Sep 03 '12 at 21:45

score 0 · Answer 2 · answered Sep 04 '12 at 05:18

0

where('date(created_at) = ?', Time.zone.now.to_date)

answered Sep 04 '12 at 05:18

house9

20,359
8
55
61

score 0 · Answer 3 · answered Sep 04 '12 at 05:50

Time in the database should be stored as UTC. This way, when you do a worldwide website, you can serve each user with their own timezone.

It's the UI (views) responsibility to show the time in the user's local timezone.

For your where you should use Time.now.utc This way, you are searching the DB with the common UTC.

created_at in where statement and timezone

3 Answers3