Thread: BUG #1993: Adding/subtracting negative time intervals changes time zone of result
BUG #1993: Adding/subtracting negative time intervals changes time zone of result
From
"Nicholas"
Date:
The following bug has been logged online:
Bug reference: 1993
Logged by: Nicholas
Email address: hb@pg.x256.org
PostgreSQL version: 8.0.3,8.0.4,8.1
Operating system: Gentoo Linux
Description: Adding/subtracting negative time intervals changes time
zone of result
Details:
spatula ~ # psql -U postgres
Welcome to psql 8.1beta1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# SELECT VERSION();
version
----------------------------------------------------------------------------
----------------------------------------------------------
PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.3.6 (Gentoo 3.3.6, ssp-3.3.6-1.0, pie-8.7.8)
(1 row)
postgres=# SELECT NOW()-interval '1 week';
?column?
-------------------------------
2005-10-17 08:52:37.355219+10
(1 row)
postgres=# SELECT NOW()-interval '-1 week';
?column?
-------------------------------
2005-10-31 08:52:39.021583+11
(1 row)
postgres=#
Nicholas wrote: > The following bug has been logged online: > > Bug reference: 1993 > Logged by: Nicholas > Email address: hb@pg.x256.org > PostgreSQL version: 8.0.3,8.0.4,8.1 > Operating system: Gentoo Linux > Description: Adding/subtracting negative time intervals changes time > zone of result > Details: > > spatula ~ # psql -U postgres > Welcome to psql 8.1beta1, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > postgres=# SELECT VERSION(); > version > ---------------------------------------------------------------------------- > ---------------------------------------------------------- > PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC > i686-pc-linux-gnu-gcc (GCC) 3.3.6 (Gentoo 3.3.6, ssp-3.3.6-1.0, pie-8.7.8) > (1 row) > > postgres=# SELECT NOW()-interval '1 week'; > ?column? > ------------------------------- > 2005-10-17 08:52:37.355219+10 > (1 row) > > postgres=# SELECT NOW()-interval '-1 week'; > ?column? > ------------------------------- > 2005-10-31 08:52:39.021583+11 Looks to mee like Daylight Savings has conveniently started. > (1 row) > > postgres=# > > ---------------------------(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 08:51:59 +1000, Russell Smith <mr-russ@pws.com.au> wrote:
> Nicholas wrote:
> > postgres=# SELECT NOW()-interval '1 week';
> > ?column?
> > -------------------------------
> > 2005-10-17 08:52:37.355219+10
> > (1 row)
> >
> > postgres=# SELECT NOW()-interval '-1 week';
> > ?column?
> > -------------------------------
> > 2005-10-31 08:52:39.021583+11
>
> Looks to mee like Daylight Savings has conveniently started.
But the elapsed time for those results is only 6 days, 23 hours.
That's changed since v7.4.7
template1=# select now();
now
-------------------------------
2005-10-25 12:40:22.699545+10
(1 row)
template1=# select now() + '1 week'::interval;
?column?
------------------------------
2005-11-01 13:40:33.85492+11
(1 row)
template1=# select now() - '-1 week'::interval;
?column?
-------------------------------
2005-11-01 13:40:46.707656+11
(1 row)
template1=# select version();
version
--------------------------------------------------------------------------------
-------------------------
PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222
(Red Hat Linux 3.2.2-5)
(1 row)
+---------------------------------------+-----------------+
: 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 : :
+---------------------------------------+-----------------+
Klint Gore <kg@kgb.une.edu.au> writes:
> That's changed since v7.4.7
Yup. '1 week' = '7 days' which is no longer the same as 7*24 hours.
In particular, as of 8.1 local noon plus one day is still local noon,
even if there was a DST change in between. Adding 24 hours, on the
other hand, might give 11am or 1pm.
regards, tom lane
On Tue, Oct 25, 2005 at 12:48:10PM +1000, Klint Gore wrote:
> On Tue, 25 Oct 2005 08:51:59 +1000, Russell Smith <mr-russ@pws.com.au> wrote:
> > Looks to mee like Daylight Savings has conveniently started.
>
> But the elapsed time for those results is only 6 days, 23 hours.
>
> That's changed since v7.4.7
I think this item in the 8.1 Release Notes might be relevant:
* Add an internal day field to INTERVAL so a one day interval can be
distinguished from a 24 hour interval (Michael Glaesemann)
Days that contain a daylight savings time adjustment are not 24 hours,
but typically 23 or 25 hours. This change allows days (not fixed
24-hour periods) to be added to dates who's result includes a daylight
savings time adjustment period. Therefore, while in previous releases
1 day and 24 hours were interchangeable interval values, in this
release they are treated differently, e.g.
'2005-05-03 00:00:00 EST' + '1 day' = '2005-05-04 00:00:00-04'
'2005-05-03 00:00:00 EST' + '24 hours' = '2005-05-04 01:00:00-04'
Here's an example and the results from 7.4.9, 8.0.4, and 8.1beta4:
\x
SET TimeZone TO 'Australia/NSW';
SELECT version(), now(), now() + interval'1 week', now() + interval'168 hours';
-[ RECORD 1 ]-----------------------------------------------------------------------
version | PostgreSQL 7.4.9 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
now | 2005-10-25 13:35:43.663169+10
?column? | 2005-11-01 14:35:43.663169+11
?column? | 2005-11-01 14:35:43.663169+11
-[ RECORD 1 ]-----------------------------------------------------------------------
version | PostgreSQL 8.0.4 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
now | 2005-10-25 13:35:45.459081+10
?column? | 2005-11-01 14:35:45.459081+11
?column? | 2005-11-01 14:35:45.459081+11
-[ RECORD 1 ]--------------------------------------------------------------------------
version | PostgreSQL 8.1beta4 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
now | 2005-10-25 13:35:47.104595+10
?column? | 2005-11-01 13:35:47.104595+11
?column? | 2005-11-01 14:35:47.104595+11
--
Michael Fuhr
On Mon, Oct 24, 2005 at 11:21:52PM -0400, Tom Lane wrote:
> Klint Gore <kg@kgb.une.edu.au> writes:
> > That's changed since v7.4.7
>
> Yup. '1 week' = '7 days' which is no longer the same as 7*24 hours.
> In particular, as of 8.1 local noon plus one day is still local noon,
> even if there was a DST change in between. Adding 24 hours, on the
> other hand, might give 11am or 1pm.
Should 24 hours be the same as 1 * 24 hours? The latter appears
to be equal to 1 day, not 24 hours:
test=> SELECT '2005-10-29 12:00:00-06'::timestamptz + '24 hours'::interval;
?column?
------------------------
2005-10-30 11:00:00-07
(1 row)
test=> SELECT '2005-10-29 12:00:00-06'::timestamptz + 1 * '24 hours'::interval;
?column?
------------------------
2005-10-30 12:00:00-07
(1 row)
test=> SELECT '2005-10-29 12:00:00-06'::timestamptz + '1 day'::interval;
?column?
------------------------
2005-10-30 12:00:00-07
(1 row)
--
Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> Should 24 hours be the same as 1 * 24 hours?
Yes, I would think so.
> The latter appears to be equal to 1 day, not 24 hours:
Urgh. I think this is a serious thinko in Michael Glaesemann's rewrite
of interval_mul. The application of interval_justify_hours is utterly
wrong ... and in fact, I'm not sure it should be applied in any of the
three functions that currently call it. I don't mind the user deciding
he'd like to flatten '24 hours' to '1 day' but the basic arithmetic
functions for intervals have no business doing that.
Comments?
regards, tom lane
Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Should 24 hours be the same as 1 * 24 hours?
>
> Yes, I would think so.
>
> > The latter appears to be equal to 1 day, not 24 hours:
>
> Urgh. I think this is a serious thinko in Michael Glaesemann's rewrite
> of interval_mul. The application of interval_justify_hours is utterly
> wrong ... and in fact, I'm not sure it should be applied in any of the
> three functions that currently call it. I don't mind the user deciding
> he'd like to flatten '24 hours' to '1 day' but the basic arithmetic
> functions for intervals have no business doing that.
The reason interval_justify_hours is called by interval multiplication
is so multipling an interval '2 days, 4 hours' by 10 doesn't return
values like 20 days, 40 hours, etc, but instead something like '21 days,
16 hours', which seems more reasonable.
For a query like:
test=> SELECT '2005-10-29 12:00:00-06'::timestamptz + 1 * '24 hours'::interval;
the interval multiplication really has no fixed timestamp associated
with it, so it seems good to adjust the output. That result is _then_
added to an interval, and this is where the problem happens, where this
computes to 1 day:
test=> select 1 * '24 hours'::interval;
?column?
----------
1 day
(1 row)
I would say if intervals are going to be added to timestamps, we
probably don't want the adjustment, but if they are going to be used on
their own, it seems the adjustment makes sense. One solution would be
to suggest the use of interval_justify_hours() in the documentation for
interval multiplication, and prevent the justification from happening
automatically.
--
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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Urgh. I think this is a serious thinko in Michael Glaesemann's rewrite
>> of interval_mul.
> The reason interval_justify_hours is called by interval multiplication
> is so multipling an interval '2 days, 4 hours' by 10 doesn't return
> values like 20 days, 40 hours, etc, but instead something like '21 days,
> 16 hours', which seems more reasonable.
That's utterly WRONG, though. The entire *point* of the 8.1 change is
that days and hours are incommensurable. We are forced to down-convert
in some cases --- for example, we can't compute a useful result for
"0.5 * '1 day'" without imputing "12 hours" as the equivalent of 0.5 day
--- but we never have to and never should up-convert, except by explicit
user command ... which is what the justify_hours function is for.
> One solution would be
> to suggest the use of interval_justify_hours() in the documentation for
> interval multiplication, and prevent the justification from happening
> automatically.
Exactly. Forcing the justification to happen is broken, because there's
no way to get the other behavior.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Urgh. I think this is a serious thinko in Michael Glaesemann's rewrite > >> of interval_mul. > > > The reason interval_justify_hours is called by interval multiplication > > is so multipling an interval '2 days, 4 hours' by 10 doesn't return > > values like 20 days, 40 hours, etc, but instead something like '21 days, > > 16 hours', which seems more reasonable. > > That's utterly WRONG, though. The entire *point* of the 8.1 change is > that days and hours are incommensurable. We are forced to down-convert > in some cases --- for example, we can't compute a useful result for > "0.5 * '1 day'" without imputing "12 hours" as the equivalent of 0.5 day > --- but we never have to and never should up-convert, except by explicit > user command ... which is what the justify_hours function is for. 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? > > One solution would be > > to suggest the use of interval_justify_hours() in the documentation for > > interval multiplication, and prevent the justification from happening > > automatically. > > Exactly. Forcing the justification to happen is broken, because there's > no way to get the other behavior. If that's what people want, it is fine by me. -- 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
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
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
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
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 : :
+---------------------------------------+-----------------+
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
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 : :
+---------------------------------------+-----------------+
[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 : :
+---------------------------------------+-----------------+
>> 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...
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
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
On Wed, 26 Oct 2005 00:44:50 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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. I think this is what I was getting at. In my timezone 'Australia/NSW', we have daylight savings. Is that used any way when the calculation happens or the result is displayed? In the examples we've been using, does anything change if the -05 and -04 are changed to timezones (EDT/PST/...)? 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 : : +---------------------------------------+-----------------+
Klint Gore <kg@kgb.une.edu.au> writes:
> I think this is what I was getting at. In my timezone 'Australia/NSW',
> we have daylight savings. Is that used any way when the calculation
> happens or the result is displayed?
Absolutely. The examples Bruce and I have been throwing around assume
US Eastern timezone, because that's where we live, but the code should
adapt to your local zone rules wherever you are.
regards, tom lane
John R Pierce <pierce@hogranch.com> writes:
> heh. as an aside... the original reason I got ON this and the jdbc
> list was due to an issue we had with an inhouse java+pgsql program when
> it was deployed in Singapore... SGT wasn't recognized, then I
> discovered that China (another later deployment location) uses CST which
> collides with Central Standard Time and convinced the developers they
> HAD to use numeric times.
Yeah, that is a bee in my bonnet too. We fixed a bunch of issues around
SET TIMEZONE by adopting the zic code, but there's still a hardwired
list of timezone names (or more accurately, GMT-offset names) embedded
in datetime.c for purposes of parsing datetime input strings. We need
to make that list user-configurable. The existing "australian_timezones"
setting is just a half-baked attempt at that.
> TIMEZONES SUCK!
Sir Arthur Clarke (he who invented the idea of geosynchronous
communications satellites) has written of a far future where everyone
on earth thinks in UTC time. Works for me ;-) but I don't suppose the
Postgres codebase will live that long.
regards, tom lane
On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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".
Timestamp subtraction is not age(). Subtraction should be precise, age()
is allowed to justify.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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. Um, what? Under what conditions is it permissable for simple arithmetic on (only) timestamptz values (which may have originated in different timezones neither of which is the current one) to be dependent on the current timezone setting? -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On 2005-10-26, Klint Gore <kg@kgb.une.edu.au> wrote:
> [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?
Absolutely.
> 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?
Yes. The difference between those two times is exactly the same whatever
timezone the person running the query is in, because they are _completely
specified_ by the input. That difference is 25 hours. There are no
circumstances in which that difference could ever be 24 or 26 hours
regardless of what timezone the user is in. (The only way in which the
timezone makes a difference is that a user in US/Eastern might, under
some circumstances, wish to regard that time period as '1 day' rather
than 25 hours; no user in any other timezone would do so. Since the
conversion from '25 hours' to '1 day' loses information, it should not
happen.)
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
John R Pierce 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).
>
> 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...
Yes, I ran this in EST5EDT time, so the answer is correct for that
timezone.
--
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
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 <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
>
--
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
Thanks for all this discussion, fixing, etc. I'm currently having
"issues" getting postgres' date/time functions to do what I want. You
have obviously spotted some of the reasons for this.
Many of my issues disappear when I use 8.1, but it's still in beta. Is
it safe for me to use 8.1 in production, if I don't use any of the new
features? If not, would it be possible to backport these date/time
changes to 8.0 so that my program can operate correctly before 8.1 is
finished beta? I can do this backporting myself if someone can point me
to the relevant files. (I'm sure I can work it out myself if necessary,
but I'm a little busy at the moment).
BTW, Postgres' date functions are *great* except for these minor
problems. The best I've ever used.
Thanks!
Nicholas
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'?
>
>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 : :
>+---------------------------------------+-----------------+
>
>
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.
regards, tom lane
Andrew - Supernews <andrew+nonews@supernews.com> writes:
> Um, what? Under what conditions is it permissable for simple arithmetic on
> (only) timestamptz values (which may have originated in different timezones
> neither of which is the current one) to be dependent on the current timezone
> setting?
Timestamp subtraction will give different answers depending on whether
there's a DST adjustment in between.
regression=# select '2005-10-31'::timestamptz - '2005-10-28'::timestamptz;
?column?
-----------------
3 days 01:00:00
(1 row)
regression=# set timezone = 'Japan';
SET
regression=# select '2005-10-31'::timestamptz - '2005-10-28'::timestamptz;
?column?
----------
3 days
(1 row)
BTW, if we were doing subtraction symbolically as I think we should,
these *would* give the same answer, ie, '3 days' in both cases. Care to
rethink your opposition to that idea?
regards, tom lane
On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andrew - Supernews <andrew+nonews@supernews.com> writes: >> Um, what? Under what conditions is it permissable for simple arithmetic on >> (only) timestamptz values (which may have originated in different timezones >> neither of which is the current one) to be dependent on the current timezone >> setting? > > Timestamp subtraction will give different answers depending on whether > there's a DST adjustment in between. no, it _WILL NOT_. In your example, the result is different between timezones because the _input data_ is different. '2005-10-31'::timestamptz designates a different time in US/Eastern than it does in Japan, or UTC, or whatever. Or to put it in terms of the code: in 8.0, timestamptz_in is stable rather than immutable (since it depends on timezone), while timestamptz_mi is immutable (result depends only on the input values). In 8.0, I'm guaranteed that for timestamptz values, a+(b-a) = b in all cases regardless of timezone. 8.1 has broken that. > BTW, if we were doing subtraction symbolically as I think we should, > these *would* give the same answer, ie, '3 days' in both cases. Care to > rethink your opposition to that idea? No. If you want symbolic subtraction, that's what age() is for. If you break the subtraction operator, you leave no means of doing _accurate_ subtraction. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews wrote: > On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Andrew - Supernews <andrew+nonews@supernews.com> writes: > >> Um, what? Under what conditions is it permissable for simple arithmetic on > >> (only) timestamptz values (which may have originated in different timezones > >> neither of which is the current one) to be dependent on the current timezone > >> setting? > > > > Timestamp subtraction will give different answers depending on whether > > there's a DST adjustment in between. > > no, it _WILL NOT_. > > In your example, the result is different between timezones because the > _input data_ is different. '2005-10-31'::timestamptz designates a different > time in US/Eastern than it does in Japan, or UTC, or whatever. > > Or to put it in terms of the code: in 8.0, timestamptz_in is stable > rather than immutable (since it depends on timezone), while timestamptz_mi > is immutable (result depends only on the input values). > > In 8.0, I'm guaranteed that for timestamptz values, a+(b-a) = b in all > cases regardless of timezone. 8.1 has broken that. Yes, that is a valid problem, but what solution do we have at this stage? -- 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