From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Thrasher <thrasher(at)fibers(dot)upc(dot)es> |
Cc: | Richard Huxton <dev(at)archonet(dot)com>, pgsql-sql(at)postgresql(dot)org, Thomas Lockhart <lockhart(at)fourpalms(dot)org> |
Subject: | Re: Date trunc in UTC |
Date: | 2002-11-22 15:10:42 |
Message-ID: | 23923.1037977842@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Luc Lachance | 2002-11-22 16:26:25 | Re: calculating interval |
Previous Message | Dan Langille | 2002-11-22 14:36:15 | Re: calculating interval |