SEO

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=#

---------------------------(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

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


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

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 : :
+---------------------------------------+-----------------+

---------------------------(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:
> 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

---------------------------(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

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

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

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

---------------------------(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

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

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

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

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

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

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

---------------------------(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

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

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