Message-ID: <481DE036.90706@commandprompt.com>
Date: Sun, 04 May 2008 09:11:34 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: Hannes Dorbath <light@theendofthetunnel.de>
CC: pgsql-performance@postgresql.org
Subject: Re: Fastest way / best practice to calculate "next birthdays"
References: <fvka82$1b10$1@news.hub.org>
In-Reply-To: <fvka82$1b10$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hannes Dorbath wrote:
> The subject basically says it all, I'm looking for the fastest 
> (indexable) way to calculate the next birthdays relative to NOW() from a 
> dataset of about 1 million users.
> 
> I'm currently using a function based index, but leap year handling / 
> mapping February 29 to February 28 gives me some headaches.
> 
> Is there any best practice to do that in PostgreSQL?

postgres=# SELECT current_date|| ' a ' || to_char(current_date, 'Day'), 

current_date + '1 Year'::interval || ' a '  || to_char(current_date + '1 
Year'::interval, 'Day') as next_birthday;
         ?column?        |          next_birthday
------------------------+---------------------------------
  2008-05-04 a Sunday    | 2009-05-04 00:00:00 a Monday

?


Sincerely,

Joshua D. Drake