Re: TimeZone bug again..... - Mailing list pgsql-bugs

Operating system is redhat 9

Postgres is 7.3.2 binary rpm for linux


Hi

It seems that because to_timestamp does a time-zone conversion it is
upsetting are attempt to load legitimate data...


Is it possible (similar to the to_date fix that was done some time ago by
tom lane) to have a similar
method like "to_timestamp_raw" or something that will store/retrieve
date-times as given in format/data
without doing any datetime timezone conversions?

This is going to be the only way that our data which spans form 1800 to
today and any time
specified can be stored and accurately retrieved..

Daylight savings etc we handle outside the database records...

Ie lsd stands for local standard time and tm represents UTC time which are
the
two times that we store...


Any help greately appreciated...

These date-time bugs are stopping us from advancing our climate database on
the external web using postgresql at the moment...


(If you cannot fix it can you tell me where in the src code the time-zone
conversion is done and I will comment it out or something...)


Cheers
Arn


Error Example attached... (Time-zone is AEST)


Error is:-

------------------------------------------------------------------------

tczadam=> insert into onelands(stn_num,lsd,tm,ob_qual_flag) values(1007,
to_timestamp('199310310100','YYYYMMDDHH24MI'),
to_timestamp('199310301300','YYYYMMDDHH24MI'),0);

INSERT 7278527 1

tczadam=> insert into onelands(stn_num,lsd,tm,ob_qual_flag) values(1007,
to_timestamp('199310310200','YYYYMMDDHH24MI'),
to_timestamp('199310301300','YYYYMMDDHH24MI'),0);

ERROR:  Cannot insert a duplicate key into unique index onelands_pkey


------------------------------------------------------------------------


Table is

create table tcz.ONELANDS (
 STN_NUM        NUMERIC(6,0),
 LSD            timestamp,
 TM             timestamp not null,
 OB_QUAL_FLAG   NUMERIC(1,0) not null,
 STN_PRES       NUMERIC(7,1)
        CONSTRAINT SFC_LNDS_stn_pres_ck
        CHECK (((stn_pres is not null) and
            (stn_pres between 900.0 and 1100.0))
            or (stn_pres is null)),
 STN_PRES_QUAL  NUMERIC(2,0)
        CONSTRAINT SFC_LNDS_stn_pres_q_ck
        CHECK (((stn_pres_qual is not null) and
            (stn_pres_qual between 0 and 20))
            or (stn_pres_qual is null)),
 primary key (stn_num,lsd)
);