(basic sql) search datetime column by date

rubyonrails-talk
1

I have a datetime column and I need to search by a date, not datetime.

So obviously, I cannot do this:

find(:all, :conditions => ['my_datetime_column = ?', Date.today])

All I want to do is find the records on a datetime column that match today's date.

What I have had to do is convert each record in the datetime column to a date and compare (== Date.today), which is slower than just using a conditions option. What do you guys suggest?

Thanks for any help!

2

I have a datetime column and I need to search by a date, not datetime.

So obviously, I cannot do this:

find(:all, :conditions => ['my_datetime_column = ?', Date.today])

All I want to do is find the records on a datetime column that match today's date.

You want to say ['my_datetime_column >= ? and my_datetime_column < ?',
Date.today(), Date.today()+1] This works because the dbs I know of handle '2007-04-20' as being
'2007-04-20 00:00:00'. If you db does something different you'll have
to adjust for that.

Fred

3

Works perfectly! Thank you!

4

You can also use CURDATE() if you are using MySQL

Model.find(:all, :conditions =>['created_on <= CURDATE()'], :limit => 10)

Model.find(:all, :conditions => ['my_datetime_column = CURDATE()'])

5

Ah, it is not working as expected. I have a record with the datetime of 7pm last night (central time). I'm using tztime so when I do this:

Model.find(:all, :conditions => ['datetime_column >= ? and datetime_column < ?', TzTime.now.to_date, TzTime.now.to_date + 1])

It is showing up in the records for today when it should be yesterday. Is there maybe a way I can just search the date in the datetime column?

6

Woops! Nevermind. It is a timezone problem not a query problem.