On 04/16/2011 05:02 AM, Jasen Betts wrote:
On 2011-04-15, LaraK<indar...@gmx.net> wrote:
Hello,
I want write a function that converts a timestamp with time zone to the UTC
zone. But it should all be stored in the winter time.
Done! All timestamp with time zone information is stored internally in UTC.
But you need to be sure you really understand date/time manipulation in
PostgreSQL so you don't reinvent the wheel.
[CODE]
SELECT
to_char(CONVERT_TO_UTC(to_timestamp('2011-03-22 14:17:00', 'YYYY-MM-DD
hh24:MI:SS'), 'CET'), 'yyyy-mm-dd hh24:MI:SS') AS winter,
to_char(CONVERT_TO_UTC(to_timestamp('2011-04-22 14:17:00', 'YYYY-MM-DD
hh24:MI:SS'), 'CET'), 'yyyy-mm-dd hh24:MI:SS') AS summer
[/CODE]
must come out:
[CODE]
WINTER | SUMMER
--------------------+-------------------------
2011-03-22 13:17:00 | 2011-04-22 12:17:00
[/CODE]
that test case is ambiguous your inputs are timespamptz but
have an unspecified timezone (and so get the zone appropriate to
your time locale). I'm assuming your time locale is "Europe/Berlin"
and you really mean the following:
SELECT to_char(CONVERT_TO_UTC( '2011-03-22 14:17:00+01'::timestamptz
,'CET'),'yyyy-mm-dd hh24:MI:SS') AS winter, to_char(CONVERT_TO_UTC(
'2011-04-22 14:17:00+02'::timestamptz ,'CET'),'yyyy-mm-dd hh24:MI:SS')
AS summer;
If you can use the correct time zone name, everything is done for you.
Better yet, it will keep working when the timezone rules change (if you
apply your patches regularly) or for other time zones:
steve=> select '2011-03-22 14:17:00 Europe/Berlin' at time zone 'UTC';
timezone
---------------------
2011-03-22 13:17:00
(1 row)
steve=> select '2011-04-22 14:17:00 Europe/Berlin' at time zone 'UTC';
timezone
---------------------
2011-04-22 12:17:00
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
