Correct way to search datetime column by date

Rails
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

On Jan 7, 2011, at 8:48 AM, Jim B. wrote:

@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?

You could replace “departure_datetime >= ? and departure_datetime < ?”
with “departure_datetime BETWEEN ? AND ?”…

Doesn’t help much though…

3

Jim B. 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 W. 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

On 7 January 2011 17:30, Robert W. [email protected] wrote:

= 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’)

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

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

7

Colin L. wrote in post #973150:

On 7 January 2011 17:30, Robert W. [email protected] wrote:

= 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’)

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.

Good catch, thanks Colin.