Re: [SQL] timespan problem

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