Обсуждение: Re: Birthday comparisons

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

On Thu, Mar 22, 2001 at 07:45:41PM +0000, Andy Corteen wrote:
> 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.

how about
    birthday_between('1998-12-25','1998-12-1','1999-1-1')
? doesn't that pose a year-relevant problem?

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!