Postgres Pro
Downloads
Home   >   mailing lists

Thread: Querying date interval

Querying date interval

From
Renato De Giovanni
Date:
Hi,

Is there any SQL workaround to get the right results from the select
statement bellow? Or am I doing something wrong??

create table testdate (field1 date);
insert into testdate values ('2000-09-30');
insert into testdate values ('2000-10-20');
insert into testdate values ('2000-11-25');

select * from testdate where field1 between '2000-10-01' and
'2000-11-30' ;
  field1
------------2000-09-30   <---- why is it here??2000-10-202000-11-25
(3 rows)

Thanks in advance!
--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br

Re: Querying date interval

From
"J.H.M. Dassen (Ray)"
Date:
On Mon, Jan 15, 2001 at 12:19:56 -0200, Renato De Giovanni wrote:
> select * from testdate where field1 between '2000-10-01' and
> '2000-11-30' ;
> 
>    field1
> ------------
>  2000-09-30   <---- why is it here??
>  2000-10-20
>  2000-11-25

It works fine for me (7.0.3, Debian GNU/Linux "unstable").

I suspect your problem has to do with the "DATESTYLE" setting you're using;
does it help if you do "SET DATESTYLE TO 'ISO'" prior to INSERTing your test
values?

HTH,
Ray
-- 
[Open Source] is the finest expression of the free market. Ideas are
encouraged to proliferate and the best thinking wins. By contrast, most
corporations today operate in a central planning straitjacket.
http://www.thestandard.com/article/display/0,1151,15772,00.html

Re: Querying date interval

From
Tom Lane
Date:
Renato De Giovanni <rdg@viafractal.com.br> writes:
> create table testdate (field1 date);
> insert into testdate values ('2000-09-30');
> insert into testdate values ('2000-10-20');
> insert into testdate values ('2000-11-25');
> select * from testdate where field1 between '2000-10-01' and
> '2000-11-30' ;

>    field1
> ------------
>  2000-09-30   <---- why is it here??
>  2000-10-20
>  2000-11-25
> (3 rows)

Curious.  I can't reproduce this error in EST5EDT timezone, using
either 7.0.2 or current.

What PG version are you using, and with what timezone setting?
Is 2000-09-30 or 2000-10-01 a daylight-savings transition date in your
timezone?
        regards, tom lane

Re: Querying date interval

From
"Richard Huxton"
Date:
----- Original Message -----
From: "Renato De Giovanni" <rdg@viafractal.com.br>


> Hi,
>
> Is there any SQL workaround to get the right results from the select
> statement bellow? Or am I doing something wrong??
>
> select * from testdate where field1 between '2000-10-01' and
> '2000-11-30' ;
>
>    field1
> ------------
>  2000-09-30   <---- why is it here??
>  2000-10-20
>  2000-11-25
> (3 rows)
>
Someone else mentioned DATESTYLE - the other thing to check is that you
haven't got a local summertime adjustment on 30th Sep or 1st Oct - that
caused issues in some previous versions of postgres IIRC (have a rummage in
the archives)

- Richard Huxton

Re: Querying date interval

From
Renato De Giovanni
Date:
> > create table testdate (field1 date);
> > insert into testdate values ('2000-09-30');
> > insert into testdate values ('2000-10-20');
> > insert into testdate values ('2000-11-25');
> > select * from testdate where field1 between '2000-10-01' and
> > '2000-11-30' ;
>
> >    field1
> > ------------
> >  2000-09-30   <---- why is it here??
> >  2000-10-20
> >  2000-11-25
> > (3 rows)
>
> Curious.  I can't reproduce this error in EST5EDT timezone, using
> either 7.0.2 or current.
>
> What PG version are you using, and with what timezone setting?
> Is 2000-09-30 or 2000-10-01 a daylight-savings transition date in your
> timezone?

I'm using 7.0.2 version on debian 2.2. Standard timezone here is GMT-3,
but we´re currently in DST since 8 Oct 2000.
Never changed datestyle setting - I'm only using ISO8601 notation to
manipulate values.

And now I've noticed something even weird - leaving psql interface and
entering it again, that query show me the right result! But if I drop the
table, create the same structure with the same data then I get wrong
result again!?

Well, anyway, I'll try upgrading to 7.0.3...
--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br

Re: Querying date interval

From
Renato De Giovanni
Date:
> > select * from testdate where field1 between '2000-10-01' and
> > '2000-11-30' ;
> >
> >    field1
> > ------------
> >  2000-09-30   <---- why is it here??
> >  2000-10-20
> >  2000-11-25
>
> It works fine for me (7.0.3, Debian GNU/Linux "unstable").
>
> I suspect your problem has to do with the "DATESTYLE" setting you're using;
> does it help if you do "SET DATESTYLE TO 'ISO'" prior to INSERTing your test
> values?

Just reporting, I´ve tried: \set DATESTYLE ´ISO´ before adding values, but the
result is still wrong.

Thanks anyway,
--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br