I foolishly trashed the original postings about calculating if
birthdays fall between two dates, ignoring the year parts. However...
It struck me that the easiest way to make the comparison might be to
normalize the date to be tested such that the year does not matter,
then simply use the BETWEEN operator to make the test.
Coding style side, consider the following:
DROP FUNCTION "birthday_between" (date,date,date);
CREATE FUNCTION "birthday_between" (date,date,date)
RETURNS boolean AS
'
select true
where $1-( (EXTRACT(YEAR from $1)-1)||'' year'' )::interval
between $2 and $3;
'
LANGUAGE 'SQL';
Pull this into PostgreSQL with
psql -e db_name < my_function_in_a_text_file
Invoked something like this...
select t.birthday from test_table t where
birthday_between(t.birthday,'0001/03/21','0001/03/22');
On my development server (Linux RH6.2, Dell Poweredge, UW SCSI, 128MB
ram) this query returns only the matching records from 4,000 entries
in about 0.15 seconds.
Something tells me that the 'epoch' option to date_part() might yield
a more 'standard' query, but I did not have time to look into that :)
--
Best regards,
Andy mailto:lbc@telecam.demon.co.uk
