« Create Glass Buttons in iOS for iPhone | Main | RailsConf 2011: Rails for Zombies »

May 11, 2011

Ruby on Rails: DateTime Records with Microseconds

I'm working on a new web app for internal use at my company, from the ground up with Ruby on Rails.

One thing that has emerged as a critical component; having database records timestamped with more granularity than seconds. The solution was more involved than I would have guessed. Figured I'd log the steps since it took many hours of research and engineering effort.

1) Search for how to store microseconds in MySQL. Not possible in a traditional datetime or timestamp field. Explore all of the workarounds, none of which are ideal for the application. Realize there's no good current solution and a lot of folks have been begging since 2005 for someone at MySQL to fix so little hope it will come. See MySQL bug repot here.

2) Swallow hard and enter less familiar territory of installing and getting PostgreSQL running with the app. Lots of reading about PostgreSQL setup, configuration, and administration. Get roles set up, connected with the command-line tool, and get Rails talking to PostgreSQL.

3) Repopulate data from data sources (easier for me than the various MySQL -> Postgres transfer instructions I'd looked at)

4) Put code into Rails app to insert datetime with microseconds (this may get finessed):
...
datetime = DateTime.now
microseconds = Time.now.usec
params[:event][:datetime] = "#{datetime.to_s(:db)}.#{microseconds}"
@event = Event.new(params[:event])
...

5) Back to building functionality I was originally attempting without microseconds but was failing.

That was about two days of work, compressed into one day and a long night.

Although I've used Postgres a little bit before, I know MySQL extremely well and am comfortable in any situation that one comes across with that database (replication, disk failure, query performance, I've done it all). Postgres is a new frontier. I'm quickly becoming comfortable using it and the \ commands but know there's a lot more to be learned before I'm confortable running a production app on it.

I guess the title of this post could also be "Switching from MySQL to PostgreSQL to get DateTime records with Microseconds."

Posted by mike at May 11, 2011 12:06 PM