ActiveRecord DATETIME column values in mysql

Question

I am new to ActiveRecord and trying to do something fairly simple. I searched the internet and was unable to come up with a solution.

In mysql I have a table column with a type of DATETIME. This table has many values in it already. Some example values for this column are NULL, 0000-00-00 00:00:00, and 2009-04-30 10:30:22

Using rails and ActiveRecord I can retrieve records from the database. However, when I ask for the value of a DATETIME field with the value of 0000-00-00 00:00:00 the result I get is a nil object.

A database value of NULL also returns a nil object, which makes sense.

A database value of 2009-04-30 10:30:22 returns Thu, 30 Apr 2009 10:30:22 UTC +00:00 which also makes sense.

I thought using _before_type_cast might help, but it does not.

How can I determine if the object returned has a database value of 0000-00-00 00:00:00?

Shane · Accepted Answer · 2013-03-07 23:54:09Z

3

It seems like there is no way built in to Rails to get around casting that date to something non-nil.

The fact that there is no way around this is a sign that you're probably doing something in a non-optimal way, so my first suggestion is to just migrate all 0000-00-00 00:00:00 values to nil if you can.

If that isn't an option. You can find out whether any one record has 0000-00-00 00:00:00 via sql conditions something like this:

Post.where("date_field = '0000-00-00 00:00:00' && id = ?", post.id).exists?

Hope that helps.

answered Mar 7, 2013 at 23:54

Shane

5143 silver badges6 bronze badges

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

1 Comment

Michael Over a year ago

I agree this may be non-optimal. However, I am working with a legacy PHP application and the 0000-00-00 00:00:00 values have significant meaning vs NULL values. I am surprised that there is not a better rails solution to this. You do get an up vote from me though. Thanks!

Collectives™ on Stack Overflow

ActiveRecord DATETIME column values in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related