BUG #5192: --disable-integer-datetimes changes timestamp comparison behavior - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5192
Logged by:          Thomas S. Chin
Email address:      thom@photoshelter.com
PostgreSQL version: 8.4.1
Operating system:   Linux 2.6.28-hardened-r9-bitshelter #6 SMP x86_64
Intel(R) Xeon(R)
Description:        --disable-integer-datetimes changes timestamp comparison
behavior
Details:

To whom it may concern:

We recently migrated to a 64 bit build of 8.4.1 and encountered failed
TIMESTAMP WITH TIME ZONE comparisons.  We have been able to install a build
from scratch (using default configuration files), restore a table from a
previous pg_dump, and see the failed comparison behavior.  With further
testing, we discovered this was not a problem on a 32 bit build of 8.4.1.
After that, we noticed that our build was using --disable-integer-datetimes,
and tested a build *not* including that option and the comparisons worked
again.  The Gentoo distribution enables this build option by default.

Here is some output from a working instance (32 bit and 64 bit):

psql (8.4.1)
Type "help" for help.

test=# \i test.pg_dump
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
test=# SET time zone 'GMT';
SET
test=# SELECT * FROM test ORDER BY tswtz LIMIT 10;
             tswtz
-------------------------------
 2009-11-14 18:36:25.232694+00
 2009-11-14 18:57:24.435819+00
 2009-11-14 19:19:04.423556+00
 2009-11-14 21:51:37.439181+00
 2009-11-14 21:51:37.439181+00
 2009-11-14 22:39:09.998319+00
 2009-11-14 23:53:41.220431+00
 2009-11-15 19:08:06.342069+00
 2009-11-15 21:43:18.842069+00
 2009-11-16 13:24:05.310301+00
(10 rows)

test=# SELECT * FROM test WHERE tswtz='2009-11-14 18:36:25.232694+00';
             tswtz
-------------------------------
 2009-11-14 18:36:25.232694+00
(1 row)

Here is sample output from a non-working instance (64 bit
--disable-integer-datetimes):

psql (8.4.1)
Type "help" for help.

test=# \i test.pg_dump
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
test=# SET time zone 'GMT';
SET
test=# SELECT * FROM test ORDER BY tswtz LIMIT 10;
             tswtz
-------------------------------
 2009-11-14 18:36:25.232694+00
 2009-11-14 18:57:24.435819+00
 2009-11-14 19:19:04.423556+00
 2009-11-14 21:51:37.439181+00
 2009-11-14 21:51:37.439181+00
 2009-11-14 22:39:09.998319+00
 2009-11-14 23:53:41.220431+00
 2009-11-15 19:08:06.342069+00
 2009-11-15 21:43:18.842069+00
 2009-11-16 13:24:05.310301+00
(10 rows)

test=# SELECT * FROM test WHERE tswtz='2009-11-14 18:36:25.232694+00';
 tswtz
-------
(0 rows)

test=# SELECT * FROM test WHERE tswtz::TEXT='2009-11-14
18:36:25.232694+00';
             tswtz

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

 2009-11-14 18:36:25.232694+00

(1 row)

Explicitly casting the string to TIMESTAMP WITH TIME ZONE or using
TO_TIMESTAMP() does not alter the behavior.  I will include the contents of
test.pg_dump at the end of this submission.

Is this a bug or some change in behavior I have been unsuccessful in finding
mention of in the documentation?

Thank you for taking a look at this.  If you need more information, please
feel free to contact me back.

Regards,
thom

[ test.pg_dump ]

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: public; Tablespace:
--


CREATE TABLE test (
    tswtz timestamp with time zone
);

--
-- Data for Name: test; Type: TABLE DATA; Schema: public;
--


COPY test (tswtz) FROM stdin;
2009-11-15 16:43:18.842069-05
2009-11-15 14:08:06.342069-05
2009-11-16 08:24:05.310301-05
2009-11-16 08:24:05.405265-05
2009-11-16 08:24:05.968728-05
2009-11-16 08:24:09.858852-05
2009-11-16 08:24:11.256977-05
2009-11-16 08:24:12.601362-05
2009-11-16 08:24:12.711625-05
2009-11-16 08:24:12.948307-05
2009-11-16 08:24:15.47644-05
2009-11-16 08:24:16.761545-05
2009-11-16 08:24:20.00961-05
2009-11-16 08:24:27.038844-05
2009-11-16 08:24:27.083511-05
2009-11-16 08:24:34.056791-05
2009-11-16 08:24:34.403483-05
2009-11-16 08:24:37.11973-05
2009-11-16 08:24:37.877904-05
2009-11-16 08:24:38.143879-05
2009-11-16 08:24:37.197807-05
2009-11-16 08:24:41.436269-05
2009-11-16 08:24:42.452391-05
2009-11-16 08:24:45.312303-05
2009-11-16 08:24:46.097378-05
2009-11-16 08:24:47.654608-05
2009-11-16 08:24:49.04999-05
2009-11-16 08:24:49.087914-05
2009-11-16 08:24:49.091592-05
2009-11-16 08:24:50.409203-05
2009-11-16 08:24:51.478314-05
2009-11-16 08:24:51.478314-05
2009-11-16 08:24:51.831168-05
2009-11-16 08:24:51.831168-05
2009-11-16 08:24:51.831168-05
2009-11-16 08:24:51.831168-05
2009-11-16 08:24:51.845325-05
2009-11-16 08:24:51.850146-05
2009-11-16 08:24:51.859057-05
2009-11-16 08:24:51.86296-05
2009-11-16 08:24:55.190574-05
2009-11-16 08:24:55.197294-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.765875-05
2009-11-16 08:24:56.893104-05
2009-11-16 08:24:59.714882-05
2009-11-14 13:36:25.232694-05
2009-11-14 14:19:04.423556-05
2009-11-14 13:57:24.435819-05
2009-11-14 16:51:37.439181-05
2009-11-14 16:51:37.439181-05
2009-11-14 17:39:09.998319-05
2009-11-14 18:53:41.220431-05
\.


--
-- PostgreSQL database dump complete
--