Re: Bug with timestamp !!!

On Sat, May 12, 2001 at 02:23:30PM +0400, Alexander Dederer wrote:
> Look this:
> create table tmp (create_date timestamp);
>
> #insert into tmp values('2001-04-01 02:29:52');
> INSERT 1021715 1
>
> #select * from tmp;
> create_data
> ------------------------
> 2035-05-29 01:33:36-05
> (1 row)

While I don't see such dramatic results with this bad input data, I did
run across something a little peculiar:

<begin test>
drop table date_test;
drop sequence date_test_id_seq;

create sequence date_test_id_seq;
create table date_test (
id integer default nextval('date_test_id_seq'),
ts timestamp,
dt datetime
);

insert into date_test (ts, dt) values
('2001-04-01 01:01:01','2001-04-01 02:02:02');

insert into date_test (ts, dt) values
('2001-04-01 02:02:02','2001-04-01 01:01:01');

insert into date_test (ts, dt) values
('2001-04-01 02:02:02','2001-04-01 03:03:03');

insert into date_test (ts, dt) values
('2001-04-01 03:03:03','2001-04-01 02:02:02');

select * from date_test;

id | ts | dt
----+------------------------+------------------------
1 | 2001-04-01 01:01:01-08 | 2001-04-01 01:02:02-08
2 | 2001-04-01 01:02:02-08 | 2001-04-01 01:01:01-08
3 | 2001-04-01 01:02:02-08 | 2001-04-01 03:03:03-07
4 | 2001-04-01 03:03:03-07 | 2001-04-01 03:02:02-07
(4 rows)
<end test>

Notice how illegal times in the two o'clock hour are interpreted
differently depending on whether the first timestamp occurred before or
after the time zone change. The representations are both technically
the same, though I think it might lead to problems with date arithmetic
later on (I dunno). Daylight savings time should be abolished 8-}

--
Eric G. Miller <egm2(at)jps(dot)net>