Thread: INTERVAL representation

I've been working on date/time issues over the last few weeks (at least
one or two from reports on the list, others that I've stumbled across,
and even one or two planned ones ;)

Anyway, the INTERVAL type output representation has trouble with values
such as
 '-1 month +2 hours'

since it assumes that the *sign* of every field is the same (the current
development tree may have other troubles with interpreting this too, but
I'm fixing that). For years/months, that resolves as those values are
stored, so, for example, '-1 year +1 month' becomes '-11 months' as it
is stored, and '-1 day +2 hours -4 minutes' resolves similarly. But
months and days/hours/minutes/seconds are stored separately, so could
have different signs.

Any suggestions for how to represent mixed-sign intervals? I'm inclined
to move away from the "ago" convention for representing negative
intervals, since "-1 month +2 hours ago" could be considered a little
ambiguous.

Should we move to signed-only representations? Or retain the "ago"
convention, having it match the sign of the first printed field, with
subsequent fields having negative signs if they are positive values?

At the moment, mixed-sign intervals are stored correctly (so have the
right results for math) but are *not* represented in the output
correctly.

Possibilities are:

'1 month -2 days ago' is less than a month ago.
'1 month -2 days +03:04' is three hours more than two days less than a
month from now.
'-1 month +2 days' is less than a month ago.

Comments?
                       - Thomas

On Fri, Nov 03, 2000 at 04:25:23PM +0000, Thomas Lockhart wrote:
> I've been working on date/time issues over the last few weeks (at least
> one or two from reports on the list, others that I've stumbled across,
> and even one or two planned ones ;)
> 
<snip>
> 
> Should we move to signed-only representations? Or retain the "ago"
> convention, having it match the sign of the first printed field, with
> subsequent fields having negative signs if they are positive values?
> 
> At the moment, mixed-sign intervals are stored correctly (so have the
> right results for math) but are *not* represented in the output
> correctly.
> 
> Possibilities are:
> 
> '1 month -2 days ago' is less than a month ago.
> '1 month -2 days +03:04' is three hours more than two days less than a
> month from now.
> '-1 month +2 days' is less than a month ago.
> 
> Comments?
> 

Hmm, negative time values always force me to think twice. I guess I think of
time as a concrete thing, like a board: it has length, at to speak of a 
negative length' makes little sense. Admittedly, time is inherently vectorial,
which other physical length measurements are not, requiring an arbitrarily
chosen point as reference.

Hmm, I started this reply planning on arguing that _keeping_ the 'ago'
was easiest on my ears. Now I find I've talked myself into losing it,
because it implies too much: 'ago' claims that that one end of the
interval is 'now' and the other end is in the past. If what you've got
is actually the difference between next Christmas and New Years:

template1=# select ('25/12/2000'::timestamp - '01/01/2001'::timestamp)          as "deadtime";
 deadtime   
-------------7 00:00 ago
(1 row)

That seems just wrong.

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

> Hmm, I started this reply planning on arguing that _keeping_ the 'ago'
> was easiest on my ears. Now I find I've talked myself into losing it,
> because it implies too much: 'ago' claims that that one end of the
> interval is 'now' and the other end is in the past. If what you've got
> is actually the difference between next Christmas and New Years:
> template1=# select ('25/12/2000'::timestamp - '01/01/2001'::timestamp)
>            as "deadtime";
>   deadtime
> -------------
>  7 00:00 ago
> (1 row)
> That seems just wrong.

I've removed the "ago convention" from the ISO interval format, but have
retained it for the "traditional Postgres" format. In the latter case,
the first numeric field is never negative, and the "ago", if present,
indicates a negative interval. Subsequent fields can have a positive or
negative sign, and if negative will indicate a sign flip relative to the
leading "ago-qualified" field.

The input interpretation of all of this is about the same as for 7.0.2,
though we now do a better job coping with more variations on the
"hh:mm:ss" style of representation.

Take a look at it and let me know what y'all think!
                   - Thomas