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?

stackoverflow.com/questions/1349280/…

kommradHomer
– kommradHomer

2012-04-05 14:27:25 +00:00
Commented Apr 5, 2012 at 14:27 — kommradHomer
– kommradHomer, Commented Apr 5, 2012 at 14:27

0x4a6f4672 · Accepted Answer · 2012-04-05 14:19:42Z

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 5, 2012 at 14:19

0x4a6f4672

28.3k19 gold badges110 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

0x4a6f4672 · Accepted Answer · 2012-04-05 14:52:47Z

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 5, 2012 at 14:52

0x4a6f4672

28.3k19 gold badges110 silver badges144 bronze badges

1 Comment

SteenhouwerD Over a year ago

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

Collectives™ on Stack Overflow

Mysql date and rails dates with utc

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related