Re: Per-query local timezone

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk>
Cc: pgsql-general(at)PostgreSQL(dot)org
Subject: Re: Per-query local timezone
Date: 2011-06-14 15:14:56
Message-ID: 4DF77AF0.6070805@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Edmundo Robles L. 2011-06-14 15:20:30 Executing \i of psql command using libpq library
Previous Message Tom Lane 2011-06-14 15:05:49 Re: psql reports back wrong number of affected rows.