Re: Per-query local timezone

On 06/14/2011 05:13 AM, Mark Morgan Lloyd wrote:
> Karsten Hilbert wrote:
>> On Tue, Jun 14, 2011 at 09:40:20AM +0000, Mark Morgan Lloyd wrote:
>>
>>> Is it possible to incorporate SET TIMEZONE into a query, so that
>>> to_char(...'TZ') etc. is appropriately localised?
>>
>> You seem to want "AT TIME ZONE".
>
> Thanks for that. How can I do /this/
>
> select to_char(now() at time zone 'GMT0BST', 'TZ');
>
> It appears to return '', while if I used a separate SET TIMEZONE I'd
> expect 'BST'.
>

The "now()" function returns a timestamp with time zone (aka a point in
time). When you ask for a timestamp with time zone at a specific time
zone, you get a timestamp *without* time zone (you provided and
therefore know the desired time zone and PostgreSQL returned the
timestamp in that zone).

I'm a bit concerned with your initial statement that "The development
environment I'm working with uses short-lifetime sessions, and it's
proving difficult to get a set command and a query associated with the
same handle.". Do I take this to mean that connections are going through
some sort of pooler that is allocating connections on as short as a
per-statement basis so you might end up with a different connection
between the "set time zone.." statement and the query? If so, you may
start to find all sorts of other issues.

It's a bit convoluted, but you could get the zone from a subquery and
select the timestamp converted to that zone along with the zone itself
from the outer query:

select now() at time zone foo.tz, foo.tz from (select 'est5edt'::text as
tz) as foo;

Cheers,
Steve