Tutorial :Why does ActiveRecord generated SQL includes Date for a Time column?



Question:

When I create a SQL Time column I get a pure time, ie. no date. But when I use the Ruby Time class I get a date as well. The problem is when do a 'find' the generated SQL includes a date and I seem to be getting weird results.

The Table

start_date: time  end_time: time  day_of_week: string  

ActiveRecord

def self.now_playing      self.find(:first, :conditions => ['day_of_week = ? AND end_time > ? AND start_time < ?',  Time.now.strftime('%A'), Time.now, Time.now])  end  

The SQL

SELECT * FROM `schedules` WHERE (day_of_week = 'Saturday' AND end_time > '2009-06-20 10:19:59' AND start_time < '2009-06-20 10:19:59') LIMIT 1  

The SQL generated includes a date, could this be why I'm getting odd results, for example no record returned when there is a schedule for the given time? However if the column is a pure time column should MySQL not ignore the date part?


Solution:1

Time in Ruby is more like Timestamp... always has a date. ActiveSupport has built-in conversions for this, so if the date is screwing up your results, you can use #to_s(:format) to get the results you need. By default, there is a :time option, but it only returns hours / minutes (%H:%M) , if you needed seconds as well you'd have to add another format ...

Create an initializer called time_formats.rb or something and add:

Rails 2-ish

ActiveSupport::CoreExtensions::Date::Conversions::DATE_FORMATS.merge!({    :time_long => "%H:%M:%S"  })  

Rails 3

Time::DATE_FORMATS.merge!(    :time_long => "%H:%M:%S"  )  

Then in your finder, just do like so:

def self.now_playing    time = Time.now    self.find(:first, :conditions => ['day_of_week = ? AND end_time > ? AND start_time < ?',  time.strftime('%A'), time.to_s(:time_long), time.to_s(:time_long)])  end  

More info can be found here: http://jasonseifer.com/2010/03/10/rails-date-formats

Or here: http://brian.rarevisions.net/extending-date-formats-in-rails-3


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »