Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 84934475BF9 for ; Thu, 21 Nov 2002 10:27:06 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 84238475AFA for ; Thu, 21 Nov 2002 10:27:05 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id gALFQxsX011966; Thu, 21 Nov 2002 10:26:59 -0500 (EST) To: Richard Huxton Cc: thrasher@fibers.upc.es, pgsql-sql@postgresql.org, Thomas Lockhart Subject: Re: Date trunc in UTC In-reply-to: <200211211015.42013.dev@archonet.com> References: <3DDB5991.9060706@fibers.upc.es> <200211201306.47345.dev@archonet.com> <3DDBAD02.3090406@electronic-group.com> <200211211015.42013.dev@archonet.com> Comments: In-reply-to Richard Huxton message dated "Thu, 21 Nov 2002 10:15:41 +0000" Date: Thu, 21 Nov 2002 10:26:59 -0500 Message-ID: <11965.1037892419@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/331 X-Sequence-Number: 10473 Richard Huxton writes: > Hmm - good point. You can revert to the client default but not to the > previous value. I don't know of any way to read these SET values > either - a quick poke through pg_proc didn't show anything likely. In 7.3 you can use current_setting() and set_config() to access SHOW/SET functionality. However, I agree with your suggestion of AT TIME ZONE to rotate a timestamp into a target timezone, rather than mucking with the TimeZone setting. BTW, Thomas: is AT TIME ZONE supposed to accept timestamp-without-timezone input? If so, what's it supposed to do with it? The current behavior seems unintuitive to say the least: regression=# select now(); now ------------------------------- 2002-11-21 10:19:14.591001-05 (1 row) regression=# select now() at time zone 'UTC'; timezone ---------------------------- 2002-11-21 15:19:18.588279 (1 row) regression=# select localtimestamp; timestamp ---------------------------- 2002-11-21 10:19:22.629865 (1 row) regression=# select localtimestamp at time zone 'UTC'; timezone ------------------------------- 2002-11-21 05:19:26.178861-05 (1 row) It seems to me that the last case should give either an error or 2002-11-21 15:19:26.178861 (ie, assume that the timestamp without time zone is in my TimeZone zone). In any case, surely the result should be of type timestamp WITHOUT time zone? regards, tom lane