Thread: Interval resolution

Interval resolution

From
"Geert Jansen"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I'm running PostgreSQL 7.4.6 on RHEL 4 Beta 2. There seems to be a
bug, or call it inconsistentcy with the documentation, in the
interval data type. According to
http://www.postgresql.org/docs/7.4/static/datatype-datetime.html, the
resolution of interval is 1 microsecond with a range from -178000000
to 178000000  years. In the example below, this resolution is not
provided:

<quote>
geertj@greet:~> psql
Welcome to psql 7.4.6, 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

geertj=> create table test (iv interval);
CREATE TABLE
geertj=> insert into test values ('701868 days, 1:26:00.685041');
INSERT 22447 1
geertj=> select * from test;
               iv
- --------------------------------
 701868 days 01:26:00.685043335
(1 row)
</quote>

As you can see, there's an error of 2 microseconds. After a bit of
experimentation I've noticed that the error becomes larger when the
interval becomes bigger.

Now I don't know if this is a serious bug, and I only found it when
doing unit testing with random data. However, it is an inconsistency
with the documentation that states microsecond resolution is provided
over the entire range.

Regards,
Geert Jansen

- --
Geert Jansen <geert@boskant.nl>
PGP fingerprint: 97FA CE01 F760 4FA9 A0EC  F7F9 9ACF 9153 95E2 50E0

-----BEGIN PGP SIGNATURE-----
Version: PGP 8.0.3

iQEVAwUBQepBZZrPkVOV4lDgAQJB8Af/UzpzMZMX6rwl1oaaezPuku/FU3MSCyzV
6mUMzeP3ACZ5tTnWs6tQcb0gvEA6+ckaX2UkGFRP/NZc6xdpmUKVE/8K9/h/y/Vf
t+XweyOfm3VUVHbxCaSuruuVn1kH9+zWaUS2NDB/+EwJ6ooZFfrBT8233pReNUCM
1r21Nx2koz4rODNugopeBiF9FwzcgJFcOpSlU1Hh3uYh/quSD3t0P4rVMzdRnqm3
r5FoRiMgfSefrFo0lDm9Kkw7QLi5e8chXgzrkTAZknkU9CcOb0D8/h6pdo2upyXI
8tCj2DQBvb4ZMDDs0YlZOxlFzIej5SQsEonobWXNLYvxUXJS0bsEbg==
=fauh
-----END PGP SIGNATURE-----

Re: Interval resolution

From
Tom Lane
Date:
"Geert Jansen" <geert@boskant.nl> writes:
> I'm running PostgreSQL 7.4.6 on RHEL 4 Beta 2. There seems to be a
> bug, or call it inconsistentcy with the documentation, in the
> interval data type. According to
> http://www.postgresql.org/docs/7.4/static/datatype-datetime.html, the
> resolution of interval is 1 microsecond with a range from -178000000
> to 178000000  years. In the example below, this resolution is not
> provided:

See the NOTE below the table.  The documentation does need improvement:
it only discusses precision for timestamp, without making it clear that
this applies to time and interval as well.

            regards, tom lane