[SQL] Querying date interval

2001-01-15 Thread Renato De Giovanni 

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
[EMAIL PROTECTED]

Re: [SQL] Querying date interval

2001-01-15 Thread Tom Lane 

Renato De Giovanni [EMAIL PROTECTED] 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: [SQL] Querying date interval

2001-01-15 Thread Richard Huxton 

- Original Message -
From: "Renato De Giovanni" [EMAIL PROTECTED]


 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: [SQL] Querying date interval

2001-01-15 Thread Renato De Giovanni 

  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 were 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
[EMAIL PROTECTED]