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

Interval bug

From: Mitchell Brandsma <mitchell(at)pienetworks(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Interval bug
Date: 2001-01-10 04:28:03
Message-ID: 3A5BE4D3.9E7592E4@pienetworks.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi guys,

Firstly,
Postgres rocks!  Our live database is rapidly growing and
ever-reliable.  We're stupidly storing URLs visited by nearly a thousand
of our own machines, and are yet to miss a beat (apart from the obvious
speed problems)!

Secondly, we have a problem with interval math, detailed below.  Should
be reproducible on any platform(?)

Regards,
- Mitchell Brandsma (mitchell(at)pienetworks(dot)com)

Version: 7.0.2 (is it fixed in the latest?)

To reproduce:
psql <nameyourdatabase>

=# select '2 years ago'::interval + '1 year 360 days 00:00'::interval;
       ?column?
----------------------
 1 year 360 00:00 ago        ### Wrong! (Should be 5 or 6 days depending
on interpretation?)
(1 row)

=# select '2 years ago'::interval - '1 year 360 days 00:00'::interval;
       ?column?
-----------------------
 3 years 360 00:00 ago        ### Correct

=# select '365 days'::interval - '1 year'::interval;        <-- should
be ~ 0 or 1 day(s)
       ?column?
----------------------
 1 year 365 00:00 ago    ### Wrong!
(1 row)

Another eg:

=# select 'today'::datetime;
        ?column?
------------------------
 2001-01-10 00:00:00+08    ###  Today
(1 row)

=# create temp table testdate_mkb (td interval);
CREATE
=# insert into testdate_mkb select ('2 years'::interval - '1 year 353
00:00 ago') ;
INSERT 246954387 1
=# select * from testdate_mkb;
        td
-------------------
 3 years 353 00:00            ### !!!!!!
(1 row)

pielive=# select 'today'::datetime + td from testdate_mkb;
        ?column?
------------------------
 2004-12-28 00:00:00+08
(1 row)

The correct answer is  of course about 12 days (2 years - 1 year 353
days) from now, not nearly 4 years

Platforms tried on:
PIII 500, uname: Linux dbserver 2.2.13 #21 Thu Dec 16 06:50:30 WST 1999
i686 unknown
Celeron, uname: Linux dbserver 2.2.12-20 #1 Mon Sep 27 10:40:35 EDT 1999
i686 unknown (RedHat)


Responses

pgsql-bugs by date

Next:From: Bruno Wolff IIIDate: 2001-01-10 05:00:59
Subject: Problem using pg_dump going from 7.0.3 to 7.1beta3
Previous:From: James TroupDate: 2001-01-09 06:52:24
Subject: pgsql 7.1: int4out() brokeness?

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