DateTime attribute not formatted properly for MySQL

#1

I have a row in in MySQL database with a DATETIME value of “1934-11-18
10:58:06”, which is outside of range for a Time object. I guess Rails is
smart enough to recognize that fact and therefore pulls that value in as
a DateTime object, as opposed to Time, which it uses for dates after
year of 1969.

Now… when I try to go save that object back into the database, it
gives me the following error:

“ActiveRecord::StatementInvalid: Mysql::Error: Incorrect datetime value:
‘1934-11-18T10:58:06Z’ for column ‘call_date’ at row 1”

It looks like the T and the Z in the date are what is used by default to
format a DateTime object, but MySQL does not seem to appreciate it.

What workaround would you suggest?

Thanks,
Fyodor Golos

#2

nothing?

#3

Fyodor Golos wrote:

I have a row in in MySQL database with a DATETIME value of “1934-11-18
10:58:06”, which is outside of range for a Time object. I guess Rails is
smart enough to recognize that fact and therefore pulls that value in as
a DateTime object, as opposed to Time, which it uses for dates after
year of 1969.

Now… when I try to go save that object back into the database, it
gives me the following error:

“ActiveRecord::StatementInvalid: Mysql::Error: Incorrect datetime value:
‘1934-11-18T10:58:06Z’ for column ‘call_date’ at row 1”

It looks like the T and the Z in the date are what is used by default to
format a DateTime object, but MySQL does not seem to appreciate it.

What workaround would you suggest?

Thanks,
Fyodor Golos

I am having the same issue! I am trying to do these tutorials from
Lynda.com and on his example he inserts a date of 1966-01-01 12:00:00
into a datetime colum in a mysql database. I then get the same error he
does above, but the example in the lynda.com movie does not, his inserts
correcly. but if i adjust the year to any year > 1970, it works… to my
understanding this is only a problem with timestamp, why is this
happening on a value that is going into a datetime field??!?!?! I can’t
find a solution… please help.

Thanks,
Mike Glover

#4

Ok… Here is what I found. I am using Rails 1.2.1, ActiveRecord 1.5.1.
Take a look at the following file from ActiveRecord library:

activerecord-1.15.1\lib\active_record\connection_adapters\abstract\quoting.rb

Method “quote(value, column = nil)”, Lines 27 and 28:

when Date then “’#{value.to_s}’”
when Time, DateTime then “’#{quoted_date(value)}’”

Well… since DateTime is a subclass of Date, first “when” gets
evaluated, calling Date.Time#to_s method, which outputs the string with
T and Z, which MySQL does not like. It really should have fallen through
to next “when”, so that “quote_date(value)” is called, but it didn’t
because of the order of “when” statements.

I do not claim to be an expert in Ruby on Rails just yet, so I can’t say
with any certainity that switching those two lines will fix the problem
and won’t break anything else. But if someone more knowledgeable than me
can confirm it, I will go ahead and submit a patch for Rails 1.2.1.

For what it’s worth, this particular code looks different in latest
version of Rails (subversion’s trunk). I have not tested my code with
that version and do not know whether it exhibits the same behavior and
needs fixing or not. Hopefully someone else can chime in.

Fyodor Golos