Re: Date trunc in UTC

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