Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 38E81475E20
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 04:30:41 -0500 (EST)
Received: from egwn.net (ns2.egwn.net [193.172.5.4])
	by postgresql.org (Postfix) with ESMTP id 1C6E3475DB4
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 04:30:38 -0500 (EST)
Received: from fibers.upc.es (gw01.es3.egwn.net [212.9.66.13]) (authenticated)
	by egwn.net (8.11.6/8.11.6/EGWN) with ESMTP id gB39U3o21391;
	Tue, 3 Dec 2002 10:30:09 +0100
Message-ID: <3DEC799D.10003@fibers.upc.es>
Date: Tue, 03 Dec 2002 10:30:05 +0100
From: Thrasher <thrasher@fibers.upc.es>
Organization: =?ISO-8859-15?Q?Delegaci=F3_Alumnes_FIB?=
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.1b) Gecko/20020721
X-Accept-Language: es, es-es, en-us
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Huxton <dev@archonet.com>, pgsql-sql@postgresql.org,
	Thomas Lockhart <lockhart@fourpalms.org>
Subject: Re: Date trunc in UTC
References: <3DDB5991.9060706@fibers.upc.es>
	<200211201306.47345.dev@archonet.com>
	<3DDBAD02.3090406@electronic-group.com>
	<200211211015.42013.dev@archonet.com>
	<11965.1037892419@sss.pgh.pa.us> <3DDDF4AD.8030105@fibers.upc.es>
	<23923.1037977842@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-MailScanner: Found to be clean
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/24
X-Sequence-Number: 10612

Hi all

Finally, I am using a plpgsql procedure that accomplish that in 
PostgreSQL 7.2.1. The code follows:

CREATE OR REPLACE FUNCTION utc_date_trunc (TEXT, TIMESTAMP) RETURNS 
TIMESTAMP AS '
	DECLARE
		utcts	TIMESTAMP WITHOUT TIME ZONE;
		utcdt	TIMESTAMP WITHOUT TIME ZONE;
	BEGIN
		--> First get the indicated timestamp at UTC <--
		utcts := $2 AT TIME ZONE ''UTC'';

		--> Get the date trunc <--
		utcdt := DATE_TRUNC ($1, utcts);

		--> Return the result <--
		RETURN (utcdt at time zone ''UTC'');
	END
' LANGUAGE 'plpgsql';

I guess that it can be adapted for other time functions with ease, but I 
do not need them yet.

Thanks to everybody involved,

Thrasher

Tom Lane wrote:
> Thrasher <thrasher@fibers.upc.es> writes:
> 
>>The biggest point that I see is that it would be nice to have some kind 
>>of function that works with UTC values, regarding of which timezone the 
>>user has set.
> 
> 
> You can do that in 7.3, using the AT TIME ZONE construct.  Observe:
> 
> regression-# begin;
> BEGIN
> regression=# select now();
>               now
> -------------------------------
>  2002-11-22 09:59:48.706508-05			'now' in local time (EST)
> (1 row)
> 
> regression=# select now() at time zone 'UTC';
>           timezone
> ----------------------------
>  2002-11-22 14:59:48.706508			'now' in UTC
> (1 row)
> 
> regression=# select date_trunc('month', now() at time zone 'UTC');
>      date_trunc
> ---------------------
>  2002-11-01 00:00:00				month start in UTC
> (1 row)
> 
> regression=# select date_trunc('month', now() at time zone 'UTC') at time zone 'UTC';
>         timezone
> ------------------------
>  2002-10-31 19:00:00-05				month start in local time
> (1 row)
> 
> regression=# commit;
> 
> This is a tad bulky maybe, but it'd be easy to wrap up in a user-defined
> function.
> 
> AT TIME ZONE was less functional, and very poorly documented, in 7.2.
> The 7.3 version is described at
> http://developer.postgresql.org/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 
>