CODEWITHSUNDEEP
mysqlruby
add-semi-colons
add-semi-colons

Reputation: 18810

MySQL: Incorrect Date Value

I am parsing an xml file that has following data structure: 

  <row Id="253858" UserId="40883" Name="Scholar" Date="2009-03-08T01:52:32.570" />
  <row Id="253860" UserId="19483" Name="Supporter" Date="2009-03-08T01:57:31.733" />
  <row Id="253861" UserId="74951" Name="Autobiographer" Date="2009-03-08T02:02:32.390" />

I used a ruby script to parse this data and insert them into a mysql database. Here is how my data table looks like: 

+---------+-------------+------+-----+-------------------+-----------------------------+
| Field   | Type        | Null | Key | Default           | Extra                       |
+---------+-------------+------+-----+-------------------+-----------------------------+
| id      | int(11)     | NO   | PRI | NULL              |                             |
| user_id | int(11)     | NO   |     | NULL              |                             |
| name    | varchar(40) | YES  |     | NULL              |                             |
| created | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------+-------------+------+-----+-------------------+-----------------------------+

This xml file has large number of records and until the parser gets to 3rd record I don't get any error and all the data get parse correctly and insert into the table: 

| 253857 |   23658 | Critic             | 2009-03-08 01:52:32 |
| 253858 |   40883 | Scholar            | 2009-03-08 01:52:33 |
| 253860 |   19483 | Supporter          | 2009-03-08 01:57:32 |
+--------+---------+--------------------+---------------------+

But when we get to the record with row Id="253861" I get the following mysql error: 

load.rb:21:in `execute': Incorrect datetime value: '2009-03-08T02:02:32.390' for column 'created' at row 1 (Mysql::Error)
    from load.rb:21:in `on_start_element'
    from load.rb:133:in `parse'
    from load.rb:133:in `<main>'

incase if you need the ruby method that insert records:

  def on_start_element(element, attributes)
    if element == 'row'
      @st.execute(attributes['Id'], attributes['UserId'], attributes['Name'], attributes['Date'])
    end
  end
end

I don't think this is related to script, because I extracted the record from xml file and tried to insert directly into my mysql table and I got the following error: 

mysql> insert into badge values(253861,74951,'Autobiographer','2009-03-08T02:02:32.390');
ERROR 1292 (22007): Incorrect datetime value: '2009-03-08T02:02:32.390' for column 'created' at row 1

I also tried to insert the record that is after the above record in the xml file and I got the same results:

mysql> insert into badge values(253862,49628,'Teacher','2009-03-08T02:12:30.807');
ERROR 1292 (22007): Incorrect datetime value: '2009-03-08T02:12:30.807' for column 'created' at row 1

So something in that date string makes mysql unhappy. What I couldn't figure out is that date and previous records that has the same date structure didn't have any problem. Hope I have explain the problem clear with information.

Upvotes: 1

Views: 1484

Answers (1)

bjhaid
bjhaid

Reputation: 9752

Since you said you insert dates in a couple of places I would suggest you write and helper method to do date conversions that you can reuse everywhere you need to insert dates

require 'date'
def iso8601_to_mysql_datetime(date)
  DateTime.parse(date).to_time.strftime("%F %T")
end

iso8601_to_mysql_datetime('2009-03-08T02:02:32.390')
=> "2009-03-08 02:02:32"

NOTE: The above converts a ISO8601 into a string that MySQL understands MySQL date and time literal documentation can be found here:

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

Upvotes: 5

Related Questions