Lists: | pgsql-general |
---|
From: | Alex - <aintokyo(at)hotmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Query to return every 1st Sat of a month between two dates |
Date: | 2011-05-11 15:22:11 |
Message-ID: | SNT135-w216811A0AC929E9C7FC035CF860@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
ThanksAlex
From: | Michael Nolan <htfoot(at)gmail(dot)com> |
---|---|
To: | Alex - <aintokyo(at)hotmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query to return every 1st Sat of a month between two dates |
Date: | 2011-05-11 18:10:16 |
Message-ID: | BANLkTi=DS462ZB1awq1dMD0tY=jWWZOQig@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
>
>
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
From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Alex -'" <aintokyo(at)hotmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query to return every 1st Sat of a month between two dates |
Date: | 2011-05-16 16:43:48 |
Message-ID: | 011401cc13e8$6e898b30$4b9ca190$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alex -
Sent: Wednesday, May 11, 2011 11:22 AM
To: pgsql-general(at)postgresql(dot)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
From: | Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com> |
---|---|
To: | Alex - <aintokyo(at)hotmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query to return every 1st Sat of a month between two dates |
Date: | 2011-05-16 18:37:30 |
Message-ID: | BANLkTi=u5P4oubo3o1Bv9rpKTFsA4AUfrg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2011/5/11 Alex - <aintokyo(at)hotmail(dot)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
From: | Jaime Casanova <jaime(at)2ndquadrant(dot)com> |
---|---|
To: | Alex - <aintokyo(at)hotmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query to return every 1st Sat of a month between two dates |
Date: | 2011-05-17 22:00:22 |
Message-ID: | BANLkTimx45VrjXA6KJMU3TCuXFqe_zAO=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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