pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Querying date interval
6+ messages / 4 participants
[nested] [flat]

* Querying date interval
@ 2001-01-15 14:19 Renato De Giovanni <rdg@viafractal.com.br>
  2001-01-15 14:36 ` Re: Querying date interval J.H.M. Dassen (Ray) <jdassen@cistron-office.nl>
  2001-01-15 15:14 ` Re: Querying date interval Tom Lane <tgl@sss.pgh.pa.us>
  2001-01-15 15:29 ` Re: Querying date interval Richard Huxton <dev@archonet.com>
  0 siblings, 3 replies; 6+ messages in thread

From: Renato De Giovanni @ 2001-01-15 14:19 UTC (permalink / raw)
  To: pgsql-sql

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-20
 2000-11-25
(3 rows)

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Querying date interval
  2001-01-15 14:19 Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
@ 2001-01-15 14:36 ` J.H.M. Dassen (Ray) <jdassen@cistron-office.nl>
  2001-01-16 01:30   ` Re: Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
  2 siblings, 1 reply; 6+ messages in thread

From: J.H.M. Dassen (Ray) @ 2001-01-15 14:36 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Querying date interval
  2001-01-15 14:19 Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
  2001-01-15 14:36 ` Re: Querying date interval J.H.M. Dassen (Ray) <jdassen@cistron-office.nl>
@ 2001-01-16 01:30   ` Renato De Giovanni <rdg@viafractal.com.br>
  0 siblings, 0 replies; 6+ messages in thread

From: Renato De Giovanni @ 2001-01-16 01:30 UTC (permalink / raw)
  To: pgsql-sql

> > 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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Querying date interval
  2001-01-15 14:19 Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
@ 2001-01-15 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-01-16 01:19   ` Re: Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
  2 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-01-15 15:14 UTC (permalink / raw)
  To: Renato De Giovanni <rdg@viafractal.com.br>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Querying date interval
  2001-01-15 14:19 Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
  2001-01-15 15:14 ` Re: Querying date interval Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-01-16 01:19   ` Renato De Giovanni <rdg@viafractal.com.br>
  0 siblings, 0 replies; 6+ messages in thread

From: Renato De Giovanni @ 2001-01-16 01:19 UTC (permalink / raw)
  To: pgsql-sql

> > 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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Querying date interval
  2001-01-15 14:19 Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
@ 2001-01-15 15:29 ` Richard Huxton <dev@archonet.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Richard Huxton @ 2001-01-15 15:29 UTC (permalink / raw)
  To: Renato De Giovanni <rdg@viafractal.com.br>; pgsql-sql

----- 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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-01-16 01:30 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-15 14:19 Querying date interval Renato De Giovanni <rdg@viafractal.com.br>
2001-01-15 14:36 ` J.H.M. Dassen (Ray) <jdassen@cistron-office.nl>
2001-01-16 01:30   ` Renato De Giovanni <rdg@viafractal.com.br>
2001-01-15 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-16 01:19   ` Renato De Giovanni <rdg@viafractal.com.br>
2001-01-15 15:29 ` Richard Huxton <dev@archonet.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox