Bug with timestamp !!!

Sorry, that last post was a slip of the finger.

What I meant to say was:

I followed the same steps below on 7.1.0 (Linux),
and got this:

create_date
------------------------
2001-04-01 03:29:52-04
(1 row)

(i.e. 1 hour off)

I'm not using any strange locale settings or multibyte
stuff.

Weird...

Cheers,

Neil

ipa=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.1 on i386-unknown-freebsd4.2, compiled by GCC 2.95.2
(1 row)
ipa=# create table tmp (create_date timestamp);
CREATE
ipa=# insert into tmp values('2001-04-01 02:29:52');
INSERT 295890 1
ipa=# select * from tmp;
create_date
------------------------------
Tue May 29 02:34:00 2035 EDT
(1 row)

I have datestyle set to postgres -- is that why this happened?

-Mitch

----- Original Message -----
From: "Neil Conway" <nconway@klamath.dyndns.org>
To: <pgsql-general@postgresql.org>; <dederer@spb.cityline.ru>
Sent: Sunday, May 13, 2001 12:53 AM
Subject: Re: Bug with timestamp !!!

"Mitch Vincent" <mitch@venux.net> writes:

ipa=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.1 on i386-unknown-freebsd4.2, compiled by GCC 2.95.2

Is that really the release version, or a prerelease? (To be specific,
do you have v 1.49 of src/backend/utils/adt/timestamp.c?)

ipa=# insert into tmp values('2001-04-01 02:29:52');

If you live in the USA, 4/1 was a DST transition day: there is no such
time as 2:29 AM local time. Some C libraries cope with this better than
others. We made some last-minute patches in 7.1.1 that are intended to
work around the less-well-behaved libraries, and I'm wondering whether
you have 'em.

regards, tom lane

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@jps.net>

"Eric G. Miller" <egm2@jps.net> writes:

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

With what PG version? On what platform?

Comparing 7.1.1 against 7.0.*, I note that the resolution of "illegal"
times during a DST jump has changed, at least on my platform (HPUX 10.20).
7.0.* resolves the time backwards whereas current sources resolve
forwards: "select timestamp('2001-04-01 02:02:02')" yields
2001-04-01 01:02:02-05 in 7.0.2
2001-04-01 03:02:02-04 in current
Since this is dependent on how the local mktime() library routine
reacts to "illegal" times, some platform-to-platform variation is to be
expected. Your example looks like mktime() must actually have some
internal state on your machine, causing its result to depend on what
it was asked previously :-(

regards, tom lane

On Sun, May 13, 2001 at 11:27:45AM -0400, Tom Lane wrote:

"Eric G. Miller" <egm2@jps.net> writes:

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

With what PG version? On what platform?

# select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4

Debian GNU/Linux, "unstable"

GNU libc6 2.2.3-1

Comparing 7.1.1 against 7.0.*, I note that the resolution of "illegal"
times during a DST jump has changed, at least on my platform (HPUX 10.20).
7.0.* resolves the time backwards whereas current sources resolve
forwards: "select timestamp('2001-04-01 02:02:02')" yields
2001-04-01 01:02:02-05 in 7.0.2
2001-04-01 03:02:02-04 in current
Since this is dependent on how the local mktime() library routine
reacts to "illegal" times, some platform-to-platform variation is to be
expected. Your example looks like mktime() must actually have some
internal state on your machine, causing its result to depend on what
it was asked previously :-(

Yes, I think the timezone environment variable is being carried. I wrote
a small test program, and the same weirdness happens. So, it's not
specifically a PostgreSQL issue.

--
Eric G. Miller <egm2@jps.net>

On Sun, May 13, 2001 at 04:49:29PM -0700, Eric G. Miller wrote:

Comparing 7.1.1 against 7.0.*, I note that the resolution of "illegal"
times during a DST jump has changed, at least on my platform (HPUX 10.20).
7.0.* resolves the time backwards whereas current sources resolve
forwards: "select timestamp('2001-04-01 02:02:02')" yields
2001-04-01 01:02:02-05 in 7.0.2
2001-04-01 03:02:02-04 in current
Since this is dependent on how the local mktime() library routine
reacts to "illegal" times, some platform-to-platform variation is to be
expected. Your example looks like mktime() must actually have some
internal state on your machine, causing its result to depend on what
it was asked previously :-(

Yes, I think the timezone environment variable is being carried. I wrote
a small test program, and the same weirdness happens. So, it's not
specifically a PostgreSQL issue.

Follow-up:

I tested alway initializing the members of "struct tm" to INT_MAX the
performed the following operations:

1) strptime() to parse date/time string into struct tm
2) mktime() for time_t
3) localtime() for a struct tm again
4) strftime() for a string representation again

After doing that, the results become consistent regardless of the order
of the calls. You can't use "memset(theTime, 0, sizeof(struct tm))"
because when tm_isdst = 0 it say daylight savings time is false (which
you don't know yet from a string like '2001-04-01 02:30:30').

Example output from my test program:

BEFORE
ONE: 2001-04-01 01:30:30
TWO: 2001-04-01 02:30:30
THREE: 2001-04-01 03:30:30
AFTER
ONE: 2001-04-01 01:30:30-0800
TWO: 2001-04-01 01:30:30-0800
THREE: 2001-04-01 03:30:30-0700
REVERSED
THREE: 2001-04-01 03:30:30-0700
TWO: 2001-04-01 01:30:30-0800
ONE: 2001-04-01 01:30:30-0800

Without initializing the struct tm members to INT_MAX, the 2:30:30 time
would get interpreted as 1:30:30-0800 or 3:30:30-0700, and the 3:30:30
time would get returned as 4:30:30-0800 (which is right and wrong).

--
Eric G. Miller <egm2@jps.net>

"Eric G. Miller" <egm2@jps.net> writes:

After doing that, the results become consistent regardless of the order
of the calls. You can't use "memset(theTime, 0, sizeof(struct tm))"
because when tm_isdst = 0 it say daylight savings time is false (which
you don't know yet from a string like '2001-04-01 02:30:30').

But we don't do that --- we set tm_isdst = -1 so as to ask mktime's
opinion whether the time is DST or not. That is the case that seems
to have some history-dependent behavior ...

regards, tom lane

On Sun, May 13, 2001 at 01:34:51AM -0400, Tom Lane wrote:

If you live in the USA, 4/1 was a DST transition day: there is no such
time as 2:29 AM local time. Some C libraries cope with this better than
others. We made some last-minute patches in 7.1.1 that are intended to
work around the less-well-behaved libraries, and I'm wondering whether
you have 'em.

unless you count yourself among the intelligent folk of arizona
or indiana (except southwest/evansville and northwest gary).
those bright people leave their clocks alone.

<jealous>dammit.</jealous>

--
What do I need manners for? I already got me a wife.
-- Adam Pontipee, "Seven Brides for Seven Brothers"

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!