Mysql dates


#1

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


#2

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… :slight_smile:

Beginners, beware! Read the above text carefully BEFORE choosing a DB.

Nuff said…


– Tom M.


#3

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.


#4

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.


#5

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


#6

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.


#7

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


#8

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.

#9

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 :slight_smile: 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


#10

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…
:slight_smile:

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


#11

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.


#12

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. :slight_smile:

Not towards you, towards the…oh never mind.

Religious war ended. I hereby sign a truce.


– Tom M.