Postgres Pro
Downloads
Home   >   mailing lists

Thread: Extract between year *and* month

Extract between year *and* month

From
"One"
Date:
Hi - I'd like to return search results based on a date range that is
selected by the user :

<select name="date1yy">
       <option value="1995-01-01" selected>1995</option>

<select name="date2yy">
       <option value="2006-12-31" selected>2006</option>

My SQL is :

select *
 from tablename
    where title like ('%nith%')
       and recall_date between
           extract(year from date '1995-01-01')
              and  extract(year from date '2006-12-31')
                  order by recall_date

How can I add the MONTH to be included in the between statement ?
If I select 1995 - 2006 it currently only returns rows up to 2005-12-31

THANKS!

Re: Extract between year *and* month

From
"A. Kretschmer"
Date:
am  Thu, dem 16.11.2006, um 13:03:20 -0800 mailte One folgendes:
> Hi - I'd like to return search results based on a date range that is
> selected by the user :
>
> <select name="date1yy">
>        <option value="1995-01-01" selected>1995</option>
>
> <select name="date2yy">
>        <option value="2006-12-31" selected>2006</option>
>
> My SQL is :
>
> select *
>  from tablename
>     where title like ('%nith%')
>        and recall_date between
>            extract(year from date '1995-01-01')
>               and  extract(year from date '2006-12-31')
>                   order by recall_date
>
> How can I add the MONTH to be included in the between statement ?
> If I select 1995 - 2006 it currently only returns rows up to 2005-12-31

   and to_char(recall_date, 'yyyy-mm') between
      to_char('1995-01-01'::date, 'yyyy-mm')
         and to_char('2006-12-31'::date, 'yyyy-mm')
   order by recall_date;


**untested**


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Extract between year *and* month

From
Russell Smith
Date:
One wrote:
> Hi - I'd like to return search results based on a date range that is
> selected by the user :
>
> <select name="date1yy">
>        <option value="1995-01-01" selected>1995</option>
>
> <select name="date2yy">
>        <option value="2006-12-31" selected>2006</option>
>
> My SQL is :
>
> select *
>  from tablename
>     where title like ('%nith%')
>        and recall_date between
>            extract(year from date '1995-01-01')
>               and  extract(year from date '2006-12-31')
>                   order by recall_date
>
> How can I add the MONTH to be included in the between statement ?
> If I select 1995 - 2006 it currently only returns rows up to 2005-12-31
>
> THANKS!
>
Try

select *
 from tablename
    where title like ('%nith%')
       and date_trunc('month',recall_date::timestamp) >= date_trunc('month','1995-01-01'::timestamp)
       and date_trunc('month',recall_date::timestamp) <= date_trunc('month','2006-12-31'::timestamp)
                  order by recall_date

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>

Re: Extract between year *and* month

From
Matthias.Pitzl@izb.de
Date:
Hi!

You could use date_trunc instead of extract to truncate the date down to the
month:

select date_trunc('month','2006-12-31'::timestamp);
     date_trunc
---------------------
 2006-12-01 00:00:00

Greetings,
Matthias

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of One
> Sent: Thursday, November 16, 2006 10:03 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Extract between year *and* month
>
>
> Hi - I'd like to return search results based on a date range that is
> selected by the user :
>
> <select name="date1yy">
>        <option value="1995-01-01" selected>1995</option>
>
> <select name="date2yy">
>        <option value="2006-12-31" selected>2006</option>
>
> My SQL is :
>
> select *
>  from tablename
>     where title like ('%nith%')
>        and recall_date between
>            extract(year from date '1995-01-01')
>               and  extract(year from date '2006-12-31')
>                   order by recall_date
>
> How can I add the MONTH to be included in the between statement ?
> If I select 1995 - 2006 it currently only returns rows up to
> 2005-12-31
>
> THANKS!
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Re: Extract between year *and* month

From
"Syl"
Date:
Russell Smith wrote:
> One wrote:
> > Hi - I'd like to return search results based on a date range that is
> > selected by the user :
> >
> > <select name="date1yy">
> >        <option value="1995-01-01" selected>1995</option>
> >
> > <select name="date2yy">
> >        <option value="2006-12-31" selected>2006</option>
> >
> > My SQL is :
> >
> > select *
> >  from tablename
> >     where title like ('%nith%')
> >        and recall_date between
> >            extract(year from date '1995-01-01')
> >               and  extract(year from date '2006-12-31')
> >                   order by recall_date
> >
> > How can I add the MONTH to be included in the between statement ?
> > If I select 1995 - 2006 it currently only returns rows up to 2005-12-31
> >
> > THANKS!
> >
> Try
>
> select *
>  from tablename
>     where title like ('%nith%')
>        and date_trunc('month',recall_date::timestamp) >= date_trunc('month','1995-01-01'::timestamp)
>        and date_trunc('month',recall_date::timestamp) <= date_trunc('month','2006-12-31'::timestamp)
>                   order by recall_date
>

Fantastic.

Thank you to Matthias and Russell - everything is working perfectly!

Re: Extract between year *and* month

From
Alban Hertroys
Date:
Syl wrote:
>> Try
>>
>> select *
>>  from tablename
>>     where title like ('%nith%')
>>        and date_trunc('month',recall_date::timestamp) >= date_trunc('month','1995-01-01'::timestamp)
>>        and date_trunc('month',recall_date::timestamp) <= date_trunc('month','2006-12-31'::timestamp)
>>                   order by recall_date
>>

Actually, that looks a lot like BETWEEN, which is even shorter :)

 select *
   from tablename
  where title like ('%nith%')
    and date_trunc('month',recall_date::timestamp)
    between date_trunc('month','1995-01-01'::timestamp)
        and date_trunc('month','2006-12-31'::timestamp)
  order by recall_date


> Fantastic.
>
> Thank you to Matthias and Russell - everything is working perfectly!

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Re: Extract between year *and* month

From
"Syl"
Date:
Alban Hertroys wrote:
> Syl wrote:
> >> Try
> >>
> >> select *
> >>  from tablename
> >>     where title like ('%nith%')
> >>        and date_trunc('month',recall_date::timestamp) >= date_trunc('month','1995-01-01'::timestamp)
> >>        and date_trunc('month',recall_date::timestamp) <= date_trunc('month','2006-12-31'::timestamp)
> >>                   order by recall_date
> >>
>
> Actually, that looks a lot like BETWEEN, which is even shorter :)
>
>  select *
>    from tablename
>   where title like ('%nith%')
>     and date_trunc('month',recall_date::timestamp)
>     between date_trunc('month','1995-01-01'::timestamp)
>         and date_trunc('month','2006-12-31'::timestamp)
>   order by recall_date
>
>
> > Fantastic.
> >
> > Thank you to Matthias and Russell - everything is working perfectly!
>


Thank you for the accurate and detailed repsonses! All is working
excellent.