Skip site navigation (1) Skip section navigation (2)

Re: DST and time zones

From: A Gilmore <agilmore(at)shaw(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Re: DST and time zones
Date: 2004-09-19 01:11:11
Message-ID: 414CDCAF.5080202@shaw.ca (view raw or flat)
Thread:
Lists: pgsql-novice
Tom Lane wrote:
> A Gilmore <agilmore(at)shaw(dot)ca> writes:
> 
>>I need a method of returning a date in a given timezone and accomidating 
>>DST.  For example, my server is set to UTC, Id like to return the epoch 
>>for Vancouver Canada.
> 
> 
> Huh?  Epoch is UTC all over the world, at least for sane operating
> systems.  But disregarding your specific example, the general problem is
> valid.  I'm afraid there's not a real good solution at the moment.
> You should ideally be able to say
> 
> 	select now() at time zone 'PST8PDT';
> 
> .... 
> 
> In the meantime, the only solution I can suggest is pretty klugy:
> temporarily set the TIMEZONE variable.  For example, I'm in EST5EDT,
> so:
> 
> regression=# select now();
>               now
> -------------------------------
>  2004-09-18 15:57:26.944637-04
> (1 row)
> 
> regression=# begin;
> BEGIN
> regression=# set local timezone = 'PST8PDT';
> SET
> regression=# select extract(hour from cast(now() as timestamp without time zone));
>  date_part
> -----------
>         12
> (1 row)
> 
> regression=# commit;
> COMMIT
> 

My misuse of the term epoch has to do with the kluge Ive been 
considering using to get javascript to reconize timezones without using 
the local system (you cannot set the time zone in js), I shouldn't have 
used it for the example.

Instead of setting the TIMEZONE variable, is there a way I can return a 
given timezone's offset instead?  Maybe Im just dull today but Im having 
trouble picturing how setting the local timezone will accomplish what I 
need.

I have table holding timestamps without a time zone (considered GMT). 
Ill being making inserts into this table with a timestamp like 3pm PST, 
which I need to be translated and inserted as GMT.  Later this will 
likely be queried where I need the timestamp returned for say EST.

Thanks for the help.
A Gilmore


In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-09-19 03:03:28
Subject: Re: DST and time zones
Previous:From: M. BastinDate: 2004-09-18 20:03:33
Subject: Re: Importing data into postgreSQL from FileMaker?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group