AR trying force NULL into datetime

rubyonrails-talk
1

I scrounged the list and saw other questions but no answers for this:

Rails 1.2.5, MySQL 5.0.x

All my tables have NOT NULL for every field and all fields have an appropriate DEFAULT of either 0, 0.00, empty string or 0000-00-00 00:00:00 as appropriate.

When saving a new record which has no entry for a datetime field, Rails is complaining that the field cannot be NULL. It appears to be trying to force a NULL entry for that field. Even if I set an attribute to contain '0000-00-00 00:00:00' Rails still complains.

This feels like incorrect behavior to me.

What's up with this? Is there a config setting? A technique I'm missing? A workaround?

-- gw (www.railsdev.ws)

2

I still have no solution for this one. Rails appears to be determined to force me to either allow NULL or submit an actual date. Neither is acceptable for this case.

I scrounged the list and saw other questions but no answers for this:

Rails 1.2.5, MySQL 5.0.x

All my tables have NOT NULL for every field and all fields have an appropriate DEFAULT of either 0, 0.00, empty string or 0000-00-00 00:00:00 as appropriate.

When saving a new record which has no entry for a datetime field, Rails is complaining that the field cannot be NULL. It appears to be trying to force a NULL entry for that field. Even if I set an attribute to contain '0000-00-00 00:00:00' Rails still complains.

This feels like incorrect behavior to me.

What's up with this? Is there a config setting? A technique I'm missing? A workaround?

-- gw (www.railsdev.ws)

3

I still have no solution for this one. Rails appears to be determined to force me to either allow NULL or submit an actual date. Neither is acceptable for this case.

I think that fundamentally the problem here is that you either set the
column to an instance of Time (or possibly DateTime etc...) (which
can't represent 0000-00-00 00:00:00) or nil (which is mapped to NULL),
so when you set your attribute to '0000-00-00 00:00:00' it gets
coerced to nil. Can't think of a way around this.

Fred

4

I agree about the coercion. It's really lame though. 0000-00-00 (whether date or datetime or time version of it) is perfectly valid in the database. I've used it for years.

I tried submitting 0001-01-01 00:00:00, but Rails is coercing that to year 2001

I see it has been submitted to core group as a bug and the status was changed to WONTFIX.

I suppose I could be missing something, but I have used 0000-00-00 in MySQL for years now, so this seems _extremely_ lame.

-- gw (www.railsdev.ws)

5

The way I deal with it is to allow null and set the default to null. I know it’s not the same, but it does work.

-Bill

Greg Willits wrote:

6

Well, after doing some reading through bug reports and posts, Rails' maintainers suggest (incorrectly) that it is poor data design, and 0000-00-00 shouldn't be allowed.

I posted to the rails core list about it.

-- gw

7

nice, let us know what you find out.

Greg Willits wrote:

8

So, just to report back, there was a discussion about it, and bottom line is that since Ruby doesn't recognize 0000-00-00 as a valid date value, and since Rails auto-casts incoming field data, then bringing 0000-00-00 _in_ from the database is going to result in a nil. Since there's no way to _read_ 0000-00-00, there's little value (from the Rails Core point of view) in providing the ability to _write_ 0000-00-00.

Personally, I think there is value to it (when multiple systems share the data, when it is exported for other uses, etc.). To me, the database capability should dictate what Rails adaptors can do, but in the end I didn't bother pushing it further. It would take some code to cope with casting incoming dates of 0000-00-00 values as String, and then the app would have to test for three possible states (Date, String, Nil) instead of just two (Date, Nil). And actually I think Rails provides a way to play with fields before they're is cast? If so, then it could be implemented in the app layer.

The Rails guys of course are focused on a Rails-centric, Rails-only world, so to them the above solution has no value to core.

If an app really has to have the capability, you can also store dates as varchars -- which is what I've done in the past in order to preserve some multi-system data-interchange compatibility.

If we could find where Rails does this coercion step for dates, that could possibly be overridden, but short of doing that the bottom line is to allow NULL for date fields.

-- gw

9

You can always make a helper that converts nil dates to 0000-00-00.

10

You are missing something. Your technique will only work on databases like MySQL that use a string to represent the date type.

Another database may represent a date as a binary day number where day zero is 1/1/0001 or 9/16/1758. Such a database could not store "0000-00-00" in a date column.

In short, your technique is a hack that happens to work with MySQL, but it is not portable to other SQL databases, and hence it can not be reasonably supported by RAILS. SQL contains a NULL value for precisely this purpose, and you should use it.