“Time” columns and Rails

In SQL, there is a data type TIME which holds time of day information without date information. There is no equivalent class in Ruby. Rails (ActiveRecord) deals with this by adding some dummy date data and using the standard Ruby Time class. Currently the dummy date used is 2000-01-01, though since I can’t find any documentation on this, I guess it could be changed to something else in the future.

This means you can run into some odd issues if you aren’t careful when comparing values from SQL TIME columns with times in your code.

For example, if you have a record r in your database with the value “13:30:00″ stored in a column called arrival_time of SQL data type TIME:

Time.parse("2000-01-01 13:30") == r.arrival_time       # unless the dummy date AR uses has changed

"13:30:00" == r.arrival_time_before_type_cast

Time.parse("13:30") != r.arrival_time         # unless today is 1st January 2000

13 Responses to ““Time” columns and Rails”

  1. Will Green Says:

    Seems like you should be using a DATETIME column type. That’s what Rails uses for the magical created_at and updated_at fields.

    I don’t think TIME is standard SQL, anyway.

  2. Rebecca Says:

    Firstly, TIME is definitely given as a data type in the SQL:2003 standard. Take a look at http://www.wiscorp.com/sql_2003_standard.zip for a draft if you don’t have a copy yourself.

    Secondly, I’m using a TIME column because I specifically don’t want date information associated with the time information.

    Why would you want date information along with your times if you were writing, say, a train timetable? You want the time the train leaves each station, and what days of the week it runs on.

  3. Anthony Green Says:

    Rebeccas right, schedules which contain offset start/end times and durations require you to deal with ‘time’ seperate from any concept of a date. I havaen’t yet found a Ruby library that deals with these issues so in the Rails projects I roll my own special Duration class and use compsed_of to link it to the object attribute.

  4. Uwe Kubosch Says:


    This is definately overdue. I am seriously thinking of starting a project on this, but I really don’t want to duplicate other efforts.

    I am thinking of duplicating the design of the TimeAndMoney project on SourceForge ( http://sourceforge.net/projects/timeandmoney/ ). It covers TimePoint, CalendarDate, TimeOfDay, Duration, intervals, and a few others. TimePoint is equal to Time in Ruby.

    The project would implement the base classes, and separate mappers for ActiveRecord.

    I need help to research existing projects to avoid duplicate efforts, and testing with real-world usage. Any developers are of course welcome to join.

    Anyone interrested in participating?

  5. Rebecca Says:

    I’d be interested in working with you on that, yes.

  6. Steve Says:

    I am *so* pleased to have stumbled across this discussion. Newbie that I am in Rails, I thought I was missing something basic. I’m trying to develop a booking application for the photo studio I work for. Most of our assignments are only a few hours long. Few span multiple days. I am happy to use a datetime object for when an assignment starts, but I’m astonished that I can’t find a way to set a time exclusive of a date for when it ends. For example - a hypothetical assignment might be “Monday, May 5, 2008 from 10AM - 3:45PM”. Up until the 3:45PM part a standard datetime entry in the database is perfect. But I can’t believe I can’t use a form helper to just set an hour/minute end time. I didn’t find the “dummy date” to be 2000-01-01 though - my experiments so far seem to indicate that the current date is tacked onto the time. That means that in the example I gave above what is really being stored in the database is basically this (given that I am writing this on March 28, 2008):
    “Monday, May 5, 2008 from 10AM - Friday, March 28, 2008 3:45PM”
    That’s nuts!
    Anyway - if I’m missing something obvious maybe someone can point me in the right direction. And if in fact this is not currently implemented in Rails I sure hope Uwe and Rebecca come up with a solution pretty quickly!


  7. chris Says:

    Thank you for explaining this!

  8. jonathan Says:

    still useful in 2011. :(

  9. Patrick Says:

    Yes, unfortunately still relevant in 2011… :(

    Has there been any official progress made on this? I haven’t seen anything on Google / stackoverflow.


  10. Rebecca Says:

    No progress on my part, I’m afraid, Patrick. I stopped working with ruby/rails a couple of months after my original post and so the incentive to fix it disappeared…

  11. Chris Macke Says:

    Figured out a workaround. This will remove the date from the time field and then return it formatted however you want. I agree, it’s odd that this is even necessary. This approach is also useful to reformat stuff if you’re using to_json and want to avoid Rails default formatting for a particular model field.

    class AppointmentTime

  12. Chris Macke Says:

    The code was removed from my last post, probably because of the closing bracket. This goes inside of your model:

    def time
    self[:time] && self[:time].strftime(”%l:%M %p”)

  13. Michael Yagudaev Says:

    Is there still no official solution in 2013?

Leave a Reply