Mysql date and rails dates with utc

Question

We want to get all the people that are born on a certain date. For the moment we use the query like this :

User.where(["day(birthdate) = day(?) AND month(birtdate) = month(?)", Time.now.utc, Time.now.utc ])

Now when we safe a user with date 29/03/1989 in the form, the field birthdate in the db contains this : 28/03/1989 22:00:00 UTC+2

The problem is that mysql will take 28 as day instead of 29 when we use the day() function of mysql.

How can I fix this?

http://stackoverflow.com/questions/1349280/storing-datetime-as-utc-in-php-mysql — kommradHomer, Apr 05 '12 at 14:27

score 1 · Accepted Answer · answered Apr 05 '12 at 14:19

1

What kind of MySQL data type do you use to store the birthday attribute? It probably helps to store only the date (not the time) by choosing the data type date instead of datetime.

answered Apr 05 '12 at 14:19

0x4a6f4672

27,297
17
103
140

score 1 · Answer 2 · answered Apr 05 '12 at 14:52

1

Maybe it is helpful to set the right time zone in environment.rb (Rails 2.x) or application.rb (Rails 3.x), for example config.time_zone = "Europe/Berlin"? Active Record should auto-convert the time to this zone.

answered Apr 05 '12 at 14:52

0x4a6f4672

27,297
17
103
140

Indeed, but if the date is 28/03/1989 22:00:00 UTC+2 in the DB, then the mysql function day(28/03/1989 22:00:00 UTC+2) will give back 28 and activerecord day(Time.now) 29 if we are 28/03/1989 22:00:00 UTC+2 – SteenhouwerD Apr 06 '12 at 07:35

Mysql date and rails dates with utc

2 Answers2