From: Thrasher <thrasher@fibers.upc.es>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Subject: Re: Date trunc in UTC
Date: Tue, 03 Dec 2002 10:30:05 +0100
Message-ID: <3DEC799D.10003@fibers.upc.es> (raw)
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>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: thrasher@fibers.upc.es, tgl@sss.pgh.pa.us, dev@archonet.com, lockhart@fourpalms.org
Subject: Re: Date trunc in UTC
In-Reply-To: <3DEC799D.10003@fibers.upc.es>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox