From: Thrasher <thrasher@fibers.upc.es>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Huxton <dev@archonet.com>
Cc: thrasher@fibers.upc.es, pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Subject: Re: Date trunc in UTC
Date: Fri, 22 Nov 2002 10:11:09 +0100
Message-ID: <3DDDF4AD.8030105@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>
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. Let's say, something like
SELECT UTC_DATE_TRUNC ('month', NOW ());
utc_date_trunc
------------------------
2002-11-01 01:00:00+01
because if you work with international applications, the beggining of
the month in Spain should be the same as in Australia. But everyone will
see it in its own timezone.
I think that it would be also interesting to have the UTCeed versions of
EXTRACT and AGE.
Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>
>>Hmm - good point. You can revert to the client default but not to the
>>previous value. I don't know of any way to read these SET values
>>either - a quick poke through pg_proc didn't show anything likely.
>
>
> In 7.3 you can use current_setting() and set_config() to access SHOW/SET
> functionality. However, I agree with your suggestion of AT TIME ZONE
> to rotate a timestamp into a target timezone, rather than mucking with
> the TimeZone setting.
>
>
> BTW, Thomas: is AT TIME ZONE supposed to accept
> timestamp-without-timezone input? If so, what's it supposed to do with
> it? The current behavior seems unintuitive to say the least:
>
> regression=# select now();
> now
> -------------------------------
> 2002-11-21 10:19:14.591001-05
> (1 row)
>
> regression=# select now() at time zone 'UTC';
> timezone
> ----------------------------
> 2002-11-21 15:19:18.588279
> (1 row)
>
> regression=# select localtimestamp;
> timestamp
> ----------------------------
> 2002-11-21 10:19:22.629865
> (1 row)
>
> regression=# select localtimestamp at time zone 'UTC';
> timezone
> -------------------------------
> 2002-11-21 05:19:26.178861-05
> (1 row)
>
> It seems to me that the last case should give either an error or
> 2002-11-21 15:19:26.178861 (ie, assume that the timestamp without time
> zone is in my TimeZone zone). In any case, surely the result should
> be of type timestamp WITHOUT time zone?
>
> 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: thrasher@fibers.upc.es, tgl@sss.pgh.pa.us, dev@archonet.com, lockhart@fourpalms.org
Subject: Re: Date trunc in UTC
In-Reply-To: <3DDDF4AD.8030105@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