Re: [BUGS] Problem With Case Statement and Aggregate Functions
On Fri, 11 May 2007 14:47:04 +1000, Andrew Shea <[EMAIL PROTECTED]> wrote:
> The following works as expected:
>
> select (SELECT CASE WHEN (1=2) THEN 0 ELSE sum(count) END) from (
> select 1 as count union select 2 union select 3
> ) as "temp";
>
> The result is "6".
>
> The following also works as expected:
>
> select count(*) from (
> select 1 as count union select 2 union select 3
> ) as "temp";
>
> The results is "3".
>
>
> However the following code doesn't work even though it is very similar
> to the first query (that is, and aggregate function within a case
> statement):
>
> select (SELECT CASE WHEN (1=2) THEN 0 ELSE COUNT(*) END) from (
> select 1 as count union select 2 union select 3
> ) as "temp";
>
> The result is three rows of "1".
>
> So why does the "count" aggregate function within a case statement
> execute on a per row basis whereas the "sum" aggregate within a case
> statement will first group the rows?
The * from count(*) binds to the inner most select where it can draw
data.
Think of it like
select
(select count('1') from bar)
>from foo
foo and bar have nothing to do with each other so it turns into for each
row in foo count the number of records in bar.
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 #3277: error occurs between different versions
On Mon, 14 May 2007 09:44:05 +0100, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > Nilay Ceter wrote: > > The command line: > > > > select seri_no, ad as baslik, kull_ref as Kullanıcı_Referansi, sorumlu > > from onay,dokuman where p_id='yonetici' and onay.dokuman_id=dokuman.seri_no > > and onay_durum.seri_no=seri_no and onay_durum.p_id='yonetici' and > > (dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or dokuman.durum=7) > > and onay_durum.durum=0 and seri_no>0 > > > > > > was working properly in Postgre SQL 8.0 but in version Postgre SQL 8.2 the > > second "and" gives an error. > > And it is : > > > > " Project dym.exe raised exception class EPSQLDatabaseError with message > > 'PostgreSQL Error Code : (1) > > ERROR: missing FROM-clause entry for table "onay_durum" at character 152' > > .Process stopped.Use Step or Run to continue. " > > I don't understand how that query could have worked on PostgreSQL 8.0 > either. There's no table or alias with name "onay_durum" in the FROM clause. > > Are you sure this is the exactly same query you tried on PostgreSQL 8.0? Didn't the default in postgresql.conf for add_missing_from change in 8.1? 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 #3277: error occurs between different versions
I'm suprised it worked in 8.0. You should have got column reference is ambiguous errors on seri_no since it appears in both dokuman and onay_durum and there are 3 places where you don't specify which one you want. (Someone who knows more than I do might comment on what happens to unqualfied field names when the add missing from brings in another field with the same name?) try fully qualifying where everything comes from. select ??.seri_no, ???.ad as baslik, .kull_ref as Kullanıcı_Referansi, ?.sorumlu >from onay, dokuman, onay_durum where onay_durum.p_id='yonetici' and onay.dokuman_id=dokuman.seri_no and onay_durum.seri_no=??.seri_no and onay_durum.p_id='yonetici' and ( dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or dokuman.durum=7) and onay_durum.durum=0 and ???.seri_no>0 klint. On Mon, 21 May 2007 13:15:01 +0300, "nilay çeter" <[EMAIL PROTECTED]> wrote: > Yes,it is the same query, and had worked on PostgreSQL8.0 ,but although I > had already added "onay_durum" to from clause and it did not work on 8.2,I > have changed the 'add_missing_from = off ' to on in conf. file but > it didn't work too. > I have no idea about what to do:( > > 2007/5/14, Klint Gore <[EMAIL PROTECTED]>: > > > > On Mon, 14 May 2007 09:44:05 +0100, Heikki Linnakangas < > > [EMAIL PROTECTED]> wrote: > > > Nilay Ceter wrote: > > > > The command line: > > > > > > > > select seri_no, ad as baslik, kull_ref as Kullanıcı_Referansi, sorumlu > > > > from onay,dokuman where p_id='yonetici' and > > onay.dokuman_id=dokuman.seri_no > > > > and onay_durum.seri_no=seri_no and onay_durum.p_id='yonetici' and > > > > (dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or > > dokuman.durum=7) > > > > and onay_durum.durum=0 and seri_no>0 > > > > > > > > > > > > was working properly in Postgre SQL 8.0 but in version Postgre SQL > > > > 8.2the > > > > second "and" gives an error. > > > > And it is : > > > > > > > > " Project dym.exe raised exception class EPSQLDatabaseError with > > message > > > > 'PostgreSQL Error Code : (1) > > > > ERROR: missing FROM-clause entry for table "onay_durum" at character > > 152' > > > > .Process stopped.Use Step or Run to continue. " > > > > > > I don't understand how that query could have worked on PostgreSQL 8.0 > > > either. There's no table or alias with name "onay_durum" in the FROM > > clause. > > > > > > Are you sure this is the exactly same query you tried on PostgreSQL 8.0? > > > > Didn't the default in postgresql.conf for add_missing_from change in 8.1? > > > > 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] variable not found in subplan target list?
I've got the message "ERROR: variable not found in subplan target lists" when I have a union between 2 views. Both selects from the views run without the union. Same result using intersect and except. Same result using any of them with the all qualifier. Explain of the union gives the error, explain of each individual select gives a result. The view definitions contain unions and cross joins. The error still occurs if I simplify the selects down to select 'abc' from view1 union select 'cba' from view2 "PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC 2.96" redhat 7.2 (2.4.7-10) 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] variable not found in subplan target list? + test case
On Sun, 23 Nov 2003 22:48:17 -0800 (PST), Stephan Szabo <[EMAIL PROTECTED]> wrote:
> On Mon, 24 Nov 2003, Klint Gore wrote:
>
> > I've got the message "ERROR: variable not found in subplan target lists"
> > when I have a union between 2 views. Both selects from the views run
> > without the union. Same result using intersect and except. Same result
> > using any of them with the all qualifier. Explain of the union gives
> > the error, explain of each individual select gives a result.
> >
> > The view definitions contain unions and cross joins.
> >
> > The error still occurs if I simplify the selects down to
> >
> >select 'abc' from view1
> >union
> >select 'cba' from view2
> >
> > "PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC 2.96"
> > redhat 7.2 (2.4.7-10)
>
> Can you send a test case with table and view definitions, it's hard to
> generate one from just the information above.
ok. the last select generates the error. I used pgadmin3 and did
create database and only gave it a name. Then opened a sql window and
ran the creates. Then ran the final select and the error happened. I
didnt put any data in the tables.
This test I ran on w2k(server) sp4, using "PostgreSQL 7.4 on
i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927 (prerelease)"
Same result on the linux from first post.
SETUP ---
CREATE TABLE "component_params" (
"soc_code" character varying(7) NOT NULL,
"form_name" character varying(64) NOT NULL,
"com_name" character varying(64) NOT NULL,
"com_label" character varying(64),
"com_data_type" character varying(1) DEFAULT 'V',
"com_type" character varying(3),
"com_enabled" character varying(1) DEFAULT 'Y',
"com_enable_on_add" character varying(1) DEFAULT 'Y',
"com_visible" character varying(1) DEFAULT 'Y',
"com_default_value" character varying(64),
"com_min_value" numeric(12,4) DEFAULT 0,
"com_max_value" numeric(12,4) DEFAULT 0,
"com_min_length" integer DEFAULT 0,
"com_max_length" integer DEFAULT 0,
"com_case" character varying(1) DEFAULT 'U',
"com_unique" boolean DEFAULT 'f'::bool,
"com_align" character varying(1) DEFAULT 'L',
"com_top" integer DEFAULT 0,
"com_left" integer DEFAULT 0,
"com_width" integer DEFAULT 0,
"com_hint_level" integer DEFAULT 1,
"com_reqd" character varying(1) DEFAULT 'N',
"com_pair_name" character varying(64),
"com_pair_type" character varying(1),
"com_pair_null_values" character varying(32),
"com_combo_size" integer,
"com_hint" character varying(255),
"com_label_hint" character varying(255),
"com_default_sql" text,
"com_combo_sql" text,
"com_reqd_sql" character varying(255),
"com_help" text,
"create_method" integer,
"create_date" timestamp(0) with time zone,
"create_user_id" character varying(8),
"last_upd_method" integer,
"last_upd_date" timestamp(0) with time zone,
"last_upd_user_id" character varying(8),
Constraint "component_params_pkey" Primary Key ("soc_code", "form_name",
"com_name")
);
CREATE TABLE "societies" (
"soc_code" character varying(7) NOT NULL,
"soc_short_name" character varying(32),
"soc_package_list" character varying,
"create_date" timestamp with time zone,
"create_user_id" character varying(8),
"last_upd_date" timestamp with time zone,
"last_upd_user_id" character varying(8),
Constraint "societies_pkey" Primary Key ("soc_code")
);
create view component_params_v as
select
soc_code,
form_name,
com_name,
com_label,
com_data_type,
com_type,
com_enabled,
com_visible,
com_default_value,
com_min_value,
com_max_value,
com_min_length,
com_max_length,
com_case,
com_align,
com_top,
com_left,
com_width,
com_help,
com_reqd,
com_pair_name,
com_pair_type,
Re: [BUGS] variable not found in subplan target listS? + test case
On Tue, 25 Nov 2003 11:39:03 +1100, Klint Gore <[EMAIL PROTECTED]> wrote: > On Sun, 23 Nov 2003 22:48:17 -0800 (PST), Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Mon, 24 Nov 2003, Klint Gore wrote: > > > > > I've got the message "ERROR: variable not found in subplan target lists" Had a quick look thru the source code and saw that there are 2 very similar messages and checked that it is definitely the plural "lists". 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] variable not found in subplan target list? + test case
On Tue, 25 Nov 2003 19:02:54 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Klint Gore <[EMAIL PROTECTED]> writes: > >> Can you send a test case with table and view definitions, it's hard to > >> generate one from just the information above. > > > ok. the last select generates the error. > > Hoo, that was a fun one. Here's the patch. > > regards, tom lane > works on linux and cygwin. thanks for the fix. 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] missing messages?
I posted a message last friday lunchtime (australian time) to this list which I didnt see any notification 5 hours later. I assumed that something had gone astray and used the web page bug submit but never saw that message either. Can someone let me know what's happening with this list? klint. (it was about "create table as select..." killing all connections to the database) +---+-----+ : 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Problem installing postgresql in XP with cygwin
On Fri, 12 Nov 2004 13:28:02 +0100, [EMAIL PROTECTED] wrote: > I'm installinng postgresql in XP with cygwin. I have downloaded the last > version of cygwin today, so the software is up to date. > I have included in the download the packages cygrunsrv and cygipc, in > addition to postgres, of course. > > I have created a new user postgres with right to log on as a service. > After I have executed the previous sentence I did in cygwin: > > ipc-daemon2 --install-as-service this has been replaced with cygserver make sure the CYGWIN environment variable is set to SERVER run cygserver-config then start the cygserver service or run the cygserver in the background then try the initdb again. Can you post another message to the list with where the instructions you followed are so they can be updated? 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 8: explain analyze is your friend
Re: [BUGS] BUG #1500: child dead
If it's any help, on windows 8.0.0, I get a popup that says 'The instruction at "0x78001d90" referenced memory at "0x". The memory could not be read." 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: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1644: control structeres perfomance in pgsql
On Wed, 04 May 2005 11:52:32 +1000, Neil Conway <[EMAIL PROTECTED]> wrote: > tirny wrote: > > any code instructions > > *** > > IF FOUND AND TG_OP = 'UPDATE' THEN > > *** > > END IF; > > *** > > > > if not found plsql does't break this structure after IF FOUND and proceed > > TG_OP = \'UPDATE\', what bring control structures to: > > IF FOUND THEN > > IF TG_OP = 'UPDATE' THEN > >*** > > END IF; > > END IF; > > > > inconveniently, isn't it? > > I don't understand. Can you explain what the problem is? I think they mean that it doesn't do short-circuit evaluation. [to tirny] If so, see explanation in the documentation at the bottom of http://www.postgresql.org/docs/8.0/static/sql-expressions.html 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1698: Different behavior in UNIQUE and DISTINCT
On Mon, 13 Jun 2005 10:12:38 -0300, Mauro Delfino <[EMAIL PROTECTED]> wrote: > > > I have the these two tables: > > > CREATE TABLE table_one > > > ( field1 VARCHAR(255) ); > > > > > > CREATE TABLE table_two > > > ( field1 VARCHAR(255) UNIQUE ); > > > > > > SELECT DISTINCT field1 FROM table_one; > > > The query results 500k rows. > > > > > > INSERT INTO table_two (field1) (SELECT field1 FROM table_one); > > > This error occurs: > > > ERROR: duplicate key violates unique constraint "table_two_field1_key" > > > > > > What happened? DISTINC and UNIQUE have different algorithms to determine > > > when two strings are equal? Did you forget to put distinct in your insert select? table1.field1 is not unique so it allows duplicates in table_one. Try "select field1 from table_one group by field1 having count(*) > 1" and see if it gives you any results. If you do get results, then you need to put the distinct into the insert statement. INSERT INTO table_two (field1) (SELECT distinct field1 FROM table_one); 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1916: selection criteria from one outer join on clause applied to other joins
On Wed, 28 Sep 2005 00:34:37 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > Obviously, adding an outer join to a query which is already returning rows > > should never reduce the number of rows returned. > > I think this is a case of overenthusiastic propagation of implied > equalities. Do you know offhand if it fails in 8.0.* or earlier > branches? fails on 7.4.7 (rh9) and 8.0.0 (win) for me. 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 #1947: Enhancement Request - CONCAT() function
On Mon, 10 Oct 2005 23:45:03 -0400 (EDT), Bruce Momjian wrote: > Oracle has a tendency to keep things around forever, which is nice, but > over the years it produces a system with lots of strange features that > make the system hard to use. PostgreSQL has a tendency to remove old, > unused stuff over time to keep the system easy to understand. This is > part of the reason we have not added CONCAT, because it doesn't really > add new functionality to the vast majority of our users. > > Sure, if someone wants to write scripts that work with PostgreSQL and > Oracle EBCDIC machines, it would be nice to have, but for 99% of our > users, it is just added cruft, and we have a tendency to try to reduce > cruft rather than make 1% of people happy, especially when the > extensibility system of PostgreSQL allows users to create their own > CONCAT functions if they desire. Where does textcat fit into things? 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 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 #1956: Plpgsql top-level DECLARE does not share scope
On Thu, 13 Oct 2005 16:24:23 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > BTW, another issue here is that if we did merge the first DECLARE with > the scope of auto-declared variables, it would be a non backwards > compatible change. Right now you can do, say, > > declare found int; > > and it'll override the standard FOUND variable. If we change this then > you'd get an error. (Of course, it could be argued that that would be > a Good Thing. But it would inhibit us from adding new auto-declared > variables that are less central to the language than FOUND, because of > the risk of breaking existing code.) Could something be done using alias? eg declare x int; ... declare x alias for outer x klint. ---(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
On Tue, 25 Oct 2005 08:51:59 +1000, Russell Smith <[EMAIL PROTECTED]> 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 : [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 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
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
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
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
