Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Tom Lane wrote: > Bruce Momjian 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
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Bruce Momjian 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
Re: [BUGS] BUG #1985: cannot insert Chinese character into a table
How can I search the Unicode byte code seqence? There is a Unified CJK Ideographs in the following website: http://www.unicode.org/charts/ I chose some characters within that pdf file but none of them can be inserted into utf8 encoded table. I took another screenshot from latest 8.1 beta 4: http://www.tong.cc/pgsql8.1beta3_2.png Tell me more if I can help. Jeff Tong wrote: The following bug has been logged online: Bug reference: 1985 Logged by: Jeff Tong Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1beta3 Operating system: Windows XP Description:cannot insert Chinese character into a table encoded with UTF8 Details: I am a traditional Chinese user in Hong Kong. 8.1beta3 for WinXP still cannot let me insert Chinese character into a table encoded with UTF8. I think it is very importance issue with CJK users who need Unicode encoded tables. Here is a screenshot I took from command prompt: http://www.tong.cc/pgsql8.1beta3.png Strange. We thought we fixed all the UTF-8/Chinese issues in 8.1. Can you tell us the Unicode byte code sequence that is being rejected? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1997: Grammar error in phpPgAdmin 3.1
The following bug has been logged online: Bug reference: 1997 Logged by: Patrick Kik Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: SLES 9 Description:Grammar error in phpPgAdmin 3.1 Details: In Dutch, after deleting a column the text "Kolom verwijdert" appears. This should be "Kolom verwijderd". ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Tom Lane wrote: > Bruce Momjian 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
[BUGS] BUG #1998: transaction locks parent record when it shouldn't
The following bug has been logged online: Bug reference: 1998 Logged by: Dmitry Panov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.4 Operating system: Linux Description:transaction locks parent record when it shouldn't Details: Hi, I discovered that inserting a child record locks parent record so that if another translation tries to insert another child record which references the same parent. This can be illustrated by a simple test case: create table testparent (id integer, constraint testparent_pk primary key (id)); create table testchild (parent_id integer, a varchar, constraint testchild_fk foreign key (parent_id) references testparent(id) on delete cascade); insert into testparent values (1); insert into testparent values (2); then run 2 transactions in parallel: TRANSATION 1: begin; insert into testchild values (1, '1'); TRANSACTION 2: begin; insert into testchild values (2, '22'); TRANSACTION 1: insert into testchild values (2, '2'); TRANSACTION 2: insert into testchild values (2, '22'); I believe it's not necessary to lock the parent record to maintain the read commited isolation level. This test case works fine in Oracle and Mysql 4.1/InnoDB. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1998: transaction locks parent record when it shouldn't
Dmitry Panov wrote: > I discovered that inserting a child record locks parent record so that if > another translation tries to insert another child record which references > the same parent. This is fixed in 8.1. -- Alvaro Herrera Architect, http://www.EnterpriseDB.com "Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3
Hi, I have a query that throws error "RIGHT JOIN is only supported with merge-joinable join conditions". This should allow it to be reproduce. create table table1 (t1id integer not null, extension integer not null); create table table2 (t1id integer not null, t3id integer not null, original integer not null, replacement integer not null); create table table3 (t3id integer not null); create unique index table3ix1 on table3 (t3id); insert into table3 select * from generate_series(1,1); select count(table3.*) from table1 inner join table2 on table1.t1id = table2.t1id and table1.extension in (table2.original, table2.replacement) left outer join table3 on table2.t3id = table3.t3id and table1.extension in (table2.replacement); I am on PostgreSQL 8.1 beta3 under Windows XP Service Pack 2. Thanks Jean-Pierre Pelletier e-djuster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Bruce Momjian 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 1 * '100 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
Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3
"Jean-Pierre Pelletier" <[EMAIL PROTECTED]> writes: > I have a query that throws error "RIGHT JOIN is only supported with > merge-joinable join conditions". Wow, that's a goodie ... seems to fail all the way back to 7.2 ... thanks for the report. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] Variable not found in subplan target lists, PostgreSQL 8.1 beta3
Hi, I have a query that throws error "Variable not found in subplan target lists". This should allow it to be reproduce. create temporary table table1 ( col1 integer not null, col2 integer not null ); create temporary table table2 (); create or replace function udftable1row(integer, integer) returns table1 AS $$ select $1, $2; $$ language sql immutable; create or replace function udf(table1) returns void as $$ $$ language sql immutable; -- This throws "Variable not found in subplan target lists" select udf(t1) from udftable1Row(1,2) t1 cross join table2; -- Now that we have Row constructor, I can get rid of my function udftable1row() and this works ok select udf(t1) from (select (cast(row(1,2) as table1)).*) t1 cross join table2; I am on PostgreSQL 8.1 beta3 under Windows XP Service Pack 2. Thanks, Jean-Pierre Pelletier e-djuster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3
"Jean-Pierre Pelletier" <[EMAIL PROTECTED]> writes:
> select
>count(table3.*)
> from
>table1
>inner join table2
>on table1.t1id = table2.t1id
>and table1.extension in (table2.original, table2.replacement)
>left outer join table3
>on table2.t3id = table3.t3id
>and table1.extension in (table2.replacement);
I've applied the attached patch (for 8.1, variants as needed for back
branches) to fix this failure.
BTW, I think the reason nobody saw this before is that using a condition
on table1 vs table2 in the outer-join condition for table3 is a bit, er,
weird. Are you sure that the original query will do what you really
wanted?
But anyway, many thanks for the test case!
regards, tom lane
Index: joinpath.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.96
diff -c -r1.96 joinpath.c
*** joinpath.c 15 Oct 2005 02:49:20 - 1.96
--- joinpath.c 25 Oct 2005 19:52:54 -
***
*** 795,800
--- 795,801
{
List *result_list = NIL;
boolisouterjoin = IS_OUTER_JOIN(jointype);
+ boolhave_nonmergeable_joinclause = false;
ListCell *l;
foreach(l, restrictlist)
***
*** 803,844
/*
* If processing an outer join, only use its own join clauses
in the
!* merge. For inner joins we need not be so picky.
!*
!* Furthermore, if it is a right/full join then *all* the
explicit join
!* clauses must be mergejoinable, else the executor will fail.
If we
!* are asked for a right join then just return NIL to indicate
no
!* mergejoin is possible (we can handle it as a left join
instead). If
!* we are asked for a full join then emit an error, because
there is
!* no fallback.
*/
! if (isouterjoin)
! {
! if (restrictinfo->is_pushed_down)
! continue;
! switch (jointype)
! {
! case JOIN_RIGHT:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator
== InvalidOid)
! return NIL; /* not
mergejoinable */
! break;
! case JOIN_FULL:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator
== InvalidOid)
! ereport(ERROR,
!
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
!errmsg("FULL
JOIN is only supported with merge-joinable join conditions")));
! break;
! default:
! /* otherwise, it's OK to have
nonmergeable join quals */
! break;
! }
! }
if (!restrictinfo->can_join ||
restrictinfo->mergejoinoperator == InvalidOid)
continue; /* not mergejoinable */
/*
* Check if clause is usable with these input rels. All the
vars
--- 804,822
/*
* If processing an outer join, only use its own join clauses
in the
!* merge. For inner joins we can use pushed-down clauses too.
!* (Note: we don't set have_nonmergeable_joinclause here because
!* pushed-down clauses will become otherquals not joinquals.)
*/
! if (isouterjoin && restrictinfo->is_pushed_down)
! continue;
if (!restrictinfo->can_join ||
restrictinfo->mergejoinoperator == InvalidOid)
+ {
+ have_nonmergeable_joinclause = true;
continue; /* not mergejoinable */
+ }
/*
* Check if clause is usable with these input rels. All the
vars
***
*** 856,865
--- 834,870
/* lefthand side is inner */
}
else
+ {
+ have_nonmergeable_joinclause = true;
continue; /* no good for these
input relations */
+ }
result_list = lcons(restrictinf
Re: [BUGS] Variable not found in subplan target lists, PostgreSQL 8.1 beta3
"Jean-Pierre Pelletier" <[EMAIL PROTECTED]> writes: > I have a query that throws error "Variable not found in subplan target > lists". > This should allow it to be reproduce. This seems to be fixed already in beta4. But thanks for the report! Relevant fix is: 2005-10-19 13:31 tgl * src/backend/optimizer/plan/createplan.c: Fix oversight in recent changes to enable the 'physical tlist' optimization for subquery and function scan nodes: we can't just do it unconditionally, we still have to check whether there is any need for a whole-row Var. I had been thinking that these node types couldn't have any system columns, which is true, but that loop is also checking for attno zero, ie, whole-row Var. Fix comment to not be so misleading. Per test case from Richard Huxton. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3
Thanks for the speedy fix.
I agree that this is not a typical query, in it Table2.t3id and Table3.t3id
would always join
(a foreing key constraint ensure that) but columns from Table3 should
sometimes be excluded which is taken care by "table1.extension in
(table2.replacement)".
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, October 25, 2005 4:34 PM
Subject: Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join
conditions, PostgreSQL 8.1 beta3
"Jean-Pierre Pelletier" <[EMAIL PROTECTED]> writes:
select
count(table3.*)
from
table1
inner join table2
on table1.t1id = table2.t1id
and table1.extension in (table2.original, table2.replacement)
left outer join table3
on table2.t3id = table3.t3id
and table1.extension in (table2.replacement);
I've applied the attached patch (for 8.1, variants as needed for back
branches) to fix this failure.
BTW, I think the reason nobody saw this before is that using a condition
on table1 vs table2 in the outer-join condition for table3 is a bit, er,
weird. Are you sure that the original query will do what you really
wanted?
But anyway, many thanks for the test case!
regards, tom lane
Index: joinpath.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.96
diff -c -r1.96 joinpath.c
*** joinpath.c 15 Oct 2005 02:49:20 - 1.96
--- joinpath.c 25 Oct 2005 19:52:54 -
***
*** 795,800
--- 795,801
{
List*result_list = NIL;
bool isouterjoin = IS_OUTER_JOIN(jointype);
+ bool have_nonmergeable_joinclause = false;
ListCell *l;
foreach(l, restrictlist)
***
*** 803,844
/*
* If processing an outer join, only use its own join clauses in the
! * merge. For inner joins we need not be so picky.
! *
! * Furthermore, if it is a right/full join then *all* the explicit join
! * clauses must be mergejoinable, else the executor will fail. If we
! * are asked for a right join then just return NIL to indicate no
! * mergejoin is possible (we can handle it as a left join instead). If
! * we are asked for a full join then emit an error, because there is
! * no fallback.
*/
! if (isouterjoin)
! {
! if (restrictinfo->is_pushed_down)
! continue;
! switch (jointype)
! {
! case JOIN_RIGHT:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator == InvalidOid)
! return NIL; /* not mergejoinable */
! break;
! case JOIN_FULL:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator == InvalidOid)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("FULL JOIN is only supported with merge-joinable join
conditions")));
! break;
! default:
! /* otherwise, it's OK to have nonmergeable join quals */
! break;
! }
! }
if (!restrictinfo->can_join ||
restrictinfo->mergejoinoperator == InvalidOid)
continue; /* not mergejoinable */
/*
* Check if clause is usable with these input rels. All the vars
--- 804,822
/*
* If processing an outer join, only use its own join clauses in the
! * merge. For inner joins we can use pushed-down clauses too.
! * (Note: we don't set have_nonmergeable_joinclause here because
! * pushed-down clauses will become otherquals not joinquals.)
*/
! if (isouterjoin && restrictinfo->is_pushed_down)
! continue;
if (!restrictinfo->can_join ||
restrictinfo->mergejoinoperator == InvalidOid)
+ {
+ have_nonmergeable_joinclause = true;
continue; /* not mergejoinable */
+ }
/*
* Check if clause is usable with these input rels. All the vars
***
*** 856,865
--- 834,870
/* lefthand side is inner */
}
else
+ {
+ have_nonmergeable_joinclause = true;
continue; /* no good for these input relations */
+ }
result_list = lcons(restrictinfo, result_list);
}
+ /*
+ * If it is a right/full join then *all* the explicit join clauses must
be
+ * mergejoinable, else the executor will fail. If we are asked for a
right
+ * join then just return NIL to indicate no mergejoin is possible (we can
+ * handle it as a left join instead). If we are asked for a full join
then
+ * emit an error, because there is no fallback.
+ */
+ if (have_nonmergeable_joinclause)
+ {
+ switch (jointype)
+ {
+ case JOIN_RIGHT:
+ return NIL; /* not mergejoinable */
+ case JOIN_FULL:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("FULL JOIN is only supported with merge-joinable join
conditions")));
+ break;
+ default:
+ /* otherwise, it's OK to have nonmergeable join quals */
+ break;
+ }
+ }
+
return result_list;
}
---(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 2: Don't 'kill -9'
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
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'?
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 : [EMAIL PROTECTED] : 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
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Nicholas Vinen wrote:
>
> 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
Not really, it is "beta".
> 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).
Backporting is probably more dangerous than using 8.1 beta, unless you
are very careful and skillful. src/backend/utils/adt is where most of
the stuff lives.
> BTW, Postgres' date functions are *great* except for these minor
> problems. The best I've ever used.
It is really up to you how much risk you want to take for the features
you want.
---
>
>
> Thanks!
> Nicholas
>
>
> 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'?
> >
> >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 : [EMAIL PROTECTED] : 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| 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: [BUGS] BUG #1993: Adding/subtracting negative time intervals
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
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian
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 : [EMAIL PROTECTED] : 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
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
[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
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 : [EMAIL PROTECTED] : 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
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Klint Gore <[EMAIL PROTECTED]> writes:
> On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian
> 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
John R Pierce <[EMAIL PROTECTED]> 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
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Klint Gore <[EMAIL PROTECTED]> 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 ---(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: [BUGS] BUG #1993: Adding/subtracting negative time intervals
On Wed, 26 Oct 2005 00:44:50 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > John R Pierce <[EMAIL PROTECTED]> 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 : [EMAIL PROTECTED] : 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
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
John R Pierce <[EMAIL PROTECTED]> 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 ---(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: [BUGS] BUG #1993: Adding/subtracting negative time intervals
On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote:
> Klint Gore <[EMAIL PROTECTED]> writes:
>> On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian
>> 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
