“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
January 3rd, 2008 at 7:57 pm
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.
January 4th, 2008 at 11:08 am
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.
January 4th, 2008 at 2:18 pm
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.
February 21st, 2008 at 10:08 am
Hi!
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?
February 21st, 2008 at 12:07 pm
I’d be interested in working with you on that, yes.
March 29th, 2008 at 1:55 am
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!
Steve
April 2nd, 2008 at 5:05 am
Thank you for explaining this!
March 22nd, 2011 at 7:07 am
still useful in 2011.
April 30th, 2011 at 7:58 am
Yes, unfortunately still relevant in 2011…
Has there been any official progress made on this? I haven’t seen anything on Google / stackoverflow.
Thanks!
May 4th, 2011 at 10:46 pm
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…
August 26th, 2012 at 7:19 am
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
August 26th, 2012 at 7:20 am
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”)
end
August 4th, 2013 at 2:31 am
Is there still no official solution in 2013?