From: | Bosco Rama <postgres(at)boscorama(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "er(dot)tejaspatel88(at)gmail(dot)com" <er(dot)tejaspatel88(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Fastest way / best practice to calculate "next birthdays" |
Date: | 2015-05-21 16:15:04 |
Message-ID: | 555E0488.1030908@boscorama.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 05/20/15 20:22, David G. Johnston wrote:
> On Monday, May 18, 2015, er(dot)tejaspatel88(at)gmail(dot)com <
> er(dot)tejaspatel88(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-05-21 16:50:04 | Re: Fastest way / best practice to calculate "next birthdays" |
Previous Message | Tom Lane | 2015-05-21 15:28:16 | Re: union all and filter / index scan -> seq scan |