Re: nasty problem with redhat 6.2 + pg 7.02 - Mailing list pgsql-general

From leonbloy@sinectis.com.ar
Subject Re: nasty problem with redhat 6.2 + pg 7.02
Date
Msg-id 200009191726.OAA12391@dolores.sinectis.com.ar
Whole thread Raw
In response to nasty problem with redhat 6.2 + pg 7.02  (leonbloy@sinectis.com.ar)
List pgsql-general
>  On Tue, Sep 19, 2000 at 12:36:22PM -0400, Lamar Owen wrote:
>  >
>  > I can't duplicate:
>  > test=# SET datestyle=postgres;
>  > SET VARIABLE
>  > test=# create table serv2 (fecha1 date, fecha2 date);
>  > CREATE
>  > test=# insert into serv2 values ('13-01-2000','01-10-2000');
>  > INSERT 407754 1
>  > test=# select * from serv2;
>  >    fecha1   |   fecha2
>  > ------------+------------
>  >  01-13-2000 | 01-10-2000
>  > (1 row)
>  >
>  > test=#
>  > System is RedHat 6.2 with the PostgreSQL 7.0.2-2 RPMset loaded. Hmmm,

Right. I tried on a machine with the RPM set, and all went OK.
My problem appears with a compiled 7.02 distro (no special switches,
no locales)

>
>  This looks suspiciously like a timezone/daylight savings time issue, to
>  me. Somewhere, the date is getting represented as a full date/time, then
>  an hour is getting subtracted, then truncated to a date again.
>
>  INSERT INTO serv2 VALUES ('13-01-2000'::date,'01-10-2000'::date);
>
>  That _might_ force the correct conversion, but I cant' test it, since
>  I don't see your problem here.
>

No luck:

test1=# create table servx (fechax date);
CREATE
test1=# insert into servx values ('01-10-2000');
INSERT 81748516 1
test1=# select * from servx;
   fechax
------------
 30-09-2000
(1 row)

test1=# insert into servx values ('01-10-2000'::date);
INSERT 81748517 1
test1=# select * from servx;
   fechax
------------
 30-09-2000
 30-09-2000
(2 rows)

And (to make things worse) the result is rather random:

On the same machine, I start another db ...

[postgres@bert postgres]$ createdb test2
CREATE DATABASE
[postgres@bert postgres]$ psql test2
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test2=# create table servx (fechax date);
CREATE
test2=# insert into servx values ('01-10-2000');
INSERT 81748585 1
test2=# select * from servx;
   fechax
------------
 01-10-2000
(1 row)

test2=# insert into servx values ('01-10-2000'::date);
INSERT 81748586 1
test2=# select * from servx;
   fechax
------------
 01-10-2000
 01-10-2000
(2 rows)

test2=# create table servxx (fechax date,fechaxx date);
CREATE
test2=# insert into servxx values ('01-10-2000','01-10-2000');
INSERT 81748597 1
test2=# select * from servxx;
   fechax   |  fechaxx
------------+------------
 01-10-2000 | 01-10-2000
(1 row)

test2=# insert into servxx values ('13-10-2000','01-10-2000');
INSERT 81748598 1
test2=# select * from servxx;
   fechax   |  fechaxx
------------+------------
 01-10-2000 | 01-10-2000
 13-10-2000 | 30-09-2000


Aaggghhh!!!!

Thanks, anyway

Hernan Gonzalez
Argentina

pgsql-general by date:

Previous
From: "Ross J. Reedstrom"
Date:
Subject: Re: nasty problem with redhat 6.2 + pg 7.02
Next
From: Tom Lane
Date:
Subject: Re: error compiling plperl