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

INTERVAL representation

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: INTERVAL representation
Date: 2000-11-03 16:25:23
Message-ID: 3A02E6F3.76679E3F@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: Zeugswetter Andreas SBDate: 2000-11-03 16:32:21
Subject: AW: AW: LIMIT in DECLARE CURSOR: request for comments
Previous:From: Tom LaneDate: 2000-11-03 16:10:55
Subject: Re: AW: LIMIT in DECLARE CURSOR: request for comments

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