From: Bosco Rama <postgres@boscorama.com>
To: David G. Johnston <david.g.johnston@gmail.com>
To: er.tejaspatel88@gmail.com <er.tejaspatel88@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Fastest way / best practice to calculate "next birthdays"
Date: Thu, 21 May 2015 09:15:04 -0700
Message-ID: <555E0488.1030908@boscorama.com> (raw)
In-Reply-To: <CAKFQuwZLcWw6=T6GU9rF+6DCN6+DSOgZJg1JAgOKLWG6KEUiWA@mail.gmail.com>
References: <fvka82$1b10$1@news.hub.org>
<481DE036.90706@commandprompt.com>
<481DF76D.3060807@theendofthetunnel.de>
<481E9E12.8070208@Sheeky.Biz>
<1431941415888-5849705.post@n5.nabble.com>
<CAKFQuwZLcWw6=T6GU9rF+6DCN6+DSOgZJg1JAgOKLWG6KEUiWA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 05/20/15 20:22, David G. Johnston wrote:
> On Monday, May 18, 2015, er.tejaspatel88@gmail.com <
> er.tejaspatel88@gmail.com> wrote:
>
>> If I have to find upcoming birthdays in current week and the current week
>> fall into different months - how would you handle that?
>>
>
> Extract(week from timestamptz_column)
>
> ISO weeks are not affected by month boundaries but do start on Monday.
There is the year start/end boundary conditions to worry about there.
If the current week covers Dec28-Jan02 then week of year won't help for
a birthday on Jan01 or Jan02 if 'today' is in the Dec portion. Ditto
for birthday in Dec portion when 'today' is in the Jan portion.
There is probably a better way to do it than what I'm showing here, but
here's an example:
with x as (
select now() - (extract(dow from now()) || ' days')::interval as
weekstart
)
select to_char(x.weekstart, 'YYYY-MM-DD') as first_day,
to_char(x.weekstart + '6 days', 'YYYY-MM-DD') as last_day
from x;
You could probably make some of that into a function that accepts a
timestamptz and generates the two days. Or even does the compare too.
HTH.
Bosco.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgres@boscorama.com, david.g.johnston@gmail.com, er.tejaspatel88@gmail.com
Subject: Re: Fastest way / best practice to calculate "next birthdays"
In-Reply-To: <555E0488.1030908@boscorama.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox