Postgres Pro
Downloads
Home   >   mailing lists

Thread: Month/year between two dates

Month/year between two dates

From
Bor
Date:
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.

Re: Month/year between two dates

From
Steve Crawford
Date:
Bor wrote:
> 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.
>   
Lots of ways. The following springs to mind but I'm sure there are 
simpler ways (I'm assuming the date_from and date_to are data-type date 
and you are stuck with using int for year and month).

Use date_trunc to convert any date in a month to the first of the month 
and the following should work (untested):
...
date_trunc('month', date_from) <= (int1::text || '-' || int2::text || 
'-1')::date and
date_trunc('month', date_to) >= (int1::text || '-' || int2::text || 
'-1')::date
...

Cheers,
Steve

Re: Month/year between two dates

From
"ramasubramanian"
Date:
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" <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     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

Re: Month/year between two dates

From
Jorge Godoy
Date:
I think he wanted something like:<br /><br />test=# create table month_test(id serial primary key, start_date date not
null,end_date date not null);<br />CREATE TABLE<br />test=# insert into month_test (start_date, end_date) values
('2009-01-01'::date,'2009-08-31'::date);<br /> INSERT 0 1<br />test=# insert into month_test (start_date, end_date)
values('2009-08-01'::date, '2009-08-10'::date);<br />INSERT 0 1<br />test=# insert into month_test (start_date,
end_date)values ('2009-08-11'::date, '2009-08-31'::date);<br /> INSERT 0 1<br />test=# select * from month_test;<br
/> id| start_date |  end_date  <br />----+------------+------------<br />  1 | 2009-01-01 | 2009-08-31<br />  2 |
2009-08-01| 2009-08-10<br />  3 | 2009-08-11 | 2009-08-31<br />(3 rows)<br /><br />test=# select * from month_test
where(start_date, end_date) overlaps ('2009-05-01'::date, '2009-05-31'::date);<br /> id | start_date |  end_date  <br
/>----+------------+------------<br/>  1 | 2009-01-01 | 2009-08-31<br /> (1 row)<br /><br />test=# <br clear="all"
/><br/>--<br />Jorge Godoy     <<a href="mailto:jgodoy@gmail.com" target="_blank">jgodoy@gmail.com</a>><br /><br
/><br/><div class="gmail_quote">On Wed, Aug 12, 2009 at 00:59, ramasubramanian <span dir="ltr"><<a
href="mailto:ramasubramanian.g@renaissance-it.com"target="_blank">ramasubramanian.g@renaissance-it.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"> Dear Bor,<br />   How you will 1 record for 2009/05 (if you use 2009/05 ) it will fetch all the
recordsas it is not having month 05<br /> am i correct?<br /><br /> ----- Original Message ----- From: "Bor" <<a
href="mailto:dborovnik@gmail.com"target="_blank">dborovnik@gmail.com</a>><br /> To: <<a
href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>><br /> Sent: Tuesday, August 11,
20096:43 PM<br /> Subject: [SQL] Month/year between two dates<br /><br /><br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><br /> Hi to all,<br
/><br/> I have a very simple question. Let's say that I have three records (id, date<br /> from, date to):<br /><br />
1    2009-01-01     2009-08-31<br /> 2     2009-08-01     2009-08-10<br /> 3     2009-08-11     2009-08-31<br /><br />
NowI want to get records, "related" to a single month/year data (two<br /> integers). For 2009/08 (int1 = 2009, int2 =
8)I should get all three<br /> records, for 2009/05 only record 1, but for 2009/11 none of the records.<br /><br /> Is
thereany simple way to do this? A query would do :).<br /><br /> Thanks alot.<br /> -- <br /> View this message in
context:<a href="http://www.nabble.com/Month-year-between-two-dates-tp24917400p24917400.html"
target="_blank">http://www.nabble.com/Month-year-between-two-dates-tp24917400p24917400.html</a><br/> Sent from the
PostgreSQL- sql mailing list archive at Nabble.com.<br /><br /><br /> -- <br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></blockquote><br /><br /> -- <br /> Sent via
pgsql-sqlmailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br /> To
makechanges to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></blockquote></div><br />