Rails & SQLite3: Date Mis-Match

Question

Rails is returning the wrong date from my database records. For my model ("Target"), if I run in script/console:

Target.find :all

I get all my records with the "recorded_on" field reading dates from "2000-01-01 xx:xx:xx".

If I take the same database and run in the sqlite3 console:

SELECT * FROM targets;

I get all my records with the "recorded_on" field reading dates from "2009-04-22 xx:xx:xx", which are the correct dates.

The time values seem to be correct between the two, only the date values are off.

What's going on?

Answer 1

The reliable translation of times between rails and your database depends on lots of rails and database configuration voodoo that I had no patience to tackle.

My final solution was to stop storing time values as times in the database. I store ints:

Modelname.time_column = Time.now().to_i

(Actually, I do the to_i bit in an override of the attribute's setter and reverse the process in the getter:)

Time.at(read_attribute(:time_column))

It's a hack, but I'm happy with its simplicity and how simple it has been to move it between configurations and development machines.

The drawback is, of course, that sql gives you an old-fashioned int when you query it. MySQL may have ways to convert, but I doubt that SQLite will.

Answer 2

It's probably not an answer to your question but I just learned this trick the other day:

Time.now.to_s(:db)