Re: converting timstamptz to local time zone

On Thu, Mar 30, 2006 at 02:24:55PM +0300, Peter Nixonn wrote:
> How can I "select ('2006-01-01 9:00:00+02' at time zone 'GMT')::timestamp;"
> where instead of GMT it has the local time zone of the machine I am running
> it on (which varies between my machines)?
> I dont want to hard code the timezone of the machine into the query.. I want
> to pick it up from the environment, but cant figure out how :-(

Just cast it to 'timestamp', that uses the configured timezone:

test=# set timezone='Europe/Amsterdam';
SET
test=# select '2006-01-01 9:00:00+02'::timestamptz::timestamp;
timestamp
---------------------
2006-01-01 08:00:00
(1 row)

test=# set timezone='Australia/Sydney';
SET
test=# select '2006-01-01 9:00:00+02'::timestamptz::timestamp;
timestamp
---------------------
2006-01-01 18:00:00
(1 row)

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.