date-agnostic times

rubyonrails-talk
1

Let's say I have a Trains model with arrival_time specified in the migration as a :time. How can I structure the model and the query so that I can find all trains with an arrival_time between 7:30 and 9:30am, no matter the day? In my migration I specified :time but of course the database always contains a date and using :conditions => ['arrival_time > ?', time1] uses the date.

What am I missing? There has to be an easy way to do this...

2

Mark Thomas wrote:

Let's say I have a Trains model with arrival_time specified in the migration as a :time. How can I structure the model and the query so that I can find all trains with an arrival_time between 7:30 and 9:30am, no matter the day? In my migration I specified :time but of course the database always contains a date and using :conditions => ['arrival_time > ?', time1] uses the date.

What am I missing? There has to be an easy way to do this...

Could you just track the time as an int like military time? (0000 - 2359) You'd have to validate on entry to ensure that the minutes portion of a 'time' value never exceeded 59, but it should be doable.

3

Yeah, I guess I'll have to write my own Time class, although I'll probably call it Clock to avoid confusion. Not sure why ruby needs both DateTime and Time, when they both represent dates AND times.

4

What database are you usingm and can you change it? MySQL 5, I know for sure, has it's own Time type that does not include a date, and indeed AR's :time type maps to this.

An alternative is simply to always store the same day in every record (or create another column and do the same). Then your query will work correctly.

5

What database are you usingm and can you change it? MySQL 5, I know for sure, has it's own Time type that does not include a date, and indeed AR's :time type maps to this.

Thanks for the info. Does it ignore the date portion of a ruby Time object?

I was developing on SQLite but I can change it to MySQL. From an idealistic point of view, I hate database-dependent code, especially this, which would mysteriously break when switched to another DB. But in this particular situation I don't see that being an issue.

An alternative is simply to always store the same day in every record (or create another column and do the same). Then your query will work correctly.

Another good idea. Thanks.