Ruby: Parse Time String to accept in MySql as a valid date time

Question

I am parsing set of xml records that has time stamp strings. My end goal is to parse these records and put them in mysql database. I am new to ruby and script was put together by looking at other examples in online:

Here is how my xml records looks like:

  <row Id="253828" UserId="56959" Name="Teacher" Date="2009-03-08T00:27:31.807" />
  <row Id="253829" UserId="22221" Name="Popular Question" Date="2009-03-08T00:32:32.217" />

This is how the ruby method looks like:

require 'date'

    @st = @my.prepare("insert into badge(id, user_id, name, created) values(?, ?, ?, ?)")
    if element == 'row'
      @st.execute(attributes['Id'], attributes['UserId'], attributes['Name'], DateTime.parse(attributes['Date']).strftime("%F %T"))
    end

But the problem is after parsing close to 10,000 something records I get a mysql error:

Incorrect datetime value: '2009-03-08 02:02:32' for column 'created' at row 1 (Mysql::Error)

I started to check this in repl and here is the out put of repl:

irb(main):016:0> DateTime.parse('2009-03-08T00:27:31.807').strftime("%F %T")
=> "2009-03-08 00:27:31"
irb(main):017:0> DateTime.parse('2009-03-08T00:32:32.217').strftime("%F %T")
=> "2009-03-08 00:32:32"

If you look carefully you see that orginal time string had 2009-03-08T00:27:31.807 and value 807 didn't make any impact on the conversion. Because if we change the two time strings to be exact except the different is the last three digits after . then I have a feeling the conversion is incorrect. I wonder whats the best way to convert this to valid mysql time stamp while keep the the integrity of the data.

Hypothetically, lets say time string is 2009-03-08T00:27:31.807 and 2009-03-08T00:27:31.507 the trying this on repl would give us:

irb(main):018:0> DateTime.parse('2009-03-08T00:27:31.807').strftime("%F %T")
=> "2009-03-08 00:27:31"
irb(main):019:0> DateTime.parse('2009-03-08T00:27:31.507').strftime("%F %T")
=> "2009-03-08 00:27:31"

They are both same time stamp but are they?

I found following post in StackExchange DataBase Admin section: https://dba.stackexchange.com/questions/48704/mysql-5-6-datetime-incorrect-datetime-value-2013-08-25t1700000000-with-er not sure if I should do something like that.

To give a complete picture from the mysql side: Here is how my table looks like:

CREATE TABLE badge
(
    id INT NOT NULL PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(40) NULL,
    created TIMESTAMP
);

For representing the timestamp in Ruby, you also need to add %N to get the milliseconds. DateTime.parse('2009-03-08T00:27:31.807').strftime("%F %T.%N") => "2009-03-08 00:27:31.807000000" — Sean Redmond
– Sean Redmond, Commented Feb 9, 2014 at 14:38
@CBroe wow thats a disaster because then any analysis I do on this data will not be accurate. What steps can I take? — add-semi-colons
– add-semi-colons, Commented Feb 9, 2014 at 14:51
@SeanRedmond Thanks, looks like now we are capturing it but as CBroe mentioned MySQL will drop this. I wonder what is the best practice on this. I bet I am not the first to see this issue :) — add-semi-colons
– add-semi-colons, Commented Feb 9, 2014 at 14:54
If you need that fractional seconds data, you will have to store it in a separate column. — C3roe
– C3roe, Commented Feb 9, 2014 at 15:13

O. Jones · Accepted Answer · 2014-02-09 15:13:26Z

2

In MySQL, the resolution of stored time information is one second. The millisecond values you have shown (.807) etc, can't be stored. As far as your table is concerned, 2009-03-08T00:27:31.807 and 2009-03-08T00:27:31.507 happened at precisely the same second.

As of MySQL version 5.6.4, fractional time data can be stored if you define the column's data type correctly. See https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

You may be able to succeed in this xml-to-MySQL conversion process by passing your ISO 8601 timestamps as text strings to the database, then doing your date/time conversion using a MySQL query with a form like this:

 INSERT INTO whatever
    ...
  VALUES 
     (..., 
      CAST(STR_TO_DATE( ? ,'%Y-%m-%dT%H:%i:%s.%f') AS DATETIME),    
      ...)

I've tried this on a variety of server versions. It seems to be version-proof.

If you're using MySQL prior to 5.6.4, and you need to keep the milliseconds, you have a pain in the neck in your near future. You might consider storing your timestamps as UNIX style microseconds in a bigint. Your application code will need to use strftime("%Q") to get the microsecondy UNIX timestamps.

If any of your dates are before 1-Jan-1970, this won't work either.

edited Feb 9, 2014 at 15:13

answered Feb 9, 2014 at 14:57

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

add-semi-colons Over a year ago

Just edited the question to add the table create info. also how in ruby i do the insert. Do you think I should create the column differently?

add-semi-colons Over a year ago

so I should first get the unix time using strftime("%Q") and then use that time to insert to the database and then convert afterwards.

O. Jones Over a year ago

It's hard to be more specific about what you should do without knowing what you're trying to do. For many applications, one-second time resolution is plenty. It may not be for this application. But if you need millisecond time stamps, it helps to know why.

add-semi-colons Over a year ago

XML data files are data dumps and I creating a MySQL database to store them to do analytics works such as behavioral analysis. Mili-seconds are not that important in that case but i thought I ll use strftime("%Q") as you said to get the UNIXTIME and store it the database in that way i don't loose any information. And during analysis just convert from ruby or python or R to get year,month,date,time...

Collectives™ on Stack Overflow

Ruby: Parse Time String to accept in MySql as a valid date time

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related