Tom wrote:
> At this point it should move to pghackers, I think.
(responding to a patch for ISO 8601 "Time Intervals" in pgsql-patches)
Looks like I'll take a shot at more broadly hacking the postgresql
time interval code. Before doing so, I wanted to ask opinions
regarding what the "right" behavior is of various timestamp/interval
operations.
I think the best way ask the specific questions is to ask a
quiz highlighting some of the unexpected behavior with the
current implementation.
1. What should this expression give:
select '0.01 years'::interval > '0.01 months'::interval;
A) False - the first is 0 months, the second is about 25000 seconds. B) True - one is about 300000 seconds,
theother is about 25000. C) An error - fractional dates are asking for trouble. D) Something else -- please tell
me.
2. If I have this expression:
select '2003-01-31'::timestamp + '2 months', '2003-01-31'::timestamp + '1 month' + '1 month'
'2003-01-31'::timestamp + '0.5 months'::interval * 4;
would I expect the results to:
A) All be different. The first is 89 days, (Mar 31, because it's the last day of Mar). the second 86
days,(Mar 28, because February clips the date) and the third 90 days (Apr 01, because half-months are 15 days).
B)All should be the same. Two months is two months no matter how you slice it. C) An error - with fractional
monthsbeing undefined. D) Something else -- please tell me.
3. Or odd behavior with time-zones.
select '2002-01-01'::timestamp + '6 months', '2002-01-01'::timestamp + '181 days',
'2002-01-01'::timestamp+ '4344 hours';
Note that those months have 181 days, and 4344 is 181 days * 24 hours. I would expect:
A) The first one represents midnight on 2002-07-01. The second two one hour different (1AM) to make up
forthe missed hour on daylight savings.
B) The first two expressions (Days and Months) are both "calendar time" so they'd both be midnight. Only
thethird one would be 1AM.
D) Something else -- please tell me.
To give away the answers...
(A) Appears to be current behavior. (B) Is one possible proposal that started being discussed on PGPatches. (C) Is one
otherpossible proposal that mentioned on PGPatches. (D) Would be appreciated.
I'd love to hear what any specs, especially the SQL spec
has to say for it.
Ron