From: | Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)PostgreSQL(dot)org |
Subject: | Re: Getting the name of the timezone, adjusted for daylight saving |
Date: | 2011-01-26 17:00:21 |
Message-ID: | ihpjv6$ipp$1@pye-srv-01.telemetry.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane wrote:
> Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk> writes:
>> SELECT to_char(('2011-03-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP
>> WITH TIME ZONE, 'HH24:MI TZ');
>> to_char
>> -----------
>> 12:00 GMT
>> (1 row)
>
> You haven't said exactly what you were hoping to accomplish, but I
> suspect the point here is to format a time according to some other zone
> than the prevailing TimeZone setting. You basically can't do that, at
> least not with to_char and the timestamptz data type --- the information
> just isn't there. Consider creating a little plpgsql function that
> temporarily changes the timezone setting and then calls to_char.
Thanks Tom. Timestamps are going into the database which are implicitly
UTC, and I was looking for a way to convert them when displayed to the
local timezone (the client gets this from a configuration file and puts
it in the query) and also to present the timezone name.
So I think that what you're saying is that the result from to_char()
will always be UTC, possibly corrected for daylight saving (i.e. GMT or
BST respectively). I can live with that, the app is for local use and
we're not that far from Greenwich :-)
Now if I'm reading the documentation correctly I can refer to
pg_timezone_names and use is_dst to determine whether a particular
timezone (e.g. GMT) has a daylight saving correction applied at the
current date. But from there is there any cross-reference so that if DST
is active I can change the displayed TZ name to BST?
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
From | Date | Subject | |
---|---|---|---|
Next Message | MargaretGillon | 2011-01-26 17:05:15 | Re: Install Pgadmin3 1.12 on ubuntu 10.4 lucid client without postgresql server install |
Previous Message | Tom Lane | 2011-01-26 15:20:49 | Re: Getting the name of the timezone, adjusted for daylight saving |