Re: Query to return every 1st Sat of a month between two dates

On Wed, May 11, 2011 at 10:22 AM, Alex - <aintokyo(at)hotmail(dot)com> wrote:
> Hi,
> is there an easy way to return the date of every first Saturday of a month
> in a data range i.e. 2011-2013

if you want a list of the first saturdays of every month and you're
using at least 8.4:

with q as (select d, lag(d) over ()
from generate_series('2011-02-01'::date, now()::date, '1
day') as s(d)
where extract(dow from d) = 6
)
select d from q where (lag is null) or (extract(month from d) <>
extract(month from lag));

where '2011-02-01' is the initial date and now()::date - '1 day' the
final one, replace them with you're own range

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL