From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thrasher <thrasher@fibers.upc.es>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Subject: Re: Date trunc in UTC
Date: Fri, 22 Nov 2002 10:10:42 -0500
Message-ID: <23923.1037977842@sss.pgh.pa.us> (raw)
In-Reply-To: <3DDDF4AD.8030105@fibers.upc.es>
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>
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
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: tgl@sss.pgh.pa.us, thrasher@fibers.upc.es, dev@archonet.com, lockhart@fourpalms.org
Subject: Re: Date trunc in UTC
In-Reply-To: <23923.1037977842@sss.pgh.pa.us>
* 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