Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Sorry, I should have CC'ed hackers on this. The issue is that because
of interval_justify_hours(), subtracting a fixed interval from a
timestamp and re-adding the same value produces a different result.
---
Bruce Momjian wrote:
I saw a lot of disussion because I forgot to specify that my tests were
for EST5EDT, but what about the use of interval_justify_hours() in
timestamp_mi(). Is this something we want to change?
---
Bruce Momjian wrote:
Klint Gore wrote:
On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane [EMAIL PROTECTED] wrote:
regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1
day'::interval;
?column?
2005-10-30 13:22:00-05
(1 row)
regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz;
?column?
1 day 01:00:00
(1 row)
ISTM that given the former result, the latter calculation ought to
produce '1 day', not something else.
Would the '1 day' result know it was 24 hours or be the new 23/24/25
hour version of '1 day'?
It has no idea. When you do a subtraction, it isn't clear if you are
interested in days or hours, so we give hours. If you want days,
you should convert the timestamps to dates and just subtract them.
If it was the new version, could you get the original values back?
i.e. what would be the result of
select
('2005-10-29 13:22:00-04'::timestamptz +
('2005-10-30 13:22:00-05'::timestamptz -
'2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
You bring up a good point here. With current CVS your subtraction
yields:
test- ('2005-10-30 13:22:00-05'::timestamptz -
test( '2005-10-29 13:22:00-04'::timestamptz);
?column?
1 day 01:00:00
(1 row)
so adding that to the first timestamp gets:
test= select
test- ('2005-10-29 13:22:00-04'::timestamptz +
test( ('2005-10-30 13:22:00-05'::timestamptz -
test( '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
timezone
-
2005-10-30 14:22:00
(1 row)
This is certainly _not_ what someone would expect as a return value.
What happens is that we subtract to generate the number of hours
different, but then get all smart that oh, that is one day to add, and
one hour and return an unexpected value.
This is actually a good argument that the use of
interval_justify_hours() in timestamp_mi() is a mistake. Without this
call, we have:
test= select
test- ('2005-10-30 13:22:00-05'::timestamptz -
test( '2005-10-29 13:22:00-04'::timestamptz);
?column?
--
25:00:00
(1 row)
and
test= select
test- ('2005-10-29 13:22:00-04'::timestamptz +
test( ('2005-10-30 13:22:00-05'::timestamptz -
test( '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
timezone
-
2005-10-30 13:22:00
(1 row)
but it also has the tendency to return some very high values for hours:
test= select
test- ('2005-12-30 13:22:00-05'::timestamptz -
test( '2005-10-29 13:22:00-04'::timestamptz);
?column?
1489:00:00
(1 row)
but again, if you want days, you can cast to days.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
[ bugs list removed, hackers added.]
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
I saw a lot of disussion because I forgot to specify that my tests were
for EST5EDT, but what about the use of interval_justify_hours() in
timestamp_mi(). Is this something we want to change?
It's too late to mess with it for 8.1, but see my previous message
proposing a set of TODO items for future work.
Yes, it is late, but I am worried about adding an interface change that
we will later revert in 8.2. In 8.0.X I see the query returning the '25
hour' answer:
SELECT
('2005-10-29 13:22:00-04'::timestamptz +
('2005-10-30 13:22:00-05'::timestamptz -
'2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
timezone
-
2005-10-30 13:22:00
(1 row)
In current CVS the top query returns '14:22:00'. Do we change this for
8.1, then change it back in 8.2? That seems bad to me.
Actually, 8.0.X returns '1 day, 1 hour' for the subtraction, which we
treat in 8.0.X as '25 hours':
SELECT
('2005-10-30 13:22:00-05'::timestamptz -
'2005-10-29 13:22:00-04'::timestamptz);
?column?
1 day 01:00:00
(1 row)
In 8.0.X, because we didn't have a 'days' field, we could treat '1 day 1
hour' as always '25 hours', and could display the results as days/hours.
If we remove interval_justify_hours(), then we are always going to
display timestamp subtraction in hours (not days), e.g. '6422 hours'
(yea, ugly) unless they manually call interval_justify_hours().
Keep in mind that the addition of the interval_justify_hours() did
generate some regression test changes, so removing
interval_justify_hours() might just take the results back to what we had
in 8.0. My point is that regression changes caused by its removal might
not be a good guide to determining compatibility with 8.0.X.
I guess my point is that we are changing 8.0.X behavior so we better be
sure it is now the way we want it to remain.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Bruce Momjian pgman@candle.pha.pa.us writes: Keep in mind that the addition of the interval_justify_hours() did generate some regression test changes, so removing interval_justify_hours() might just take the results back to what we had in 8.0. Not hardly. I tried already. The existing timestamp_mi behavior is probably as close to 8.0 as we can get given the change in underlying representation. I guess my point is that we are changing 8.0.X behavior so we better be sure it is now the way we want it to remain. [ shrug... ] We've changed datetime behavior in every past release, we're changing it for 8.1, we'll probably change it some more for 8.2, and again after that. All the datetime code is a work in progress. Get used to it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Keep in mind that the addition of the interval_justify_hours() did generate some regression test changes, so removing interval_justify_hours() might just take the results back to what we had in 8.0. Not hardly. I tried already. The existing timestamp_mi behavior is probably as close to 8.0 as we can get given the change in underlying representation. You mean the '6432 hours' is a worse change, OK. I guess my point is that we are changing 8.0.X behavior so we better be sure it is now the way we want it to remain. [ shrug... ] We've changed datetime behavior in every past release, we're changing it for 8.1, we'll probably change it some more for 8.2, and again after that. All the datetime code is a work in progress. Get used to it. OK, as long as we are sure we are not going to change it back to 8.0 behavior. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Not hardly. I tried already. The existing timestamp_mi behavior is probably as close to 8.0 as we can get given the change in underlying representation. You mean the '6432 hours' is a worse change, OK. Well, it's sure not a small change, and we're still undecided whether that's what we want in the long run. Also, we'd have to deal with some of the other TODO items I mentioned before we could make it work at all. There's at least one regression test that computes an interval larger than 2^31 hours (how do you think I found out about that problem ;-)) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Kevin Grittner [EMAIL PROTECTED] writes: The standard seems rich enough in this area to address all of the concerns I've seen expressed on this thread. All the usual advantages for standards compliance accrue, as well. Last I checked, the standard completely failed to deal with daylight savings time changes, making it pretty useless as a guide to solving the problems we want to deal with. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
