Skip site navigation (1) Skip section navigation (2)

Daylight saving bug in PostgreSQL 7.2.1 ?

From: Jonas Lindholm <jlhm(at)usa(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Daylight saving bug in PostgreSQL 7.2.1 ?
Date: 2003-07-12 02:54:50
Message-ID: 3F0F787A.8090605@usa.net (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi!

I believe I've found a bug with daylight saving time.
The example below shows that after inserting a row with a date and time 
when daylight saving is changing (6 April 2003 02:00:00) any selects 
using the same time and date are only returning correct result first 
time. If the select is done again the row is not found.

CREATE SEQUENCE timespan_idx_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1; 

CREATE TABLE timespan (
  host text NOT NULL,
  starttime timestamptz NOT NULL,
  endtime timestamptz NOT NULL,
  idx int8 DEFAULT nextval('"timespan_idx_seq"'::text) NOT NULL,
  CONSTRAINT timespan_pkey PRIMARY KEY (host, starttime, endtime)
) WITH OIDS;

-- Index: timespan_i1
CREATE INDEX timespan_i1 ON timespan USING btree (host);

-- Index: timespan_i2
CREATE INDEX timespan_i2 ON timespan USING btree (starttime);

-- Index: timespan_i3
CREATE INDEX timespan_i3 ON timespan USING btree (endtime);

-- Index: timespan_i4
CREATE UNIQUE INDEX timespan_i4 ON timespan USING btree (idx);

-- Index: timespan_i5
CREATE INDEX timespan_i5 ON timespan USING btree (host, starttime);

-- Index: timespan_i6
CREATE INDEX timespan_i6 ON timespan USING btree (host, endtime);


INSERT INTO timespan VALUES('XXXXXX', ' 6-APR-2003 02:00:00', ' 6-APR-2003 03:00:00');

test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00';
 idx
-----
   1  <============= correct
(1 row)

test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00';
 idx
-----
(0 rows) <=============== not correct

test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00';
 idx
-----
(0 rows) <=============== not correct 

test=# \q
[root(at)xxxxxx scripts]# /usr/local/pgsql/bin/psql test
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

test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00';
 idx
-----
   1  <============= correct
(1 row)

test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00';
 idx
-----
(0 rows) <=============== not correct


/Jonas Lindholm


Responses

pgsql-bugs by date

Next:From: Jacek KalinskiDate: 2003-07-12 07:04:08
Subject: Re: Daylight saving bug in PostgreSQL 7.2.1 ?
Previous:From: Tom LaneDate: 2003-07-10 15:24:43
Subject: Re: pg_tables view definition incorrect??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group