Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2AF206332CF for ; Wed, 12 Aug 2009 21:59:07 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36916-10 for ; Thu, 13 Aug 2009 00:59:03 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f180.google.com (mail-yx0-f180.google.com [209.85.210.180]) by mail.postgresql.org (Postfix) with ESMTP id 8D9A6632D8E for ; Wed, 12 Aug 2009 21:59:03 -0300 (ADT) Received: by yxe10 with SMTP id 10so570495yxe.15 for ; Wed, 12 Aug 2009 17:59:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:content-type; bh=0+5sKguhR3GI2OJoPLP+ZXiaBzu16HNoWaJVBDGOB90=; b=UXKli79iKAeuZbiT3JbyJpPyAVDe/KDWaKQS0EFoxxzTJG7uOTcOSf0yukG1HKb0cq SHoIhLuFJGSSFSsIUQa7IIcU12BgGo7Qgs49TqZlhjqxSX2tAmGiowx+KBr/qZYj655y PzUk1iw47xk0mPyKB5MhROSfkjvpvQzpy6OaU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type; b=SHTMdedZBs4wqN0qypmcOeONnX0IOxBYxEg3gMBioUADxmnzjOwIuFgKebmSvifawn WZzoELXad0vhfWrLMkmlyW0KGTzdmVuwzUIEj2VClVfkK9L4qeXS3P91KdQCofXIBpPp QranLXPojqxL7Gamhtq6YBsjI6tVwXZazgx8Q= MIME-Version: 1.0 Received: by 10.100.11.14 with SMTP id 14mr562580ank.81.1250125142114; Wed, 12 Aug 2009 17:59:02 -0700 (PDT) In-Reply-To: <207AA50147184F3885AB00BE4BA6CE0A@ramasubramanian> References: <24917400.post@talk.nabble.com> <207AA50147184F3885AB00BE4BA6CE0A@ramasubramanian> From: Jorge Godoy Date: Wed, 12 Aug 2009 21:58:42 -0300 Message-ID: <175c742d0908121758ud1db0b8uf79d42ee03a8e7d9@mail.gmail.com> Subject: Re: Month/year between two dates To: ramasubramanian , Bor , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e642dd640541520470fb7121 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/61 X-Sequence-Number: 32970 --0016e642dd640541520470fb7121 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit I think he wanted something like: test=# create table month_test(id serial primary key, start_date date not null, end_date date not null); CREATE TABLE test=# insert into month_test (start_date, end_date) values ('2009-01-01'::date, '2009-08-31'::date); INSERT 0 1 test=# insert into month_test (start_date, end_date) values ('2009-08-01'::date, '2009-08-10'::date); INSERT 0 1 test=# insert into month_test (start_date, end_date) values ('2009-08-11'::date, '2009-08-31'::date); INSERT 0 1 test=# select * from month_test; id | start_date | end_date ----+------------+------------ 1 | 2009-01-01 | 2009-08-31 2 | 2009-08-01 | 2009-08-10 3 | 2009-08-11 | 2009-08-31 (3 rows) test=# select * from month_test where (start_date, end_date) overlaps ('2009-05-01'::date, '2009-05-31'::date); id | start_date | end_date ----+------------+------------ 1 | 2009-01-01 | 2009-08-31 (1 row) test=# -- Jorge Godoy On Wed, Aug 12, 2009 at 00:59, ramasubramanian < ramasubramanian.g@renaissance-it.com> wrote: > Dear Bor, > How you will 1 record for 2009/05 (if you use 2009/05 ) it will fetch all > the records as it is not having month 05 > am i correct? > > ----- Original Message ----- From: "Bor" > To: > Sent: Tuesday, August 11, 2009 6:43 PM > Subject: [SQL] Month/year between two dates > > > >> Hi to all, >> >> I have a very simple question. Let's say that I have three records (id, >> date >> from, date to): >> >> 1 2009-01-01 2009-08-31 >> 2 2009-08-01 2009-08-10 >> 3 2009-08-11 2009-08-31 >> >> Now I want to get records, "related" to a single month/year data (two >> integers). For 2009/08 (int1 = 2009, int2 = 8) I should get all three >> records, for 2009/05 only record 1, but for 2009/11 none of the records. >> >> Is there any simple way to do this? A query would do :). >> >> Thanks alot. >> -- >> View this message in context: >> http://www.nabble.com/Month-year-between-two-dates-tp24917400p24917400.html >> Sent from the PostgreSQL - sql mailing list archive at Nabble.com. >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > --0016e642dd640541520470fb7121 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I think he wanted something like:

test=3D# create table month_test(i= d serial primary key, start_date date not null, end_date date not null);CREATE TABLE
test=3D# insert into month_test (start_date, end_date) val= ues ('2009-01-01'::date, '2009-08-31'::date);
INSERT 0 1
test=3D# insert into month_test (start_date, end_date) values= ('2009-08-01'::date, '2009-08-10'::date);
INSERT 0 1test=3D# insert into month_test (start_date, end_date) values ('2009-0= 8-11'::date, '2009-08-31'::date);
INSERT 0 1
test=3D# select * from month_test;
=A0id | start_date |=A0= end_date=A0
----+------------+------------
=A0 1 | 2009-01-01 | 200= 9-08-31
=A0 2 | 2009-08-01 | 2009-08-10
=A0 3 | 2009-08-11 | 2009-08-= 31
(3 rows)

test=3D# select * from month_test where (start_date, end_date) overlaps= ('2009-05-01'::date, '2009-05-31'::date);
=A0id | start= _date |=A0 end_date=A0
----+------------+------------
=A0 1 | 2009-0= 1-01 | 2009-08-31
(1 row)

test=3D#

--
Jorge Godoy =A0 =A0 <= ;jgodoy@gmail.com= >


On Wed, Aug 12, 2009 at 00:59, ramasubra= manian <ramasubramanian.g@renaissance-it.com> wrote:
Dear Bor,
=A0 How you will 1 record for 2009/05 (if you use 2009/05 ) it will fetch = all the records as it is not having month 05
am i correct?

----- Original Message ----- From: "Bor" <dborovnik@gmail.com>
To: <pgsql= -sql@postgresql.org>
Sent: Tuesday, August 11, 2009 6:43 PM
Subject: [SQL] Month/year between two dates



Hi to all,

I have a very simple question. Let's say that I have three records (id,= date
from, date to):

1 =A0 =A0 2009-01-01 =A0 =A0 2009-08-31
2 =A0 =A0 2009-08-01 =A0 =A0 2009-08-10
3 =A0 =A0 2009-08-11 =A0 =A0 2009-08-31

Now I want to get records, "related" to a single month/year data = (two
integers). For 2009/08 (int1 =3D 2009, int2 =3D 8) I should get all three records, for 2009/05 only record 1, but for 2009/11 none of the records.
Is there any simple way to do this? A query would do :).

Thanks alot.
--
View this message in context: http://www.nab= ble.com/Month-year-between-two-dates-tp24917400p24917400.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql

--0016e642dd640541520470fb7121--