Per-query local timezone

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".

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On 06/14/11 2:40 AM, Mark Morgan Lloyd wrote:

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.

this environment doesn't support even a transaction?

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

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'.

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

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

John R Pierce <pierce@hogranch.com> writes:

On 06/14/11 2:40 AM, Mark Morgan Lloyd wrote:

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.

this environment doesn't support even a transaction?

Sounds kinda broken :-( ... but maybe Mark could wrap the operations
he needs into custom functions.

regards, tom lane

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

Tom Lane wrote:

John R Pierce <pierce@hogranch.com> writes:

On 06/14/11 2:40 AM, Mark Morgan Lloyd wrote:

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.

this environment doesn't support even a transaction?

Sounds kinda broken :-( ... but maybe Mark could wrap the operations
he needs into custom functions.

Is always a possibility. The problem is that particular component I'm
using conflates the open and issue-query operations and has an implicit
transaction, the developers are aware that this has undesirable
implications.

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

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

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]