Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, what about 1.5 * '1 day'. By my logic multiplication and division
> were by definition imprecise. Is the logic that we spill down only for
> non-integral values?

Right. Interval multiplication has always spilled fractional months
over to seconds, but never the reverse. We have to have that same
policy now for fractional days.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, what about 1.5 * '1 day'. By my logic multiplication and division
> > were by definition imprecise. Is the logic that we spill down only for
> > non-integral values?
>
> Right. Interval multiplication has always spilled fractional months
> over to seconds, but never the reverse. We have to have that same
> policy now for fractional days.

OK, I think that makes sense.

--
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 <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Right. Interval multiplication has always spilled fractional months
>> over to seconds, but never the reverse. We have to have that same
>> policy now for fractional days.

> OK, I think that makes sense.

I've applied this change to interval_mul and interval_div, but the
justify_hours call is still there in timestamp_mi. Taking that one out
causes quite a lot of changes in the regression test outputs, so I'm
a bit hesitant to do it. Arguably, we need separate versions of
timestamp_mi and timestamptz_mi, with a DST-aware calculation in the
latter, but that seems a bit large of a change for late beta. The
reason is that with 8.1, we have this discrepancy:

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.

Another problem I've noticed is that interval output works with a
"struct tm" as intermediate data structure, which means that it cannot
cope with intervals containing a "time" field exceeding 2^31 hours,
because the tm_hour field overflows. With the new version of
interval_mul this is easily exposed by this test case:

regression=# select 10000 * '1000000 hours'::interval;
?column?
------------------
2147483647:00:00
(1 row)

but it was possible to get the same problem in other ways before,
so I don't think this is interval_mul's fault. Rather, interval2tm
has got to be replaced with something that can handle the full range of
representable interval values.

Finally, I notice there are no overflow checks in any of the interval
or timestamp arithmetic routines. This seems like a bad omission,
particularly in the integer-timestamp case where overflow won't be even
a little bit graceful.

So, a few TODO items for future releases:

* Improve timestamptz subtraction to be DST-aware
* Fix interval display to support values exceeding 2^31 hours
* Add overflow checking to timestamp and interval arithmetic

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <tgl@sss.pgh.pa.us> 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'?

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';

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Klint Gore wrote:
> On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <tgl@sss.pgh.pa.us> 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

On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> test-> ('2005-10-30 13:22:00-05'::timestamptz -
> test(> '2005-10-29 13:22:00-04'::timestamptz);
> ?column?
> ----------------
> 1 day 01:00:00



+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

[sorry about the previous email, I quoted the wrong bit and clicked the
wrong button]

On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
> 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)

Is that actually the correct answer?

Disregarding daylight savings, there is 25hrs between them. Once
daylight savings is taken into account there should be 24 or 26 hours
between them (southern/northern hemisphere respectively).

Or have I missed something obvious?

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>> 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)
>
>
> Is that actually the correct answer?
>
> Disregarding daylight savings, there is 25hrs between them. Once
> daylight savings is taken into account there should be 24 or 26 hours
> between them (southern/northern hemisphere respectively).

the whole DST thing falls apart when you deal with places that don't
respect it... arizona (except the navajo nation), for instance....

it would be impossible to calculate the 'correct' answer without knowing
the exact location...

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Klint Gore <kg@kgb.une.edu.au> writes:
> On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian
> <pgman@candle.pha.pa.us> wrote:
>> 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)

> Is that actually the correct answer?

I'm of the opinion that the correct answer, or at least the usually
desired answer, is "1 day".

> Disregarding daylight savings, there is 25hrs between them. Once
> daylight savings is taken into account there should be 24 or 26 hours
> between them (southern/northern hemisphere respectively).

If you want the numeric "25 hours" answer, you can always extract(epoch)
from both of them and subtract. There isn't any way to get a symbolic
"1 day" answer unless we make timestamp subtraction provide it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

John R Pierce <pierce@hogranch.com> writes:
> the whole DST thing falls apart when you deal with places that don't
> respect it... arizona (except the navajo nation), for instance....

> it would be impossible to calculate the 'correct' answer without knowing
> the exact location...

No, rather say "without knowing the correct timezone". All of this is
about doing the calculations properly according to the rules of the
current TimeZone setting. It's irrelevant whether the calculations are
correct with respect to some other timezone rules; obviously they won't
be.

(A separate issue is whether we know the rules for any particular
timezone you might wish to use. I'm pretty sure the zic database covers
everything anyone could possibly care about, though ;-))

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