Forum: Ruby on Rails DateTime attribute not formatted properly for MySQL

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.
Fyodor G. (Guest)
on 2007-01-28 03:44
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
Mike G. (Guest)
on 2007-01-29 05:02
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
Mike G. (Guest)
on 2007-01-30 08:07
nothing?
Fyodor G. (Guest)
on 2007-01-30 17:04
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
This topic is locked and can not be replied to.