Re: Per-query local timezone

From: Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk>
To: pgsql-general(at)PostgreSQL(dot)org
Subject: Re: Per-query local timezone
Date: 2011-06-14 17:14:34
Message-ID: it84tq$q5s$1@pye-srv-01.telemetry.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Steve Crawford wrote:
> 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;

Looking back through the mailing list, the issue appears to be the way
that AT TIME ZONE is parsed into a function which returns a string. I
think the easiest way round most of this is going to be to use the PGTZ
shell variable, otherwise I think I can pull the info I need out of
pg_timezone_names subject to using the correct zone name.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-06-14 18:08:49 Re: psql reports back wrong number of affected rows.
Previous Message Merlin Moncure 2011-06-14 16:36:30 Re: random backend crashes - how to debug ( Is crash dump handler released ? )