Re: nasty problem with redhat 6.2 + pg 7.02

> 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(at)bert postgres]$ createdb test2
CREATE DATABASE
[postgres(at)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