does anyone know of a way to make the date_helper deal with mysql dates with 00 values in them? I have lots of dates that are like the following: 2005-04-00 2005-00-00 and I need to set null values in a date_select for elements that are 00. These are valid dates in mysql. In the absence of an immediate solution to the above, I''ve been trying to find out how InstanceTag.new works so I can overload the to_date_select_tag() method. Has anyone ever done this, or have any pointers as to how I might approach the problem this way? I''m in a bit of a bind because whenever I use '':include_blank => true'' in a date_select form, the result is always applied to the date field in the db as eg. Jan 01, which means that as far as I can see blank values aren''t possible. Am I missing something? thanks dorian -- I do things for love or money -- +44 (0)7941 219 501 -- aim:oulalipo | yahoo:tachekent
On Jan 23, 2006, at 1:27 PM, Dorian Mcfarland wrote:> does anyone know of a way to make the date_helper deal with mysql > dates with 00 values in them? > I have lots of dates that are like the following: > 2005-04-00 > 2005-00-00 > and I need to set null values in a date_select for elements that > are 00. These are valid dates in mysql.I''m *so* fighting the temptation to start a DB religious flame war here... :-) Beginners, beware! Read the above text carefully BEFORE choosing a DB. Nuff said... -- -- Tom Mornini
Has less to do with the db software and more to do with the person creating the database. The better.subjective?() way to do a date/datetime in mysql is no default and allow NULL. Bob Silva> -----Original Message----- > From: rails-bounces@lists.rubyonrails.org [mailto:rails- > bounces@lists.rubyonrails.org] On Behalf Of Tom Mornini > Sent: Monday, January 23, 2006 5:25 PM > To: rails@lists.rubyonrails.org > Subject: Re: [Rails] mysql dates > > On Jan 23, 2006, at 1:27 PM, Dorian Mcfarland wrote: > > > does anyone know of a way to make the date_helper deal with mysql > > dates with 00 values in them? > > I have lots of dates that are like the following: > > 2005-04-00 > > 2005-00-00 > > and I need to set null values in a date_select for elements that > > are 00. These are valid dates in mysql. > > I''m *so* fighting the temptation to start a DB religious flame war > here... :-) > > Beginners, beware! Read the above text carefully BEFORE choosing a DB. > > Nuff said... > > -- > -- Tom Mornini > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Less to do with the DB software? IMHO, if I say I''m storing a date, it shouldn''t allow invalid dates! Would it be any less weird to have it not enforce any other defined constrains, such as foreigns keys (sic...in old versions), lengths, not nulls, etc? -- -- Tom Mornini On Jan 23, 2006, at 6:55 PM, Bob Silva wrote:> Has less to do with the db software and more to do with the person > creating > the database. > > The better.subjective?() way to do a date/datetime in mysql is no > default > and allow NULL. > > Bob Silva > >> -----Original Message----- >> From: rails-bounces@lists.rubyonrails.org [mailto:rails- >> bounces@lists.rubyonrails.org] On Behalf Of Tom Mornini >> Sent: Monday, January 23, 2006 5:25 PM >> To: rails@lists.rubyonrails.org >> Subject: Re: [Rails] mysql dates >> >> On Jan 23, 2006, at 1:27 PM, Dorian Mcfarland wrote: >> >>> does anyone know of a way to make the date_helper deal with mysql >>> dates with 00 values in them? >>> I have lots of dates that are like the following: >>> 2005-04-00 >>> 2005-00-00 >>> and I need to set null values in a date_select for elements that >>> are 00. These are valid dates in mysql. >> >> I''m *so* fighting the temptation to start a DB religious flame war >> here... :-) >> >> Beginners, beware! Read the above text carefully BEFORE choosing a >> DB. >> >> Nuff said... >> >> -- >> -- Tom Mornini >> >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Tom Mornini wrote:> IMHO, if I say I''m storing a date, it shouldn''t allow invalid dates!That would depend on what you count as a valid date. IMHO, 2004-00-00 2004-01-00 2004-01-23 are all valid dates and are acceptable to mysql. 2004-00-01 0000-02-23 are invalid dates and get turned into 0000-00-00 by default While I''m here though, it seems that ruby''s Date object always parses the missing elements into Jan 1st. Is that really so? Dorian> > Would it be any less weird to have it not enforce any other defined > constrains, such as foreigns keys (sic...in old versions), lengths, > not nulls, etc? > > -- -- Tom Mornini > > > On Jan 23, 2006, at 6:55 PM, Bob Silva wrote: > >> Has less to do with the db software and more to do with the person >> creating >> the database. >> >> The better.subjective?() way to do a date/datetime in mysql is no default >> and allow NULL. >> >> Bob Silva-- I do things for love or money -- +44 (0)7941 219 501 -- aim:oulalipo | yahoo:tachekent
Tom Mornini wrote:> Less to do with the DB software? > > IMHO, if I say I''m storing a date, it shouldn''t allow invalid dates!Those dates aren''t invalid. They just mean "April, 2005", or "2005". 31st February, on the other hand... -- Alex
On Jan 23, 2006, at 8:16 PM, Dorian Mcfarland wrote:> Tom Mornini wrote: >> IMHO, if I say I''m storing a date, it shouldn''t allow invalid dates! > > That would depend on what you count as a valid date. > > IMHO, > 2004-00-00 > 2004-01-00I''ve never seen either of those on a 2004 calendar...> 2004-01-23Nothing surprising there.> are all valid dates and are acceptable to mysql.That doesn''t make sense.> 2004-00-01If the first is allowed, why not this one?> 0000-02-23I think I can understand this one being invalid, unless you can select which ancient calendar you want to use.> are invalid dates and get turned into 0000-00-00 by defaultAh, in invalid date gets turned into an invalid date...makes perfect sense.> While I''m here though, it seems that ruby''s Date object always > parses the missing elements into Jan 1st. Is that really so?Not if you''re using a DB that doesn''t exhibit insane behavior. -- -- Tom Mornini
That''s all as well as can be, but it does at least allow me to rationally degrade the specificity of my dates which is the problem I have with the dataset I have inherited. If I was using a database that didn''t let me be slightly less detailed I would end up with year, month and day fields for every date I needed to store. That might seem to make more sense, but as far as I can see that''s exactly what mysql is letting me do within a single column!>> IMHO, >> 2004-00-00 >> 2004-01-00 > > I''ve never seen either of those on a 2004 calendar...I''m sure you''ve seen a 2004 bottle of wine, or a ''Best before Jan 2004'' label. They are all unique dates. Should the usage of dates be only limited to specific days, not specific months or years?>> 2004-01-23 > > Nothing surprising there. > >> are all valid dates and are acceptable to mysql. > > That doesn''t make sense. >> 2004-00-01 > > If the first is allowed, why not this one?well that might make sense to cron :) but there is only one 2004, and 12 first of the months.>> are invalid dates and get turned into 0000-00-00 by default > > Ah, in invalid date gets turned into an invalid date...makes perfect sense. >> While I''m here though, it seems that ruby''s Date object always parses >> the missing elements into Jan 1st. Is that really so? > > Not if you''re using a DB that doesn''t exhibit insane behavior.I think it''s more insane to turn my fuzzy dates into specific ones when they''re wrong. d> ---- Tom Mornini > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-- I do things for love or money -- +44 (0)7941 219 501 -- aim:oulalipo | yahoo:tachekent
Perhaps, the following terminology would be useful. I believe Dorian is
trying to store periods instead of dates:
2004-01-01 is a period describing a day in some timezone, 2004-01-01 is
also a date.
2004-01-00 is a period describing a month (again in some timezone),
2004-01-00 is not a date.
2004-00-00 is another period describing a year ...
Personally I would store these periods in a CHAR column, or with 2 date
columns (begin/end) and keep away from weird MySQL logic that you can
not control.
Erik.
On Jan 23, 2006, at 8:55 PM, Dorian Mcfarland wrote:>>> are invalid dates and get turned into 0000-00-00 by default >> Ah, in invalid date gets turned into an invalid date...makes >> perfect sense. >>> While I''m here though, it seems that ruby''s Date object always >>> parses the missing elements into Jan 1st. Is that really so? >> Not if you''re using a DB that doesn''t exhibit insane behavior. > > I think it''s more insane to turn my fuzzy dates into specific ones > when they''re wrong.I assure you, my comment was made with utmost sarcasm and derision. :-) Not towards you, towards the...oh never mind. Religious war ended. I hereby sign a truce. -- -- Tom Mornini
I turned to a VARCHAR column in the end as I couldn''t get rails to do anything useful with the dates|periods anyway. lesson learned, sort of... :) must have been the peace. d Tom Mornini wrote:> On Jan 23, 2006, at 8:55 PM, Dorian Mcfarland wrote: > >>>> are invalid dates and get turned into 0000-00-00 by default >>> Ah, in invalid date gets turned into an invalid date...makes perfect >>> sense. >>>> While I''m here though, it seems that ruby''s Date object always >>>> parses the missing elements into Jan 1st. Is that really so? >>> Not if you''re using a DB that doesn''t exhibit insane behavior. >> >> I think it''s more insane to turn my fuzzy dates into specific ones >> when they''re wrong. > > I assure you, my comment was made with utmost sarcasm and derision. :-) > > Not towards you, towards the...oh never mind. > > Religious war ended. I hereby sign a truce. > > ---- Tom Mornini > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-- I do things for love or money -- +44 (0)7941 219 501 -- aim:oulalipo | yahoo:tachekent
Dorian Mcfarland wrote:> Tom Mornini wrote: > >> IMHO, if I say I''m storing a date, it shouldn''t allow invalid dates! > > > That would depend on what you count as a valid date. > > IMHO, > 2004-00-00 > 2004-01-00 > 2004-01-23 > are all valid dates and are acceptable to mysql.Just because mysql is stupid enough to accept them doesn''t make them any more valid.> > 2004-00-01 > 0000-02-23 > are invalid dates and get turned into 0000-00-00 by default >It should error not change the data.> While I''m here though, it seems that ruby''s Date object always parses > the missing elements into Jan 1st. Is that really so? > > Dorian > >> >> Would it be any less weird to have it not enforce any other defined >> constrains, such as foreigns keys (sic...in old versions), lengths, >> not nulls, etc? >> >> -- -- Tom Mornini >> >> >> On Jan 23, 2006, at 6:55 PM, Bob Silva wrote: >> >>> Has less to do with the db software and more to do with the person >>> creating >>> the database. >>> >>> The better.subjective?() way to do a date/datetime in mysql is no >>> default >>> and allow NULL. >>> >>> Bob Silva > >