Thread: timespan problem

hi,
i ran into a strange result using the timespan type. No,
it's not related to y2k, but i don't know whether it is a
bug or i just used wrong typecasting.
here it is:


forum=> select datetime(now()) as ido;
ido
----------------------------
Sat Jan 01 21:35:32 2000 CET
(1 row)

forum=> select datetime(now())+'74565 days'::timespan as ido;
ido                                                          
------------------------
Thu Jan 19 14:07:30 2068
(1 row)                 

forum=> select datetime(now())+'74566 days'::timespan as ido;
ido
----------------------------                                 
Tue Dec 15 08:39:21 1931 CET
(1 row)                     

forum=> select datetime(now())+'70 years'::timespan as ido;
ido    
------------------------
Wed Jan 01 20:36:01 2070                                   
(1 row)                 

forum=>


I don't see anything wrong in the third query, but the result
is a bit surprising... and notice the error in the second query
too: the time fields don't match.

I'm using version 6.5.2 on Red Hat Linux 6.0

Zsolt Tulassay
zsolt@tek.bke.hu

Tulassay Zsolt <zsolt@tek.bke.hu> writes:
> forum=> select datetime(now())+'74565 days'::timespan as ido;
> ido                                                          
> ------------------------
> Thu Jan 19 14:07:30 2068
> (1 row)                 

> forum=> select datetime(now())+'74566 days'::timespan as ido;
> ido
> ----------------------------                                 
> Tue Dec 15 08:39:21 1931 CET
> (1 row)                     

Of course, both of the above are drastically wrong, since 74575 days
should be upwards of 200 years.  The problem appears to be an internal
overflow in timespan_in, since you can show wrong results just with:

regression=> select '74565 days'::timespan;
?column?
-------------------------------------
@ 24854 days 17 hours 31 mins 44 secs
(1 row)

regression=> select '74566 days'::timespan;
?column?
-----------------------------------------
@ 24854 days 12 hours 56 mins 32 secs ago
(1 row)

Looking into it, the guilty party seems to be tm2timespan() which
blithely assumes that it need not worry about overflow from its
"time" field to its "month" field:
   span->month = ((tm->tm_year * 12) + tm->tm_mon);   span->time = ((((((tm->tm_mday * 24) + tm->tm_hour) * 60) +
tm->tm_min)* 60) + tm->tm_sec);   span->time = JROUND(span->time + fsec);
 

Thomas, you want to deal with this one?  Or is this code all going
away in 7.0 anyway?
        regards, tom lane

> ... The problem appears to be an internal
> overflow in timespan_in...
> Thomas, you want to deal with this one? Or is this code all going
> away in 7.0 anyway?

I would guess the code is staying, though with bug fixes for this case
;-) I'll look at it...
                  - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> ... The problem appears to be an internal
>> overflow in timespan_in...
>> Thomas, you want to deal with this one? Or is this code all going
>> away in 7.0 anyway?

> I would guess the code is staying, though with bug fixes for this case
> ;-) I'll look at it...

I've thought about it some more and now understand why timespan is
represented as months + seconds: weeks, days, hours, and minutes all
have a unique conversion to seconds, and years and larger units all
have a unique conversion to months, but since months are variable
length the two domains cannot be unified.

So, the fundamental problem here is that the seconds field of a timespan
is int4, and therefore it overflows for timespans exceeding +/- 68 years
--- but only if the timespan is specified using smaller-than-month
units.

A reasonable solution would be to change the seconds field to float8,
which would give it range comparable to datetime itself (as well as the
ability to represent sub-second intervals).

I'm about half tempted to propose changing the months field from int4 to
float8 as well, but there probably would be no real gain from doing so.
OTOH, depending on your platform an int4+float8 structure may occupy 16
bytes anyway...
        regards, tom lane

> The problem appears to be an internal
> overflow in timespan_in...
> Looking into it, the guilty party seems to be tm2timespan() which
> blithely assumes that it need not worry about overflow from its
> "time" field to its "month" field:
>     span->month = ((tm->tm_year * 12) + tm->tm_mon);
>     span->time = ((((((tm->tm_mday * 24) + tm->tm_hour) * 60) + 
>                    tm->tm_min) * 60) + tm->tm_sec);
>     span->time = JROUND(span->time + fsec);
> Thomas, you want to deal with this one?

Sure. In the meantime, change the "24" to "24.0" (in the file
src/backend/utils/adt/dt.c around line 2444; hmm, is that line number
a coincidence??) and things will start working :(

btw, there is no possibility for overflow from "time" to "month"; see
below...

> Or is this code all going away in 7.0 anyway?

No, the code is likely to stay since timespan needs to distinguish
between "qualitative time" like months and years and "quantitative
time" like hours and seconds. "time" is stored as a double, but the
calculation was being done with all integers, which lead to the
overflow problem in intermediate results.

Sorry about that. Will commit changes soon.
                     - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California