Rails is wrong in its coercion of empty dates to nil and requiring a
table field to allow NULL as the only alternative to a valid date entry.
NULL = unknown, unavailable, "still waiting" etc.
It's a well established discussion that NULL is needed to allow that
third option to zero and non-zero, empty and non-empty.
Rails is wrong in insisting that logic does not extend to dates.
A valid date = an affirmative akin a non-empty string, a non-zero
number.
A 0000-00-00 is an affirmative akin to an empty string, or a zero.
NULL is the _third_ option.
A valid date says an event happened. A NULL says we're still waiting for
confirmation that the event happened. A 0000-00-00 says that the event
did not happen, never will happen. It is not "unknown." It is not
"unavailable."
Rails is wrong in asserting that 0000-00-00 is poor database design, and
Rails is wrong to prevent the value from being used.
Rails is wrong in asserting that 0000-00-00 is poor database design, and
Rails is wrong to prevent the value from being used.
Your approach is wrong in attempting to generate discussion. Your
attempt to make your point clear is wrong in that I don't follow what
it is you're trying to achieve.
I'm not sure what problem you've hit, what solution you propose, or
how you want to implement it, but I do know you think rails is wrong?
Rails is wrong in asserting that 0000-00-00 is poor database design, and
Rails is wrong to prevent the value from being used.
Your approach is wrong in attempting to generate discussion. Your
attempt to make your point clear is wrong in that I don't follow what
it is you're trying to achieve.
I'm not sure what problem you've hit, what solution you propose, or
how you want to implement it, but I do know you think rails is wrong?
Yeah, you're right, I Ieft too many "Rails is wrong" in there. I wrote
each one planning for that to be the one after I got done editing, and I
got interrupted and sent the message thinking I was done. It reads
flamey for sure. My appologies. Anyway...
If a (MySQL) database table declares a datetime field as NOT NULL with a
default value of 0000-00-00 00:00:00 and an empty value is submitted to
that field via Rails, Rails complains that the field cannot be null. If
a string literal of 0000-00-00 00:00:00 is submitted, Rails coerces the
value to nil, and once again complains about not null.
I tracked down some bug reports and other posts, and it appears that the
Rails core position is that using 0000-00-00 is poor data design, and it
is Rails' duty to not allow it to be used, further taking the position
that that's what NULL is for.
Well, NULL is for that third alternative. As it stands, Rails allows
only two states for dates. A valid date or NULL. There are occassions,
rare as they may be, to interpret date data in three ways just like
numerics or strings.
I don't follow what it is you're trying to achieve.
Dang. Did it again. You wanted scenarios...
A typical example would be a Date Due field where a valid date = when it
was done, a NULL value = not done yet that we know of, and a 0000-00-00
value = it has been declared that it never will be done. Reports for
this data must be able to distinguish between all three casees.
Additionally, I have legacy data/logic being replicated in Rails that
expects to find and handle 0000-00-00 date values a specific way. AFAICT
I have no way to continue the legacy data/logic systems using Rails as I
cannot populate a date field with 0000-00-00 to remain consistent with
existing data.
A date field should contain only valid dates or be null. If you need a third alternative, use a code/switch field or store your date in a string. I don't believe there are any valid scenarios where storing invalid dates in a date field is more appropriate than using a separate code/switch to indicate the additional context.
A typical example would be a Date Due field where a valid date = when it
was done, a NULL value = not done yet that we know of, and a 0000-00-00
value = it has been declared that it never will be done. Reports for
this data must be able to distinguish between all three casees.
Ugly Hax and total abuse of a magic value, imo. Use a flag, as per
Josh's post.
Additionally, I have legacy data/logic being replicated in Rails that
expects to find and handle 0000-00-00 date values a specific way. AFAICT
I have no way to continue the legacy data/logic systems using Rails as I
cannot populate a date field with 0000-00-00 to remain consistent with
existing data.
I don't think rails should be designed to play nicely with your
invalid legacy data, personally. Designing the framework around
individual cases of legacy usage is a very, very bad idea. Anyways, I
can imagine core members responses already, once they figure out what
you are asking: "Sounds like a great idea for a plug-in."
A typical example would be a Date Due field where a valid date = when it
was done, a NULL value = not done yet that we know of, and a 0000-00-00
value = it has been declared that it never will be done. Reports for
this data must be able to distinguish between all three casees.
Ugly Hax and total abuse of a magic value, imo. Use a flag, as per
Josh's post.
Just because I've seen it, doesn't mean it's "right" (and v.v.) but I've
seen it often enough. Database providers allow the value as legal, so
why shouldn't it be used? Nothing magic about it IMO. Most date parsing
and validating code I've ever seen accepts it or uses it in some way
(not that I've seen everything). It is logical and it avoids the need of
exactly the type of field you're proposing (which of course is the
typical alternative).
Additionally, I have legacy data/logic being replicated in Rails that
expects to find and handle 0000-00-00 date values a specific way. AFAICT
I have no way to continue the legacy data/logic systems using Rails as I
cannot populate a date field with 0000-00-00 to remain consistent with
existing data.
I don't think rails should be designed to play nicely with your
invalid legacy data, personally.
Well, there's the difference I guess. I don't see it as an invalid date
-- when databases allow/require it as the value in a non-null date
field, I see it is as being valid as a "non-date" or "zero date."
Designing the framework around
individual cases of legacy usage is a very, very bad idea. Anyways, I
can imagine core members responses already, once they figure out what
you are asking: "Sounds like a great idea for a plug-in."
And if I can be pointed to how/where to override this behavior I would
be happy -- I've started to dig into Rails internals, but just barely,
and still trying to push my way through the multple layers of
abstractions for things. I've downloaded the acts_as_monkey outline for
plugins, but haven't experimented yet.
If the answer is that this particular group of people have a binary view
of dates, then waddya gonna do?
If I can override the behavior somehow, that would be fine with me too.
I can design around it in the future, but I would like the option if
it's possible.
If the answer is that this particular group of people have a binary view
of dates, then waddya gonna do?
If I can override the behavior somehow, that would be fine with me too.
I can design around it in the future, but I would like the option if
it's possible.
Unfortunately even if we did want to fix this, I'm not sure what the
accessors could return:
Date.civil(0,0,0)
ArgumentError: invalid date
from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/date.rb:727:in
`civil'
from (irb):9
Date.ordinal(0,0)
ArgumentError: invalid date
from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/date.rb:707:in
`ordinal'
from (irb):10
If I can override the behavior somehow, that would be fine with me too.
I can design around it in the future, but I would like the option if
it's possible.
Unfortunately even if we did want to fix this, I'm not sure what the
accessors could return:
Date.civil(0,0,0)
ArgumentError: invalid date
from
/System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/date.rb:727:in
`civil'
from (irb):9
Date.ordinal(0,0)
ArgumentError: invalid date
from
/System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/date.rb:707:in
`ordinal'
from (irb):10
Ruby's date libraries are having none of that.
Well, the target is MySQL and not Ruby, so I am approaching this from
the perspective of how to get what I want into MySQL, an not so much
from what Rails or Ruby would natively prefer to do.
If we skip Rails/Ruby altogether and work directly with MySQL, then
here's what we discover (and is the source of my working with 0000-00-00
formatted empty dates):
(these tables below are probably going to get mangled by this email
system that's bent on hard wrapping short lines)
Do we want to follow MYSQL, or any specific DB provider, practices,
over ruby's, though? And/or the standards? (Don't honestly know what
ANSI SQL has to say about 0000-00-00).
Do we want to follow MYSQL, or any specific DB provider, practices,
over ruby's, though? And/or the standards? (Don't honestly know what
ANSI SQL has to say about 0000-00-00).
Isn't that one of the reasons for choosing one database over
another--its support of different functionalities?
What's the point of having different dbs if app frameworks reduce them
to some wimpy lowest common denominator.
What's the point of having different dbs if app frameworks reduce them
to some wimpy lowest common denominator.
Why even bother with SQL, then :)? Why not just give every database
their own query language that's designed just for them. There are
other reasons than API to pick different databases (like performance,
storage engines, replication facilities, etc).
What's the point of having different dbs if app frameworks reduce them
to some wimpy lowest common denominator.
Why even bother with SQL, then :)? Why not just give every database
their own query language that's designed just for them. There are
other reasons than API to pick different databases (like performance,
storage engines, replication facilities, etc).
ok, well, that fork is headed nowhere, but I still don't see what the
heck the catastrophe is behind allowing the database to accept defaults
it deems as valid, even resorts to, in its own API.
This is the biggest issue for me - the coercion. How “agnostic” is Rails attempting to be if it’s actively overriding or erroring on what the selected DB allows for values?
The 1992 draft SQL standard (sorry, all I have handy at the moment)
says "The <datetime field>s other than SECOND contain non-negative
integer values, constrained by the natural rules for dates using the
Gregorian calendar." This leaves it up to the Gregorian calendar,
which doesn't have a year zero, much less month zero and day zero as
far as I can tell. The ISO 8601 standard allows zero and negative year
values to represent BC dates, but still requires non-zero month and
day values. I've been mostly a DB2 user for 20 years or so, but I
believe that most RDBMS implementations follow the standard and
disallow 0000-00-00 as an invalid date.
The 1992 draft SQL standard (sorry, all I have handy at the moment)
says "The <datetime field>s other than SECOND contain non-negative
integer values, constrained by the natural rules for dates using the
Gregorian calendar." This leaves it up to the Gregorian calendar,
which doesn't have a year zero, much less month zero and day zero as
far as I can tell. The ISO 8601 standard allows zero and negative year
values to represent BC dates, but still requires non-zero month and
day values. I've been mostly a DB2 user for 20 years or so, but I
believe that most RDBMS implementations follow the standard and
disallow 0000-00-00 as an invalid date.
I would argue this is not an issue of "most" or even "standard" -- most
code on the planet is probably written with camelCase names -- which
doesn't seem to have much impact on Rails' opinion.
This is a simple case of having the database adaptor allow what the
database itself considers legal. Not only legal, but Rails is preventing
the db from using its own default behavior. It doesn't matter if the
DB2, Oracle or Sybase worlds think its dumb. Its an adaptor for MySQL.
BTW everyone -- I appreciate the discussion, and the points being made.
Not convinced I'm wrong yet, but I do appreciate the time being taken to
read & respond.
I would argue this is not an issue of "most" or even "standard" -- most
code on the planet is probably written with camelCase names -- which
doesn't seem to have much impact on Rails' opinion.
The real problem is that you *can't* retrieve that data again. Rails
needs to coerce those values into a Date or Time, neither of which
accept 0000-00-00. So we can't support it when reading data, there's
not much point letting you write it. After all, you can't construct
the time object to save in the first place.
Given that ruby can't support that value, coercing to nil seems at
least reasonable.
