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?
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.
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?
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.
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?
On Nov 24, 2007, at 5:30 PM, Frederick C. wrote:
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.
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.
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.
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.
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.
You can always make a helper that converts nil dates to 0000-00-00.
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 suppose I could be missing something, but I have used 0000-00-00 in
MySQL for years now, so this seems extremely lame.
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.
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.