pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuerying date interval
6+ messages / 4 participants
[nested] [flat]
* Querying date interval
@ 2001-01-15 14:19 Renato De Giovanni <rdg@viafractal.com.br>
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:36 J.H.M. Dassen (Ray) <jdassen@cistron-office.nl>
parent: 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 15:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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 15:29 Richard Huxton <dev@archonet.com>
parent: Renato De Giovanni <rdg@viafractal.com.br>
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
* Re: Querying date interval
@ 2001-01-16 01:19 Renato De Giovanni <rdg@viafractal.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-16 01:30 Renato De Giovanni <rdg@viafractal.com.br>
parent: J.H.M. Dassen (Ray) <jdassen@cistron-office.nl>
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
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