On 04/16/2011 05:02 AM, Jasen Betts wrote:
> On 2011-04-15, LaraK<indarija@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