Query for a specific day and month of a date

Rails
1

Hi all!

I have a table with the birthdays of users and I want my app to list
all users who birthdays today.

If today is 21/09/2009, I need all the rows which have the field
b_date 21/09.

How can I get that condition in ActiveRecord?
Should I try something like this:

@birthdays = Birthday.find(:all, :conditions => {:b_date.day =>
Date.today.day, :b_date.month => Date.today.month})

I’m a newbie so excuse me if this question is to much easy and excuse
my poor english too…

Thanks in advance.

2

It depends how it’ stored in the Database.
If it is stored in a string of format ‘MM/DD’, then I’d probably do
something like this:

def find_birthdays
mmdd = Date.today.to_s.gsub(’-’, ‘/’)[5…9]
@birthdays = Birthday.find :all, :conditions => [‘b_date like ?’,
mmdd]
end

Just make sure the month and day are always two digits.
And there might be a smarter way to do it than using gsub, but I don’t
know it off-hand.

3

On 21 Sep 2009, at 17:05, juanmac wrote:

Should I try something like this:

@birthdays = Birthday.find(:all, :conditions => {:b_date.day =>
Date.today.day, :b_date.month => Date.today.month})

I’m a newbie so excuse me if this question is to much easy and excuse
my poor english too…

You can’t do it quite like that. You need to turn what you want into
something of the form

start_time <= b_date and b_date < end_time

(so start_date is probably the first second of today and end_time is
the first second of tomorrow)

You can’t use the hash form of conditions for this.

Fred

4

Thanks!

I did it that way Aldric. It seems to be the better way. I will have a
lots of rows in the database, so I wanted to boost the performance.
That’s why I want only to retrieve the specific day.

Thanks again! :smiley:

On 21 sep, 21:29, Aldric G. [email protected]

5

juanmac wrote:

Thanks!

I did it that way Aldric. It seems to be the better way. I will have a
lots of rows in the database, so I wanted to boost the performance.
That’s why I want only to retrieve the specific day.

It’s not a good idea to store dates in the DB as strings. You should
store dates as dates in the DB. SQL has date handling functions that
you can use, so what you might want is something like :conditions =>
[‘day(b_date) = :day and month(b_date) = :month’, {:day =>
Date.today.day, :month => Date.today.month}] .

Thanks again! :smiley:

On 21 sep, 21:29, Aldric G. [email protected]

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

6

It’s not a good idea to store dates in the DB as strings. You should
store dates as dates in the DB. SQL has date handling functions that
you can use,

I agree with Marnen in this point. If you use a table “birthdays” for
storing the birthdays for users having fields
user_id => :integer
date => :date

then i think its better to use named_scope. As i said, if your model
name is Birthday then define a named_scope as
named_scope :of_day, lambda { |date| {:conditions => { :day =>
date } } }

And call it like
Birthday.of_day(Date.today)

That will generate a single query like following
SELECT * FROM birthdays WHERE (birthdays .date =
‘2009-09-22’)

This will give you a reusable and readable way you can apply for any
other days if you want.
Hope this will help :smiley:

Thank you.

samiron

7

Marnen Laibow-Koser wrote:

It’s not a good idea to store dates in the DB as strings. You should
store dates as dates in the DB. SQL has date handling functions that
you can use, so what you might want is something like :conditions =>
[‘day(b_date) = :day and month(b_date) = :month’, {:day =>
Date.today.day, :month => Date.today.month}] .

Marnen is right, mind you. If you store a year with it, you have access
to all the Rails date manipulation methods, as well as those of the
database. It’s better practice, and more convenient.