Re: Fastest way / best practice to calculate "next birthdays"

Hannes Dorbath wrote:

> Sorry, I think I phrased the question badly. What I'm after basically is:
>
> http://www.depesz.com/index.php/2007/10/26/who-has-birthday-tomorrow/
>

OK So what I came up with is - (the times are from a G4 1.25Ghz)

CREATE TABLE birthdaytest
(
   id serial PRIMARY KEY,
   birthdate date
);


CREATE INDEX idx_bday_month ON birthdaytest
USING btree(extract(month from birthdate));

CREATE INDEX idx_bday_day ON birthdaytest
USING btree(extract(day from birthdate));


insert into birthdaytest (birthdate) values
('1930-01-01'::date+generate_series(0,365*70));

... I repeated this another 15 times to load some data


vacuum analyse birthdaytest;

\timing

select count(*) from birthdaytest;

>  count
> --------
>  408816
> (1 row)
>
> Time: 233.501 ms


select * from birthdaytest
where extract(month from birthdate) = 5
and extract(day from birthdate) between 6 and 12;

>    id   | birthdate
> --------+------------
>     126 | 1930-05-06
>     127 | 1930-05-07
>     128 | 1930-05-08
> ...
> ...
>  408613 | 1999-05-11
>  408614 | 1999-05-12
> (7840 rows)
>
> Time: 211.237 ms


select * from birthdaytest
where extract(month from birthdate) = extract(month from current_date)
and extract(day from birthdate) between extract(day from current_date)
and extract(day from current_date+14);

>    id   | birthdate
> --------+------------
>     125 | 1930-05-05
>     126 | 1930-05-06
>     127 | 1930-05-07
> ...
> ...
>  408619 | 1999-05-17
>  408620 | 1999-05-18
>  408621 | 1999-05-19
> (16800 rows)
>
> Time: 483.915 ms


--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz