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
--> 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(at)fibers(dot)upc(dot)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
>
>