Correct way to search datetime column by date

rubyonrails-talk
1

Hi,

I have a flight model. I want to find all flights with a specific arrival airport, a specific departure airport and which depart on a certain date.

My problem is that the departure date is a datetime column in the db and I wish to search this column by date (not datetime).

I have written the following:

@flights = Flight.find(:all, :conditions => ['arrival_airport_id = ? and departure_airport_id = ? and departure_datetime >= ? and departure_datetime < ?', @arrival_airport_id, @departure_airport_id, @date, @date+1])

This works but the code seems long and ugly.

Is this an acceptable way to do what I want, or can someone point me to a better, 'prettier' method?

Thanks very much in advance.

2

You could replace "departure_datetime >= ? and departure_datetime < ?" with "departure_datetime BETWEEN ? AND ?"...

Doesn't help much though...

3

Jim Burgess wrote in post #973132:

I have a flight model. I want to find all flights with a specific arrival airport, a specific departure airport and which depart on a certain date.

My problem is that the departure date is a datetime column in the db and I wish to search this column by date (not datetime).

@flights = Flight.find(:all, :conditions => ['arrival_airport_id = ? and departure_airport_id = ? and departure_datetime >= ? and departure_datetime < ?', @arrival_airport_id, @departure_airport_id, @date, @date+1])

Rails 2: @flights = Flight.find(:conditions => { :arrival_airport_id => departure_airport_id, :departure_datetime => @date...(@date + 1.day) })

Rails 3: @flights = Flight.where(:arrival_airport_id => departure_airport_id, :departure_datetime => @date...(@date + 1.day))

SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" = 1) AND ("flights"."departure_datetime" >= '2011-01-07 17:21:56.932566' AND "flights"."departure_datetime" < '2011-01-08 17:21:56.932568')

4

Robert Walker wrote in post #973147:

Rails 2: @flights = Flight.find(:conditions => { :arrival_airport_id => departure_airport_id, :departure_datetime => @date...(@date + 1.day) })

Rails 3: @flights = Flight.where(:arrival_airport_id => departure_airport_id, :departure_datetime => @date...(@date + 1.day))

SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" = 1) AND ("flights"."departure_datetime" >= '2011-01-07 17:21:56.932566' AND "flights"."departure_datetime" < '2011-01-08 17:21:56.932568')

Oops, The above statements are actually correct but the resulting SQL show here was incorrect. Should have been:

SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" = 1) AND ("flights"."departure_datetime" >= '2011-01-07' AND "flights"."departure_datetime" < '2011-01-08')

5

I think the above would only be correct if the OP is operating in UTC, generally he should make @date the UTC time for the start of the local day as the datetimes in the db will be in utc.

Colin

6

Colin Law wrote in post #973150:

7

Thanks very much for all of the answers. I went with :departure_datetime => @date...(@date + 1.day) as this was exactly what I was after. Thanks also for highlighting the difference between Rails 2 and Rails 3. I look forward to giving Rails 3 a try. Best, Jim