Re: Per-query local timezone
От | Steve Crawford |
---|---|
Тема | Re: Per-query local timezone |
Дата | |
Msg-id | 4DF77AF0.6070805@pinpointresearch.com обсуждение исходный текст |
Ответ на | Re: Per-query local timezone (Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk>) |
Ответы |
Re: Per-query local timezone
|
Список | 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
В списке pgsql-general по дате отправления: