Обсуждение: Per-query local timezone
Is it possible to incorporate SET TIMEZONE into a query, so that to_char(...'TZ') etc. is appropriately localised? 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. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
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]