Обсуждение: Query to return every 1st Sat of a month between two dates

Поиск
Список
Период
Сортировка

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

От
Alex -
Дата:
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

Any help would be appreciated

Thanks
Alex 

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

От
Michael Nolan
Дата:


On Wed, May 11, 2011 at 10:22 AM, Alex - <aintokyo@hotmail.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


This is one way to do it:, there are others:

select '2011-01-01'::date + s.a as dates from generate_series(0,1095)
as s(a)
where to_char('2011-01-01'::date+s.a,'dd') between '01' and '07'
and to_char('2011-01-01'::date+s.a,'dy') = 'sat'
 
--
Mike Nolan

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

От
"David Johnston"
Дата:

Start with January 1st, 2011

Generate Series 0 ~ 200

Add (Series# * 7) Days to January 1st := CheckDate

Filter where day(CheckDate) <= 7

Filter where CheckDate <= December 31st 2013

 

David J.

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex -
Sent: Wednesday, May 11, 2011 11:22 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Query to return every 1st Sat of a month between two dates

 

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

 

Any help would be appreciated

 

Thanks

Alex 

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

От
Osvaldo Kussama
Дата:
2011/5/11 Alex - <aintokyo@hotmail.com>:
> 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
> Any help would be appreciated
> Thanks
> Alex


Try:
SELECT s.a::date+(6-(extract(dow from s.a)::int%7)) FROM
generate_series(to_date('2011','YYYY'),to_date('2012','YYYY'), '1
month') AS s(a);

Osvaldo

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

От
Jaime Casanova
Дата:
On Wed, May 11, 2011 at 10:22 AM, Alex - <aintokyo@hotmail.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