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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
