Forum: Ruby on Rails mysql dates

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Dorian M. (Guest)
on 2006-01-23 23:27
(Received via mailing list)
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
Tom M. (Guest)
on 2006-01-24 03:24
(Received via mailing list)
On Jan 23, 2006, at 1:27 PM, Dorian M. 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 M.
Bob S. (Guest)
on 2006-01-24 04:55
(Received via mailing list)
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 S.
Tom M. (Guest)
on 2006-01-24 05:34
(Received via mailing list)
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 M.
Dorian M. (Guest)
on 2006-01-24 06:19
(Received via mailing list)
Tom M. 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

>> 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 S.

--
I do things for love or money
--
+44 (0)7941 219 501
--
aim:oulalipo | yahoo:tachekent
Alex Y. (Guest)
on 2006-01-24 06:19
(Received via mailing list)
Tom M. 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
Tom M. (Guest)
on 2006-01-24 06:37
(Received via mailing list)
On Jan 23, 2006, at 8:16 PM, Dorian M. wrote:

> Tom M. 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

I've never seen either of those on a 2004 calendar...

> 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?

> 0000-02-23

I 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 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.

--
-- Tom M.
Dorian M. (Guest)
on 2006-01-24 06:55
(Received via mailing list)
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 M.
>
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails

--
I do things for love or money
--
+44 (0)7941 219 501
--
aim:oulalipo | yahoo:tachekent
Erik van Oosten (Guest)
on 2006-01-24 08:52
(Received via mailing list)
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.
Tom M. (Guest)
on 2006-01-24 09:50
(Received via mailing list)
On Jan 23, 2006, at 8:55 PM, Dorian M. 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 M.
Dorian M. (Guest)
on 2006-01-24 21:07
(Received via mailing list)
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 M. wrote:
>> when they're wrong.
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails

--
I do things for love or money
--
+44 (0)7941 219 501
--
aim:oulalipo | yahoo:tachekent
Neil D. (Guest)
on 2006-01-25 01:09
(Received via mailing list)
Dorian M. wrote:
> 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.
This topic is locked and can not be replied to.