convert in GMT time zone without summer time - Mailing list pgsql-sql

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.

For example, it must now, in the summer, the German time back by 2 hours and
in the winter time only 1 hour. But it expects only back one hour.

Is there a function or a specific time zone?
(I work with PostgreSQL 8.4.7)

[CODE]
CREATE OR REPLACE FUNCTION CONVERT_TO_UTC (TIMESTAMP with time zone,
VARCHAR)
returns TIMESTAMP
as $$
declare v_zone VARCHAR(20); p_time ALIAS FOR $1; p_zone ALIAS FOR $2; v_time1 TIMESTAMP with time zone; v_time2
TIMESTAMPwith time zone; v_text1 text; 
begin IF LENGTH(p_zone) IS NULL THEN   v_zone := 'GMT'; else   v_zone := p_zone; END IF; -- ++ Timestamp with time zone
inText umwandeln select to_char(p_time, 'DD Mon YYYY HH24:MI:SS') into v_text1; if(v_zone in ('BST', 'CET', 'DNT',
'FST','MET', 'MEWT', 'MEZ', 'NOR', 
'SET', 'SWT', 'WETDST')) then   -- ++ Timestamp with time zone in die Zeitzone '+01' umwandeln. ++   SET TIME ZONE 1;
SelectCONVERT_TO_UTC_EXEC(v_text1, 'UTC') into v_time1; elsif(v_zone in ('JST', 'KST', 'MHT', 'WDT', 'AWSST')) then
SETTIME ZONE 9;   -- ++ Timestamp with time zone in die Zeitzone '+09' umwandeln. ++ Select
CONVERT_TO_UTC_EXEC(v_text1,'UTC') into v_time1;   elsif(v_zone in ('GMT', 'UT', 'UTC', 'Z', 'ZULU', 'WET')) then   --
++Zone wird nicht geändert ++   v_time1 := p_time; else   raise exception 'unbekannte Zone - ist noch eine Baustelle';
endif; RETURN v_time1 ; 
end
$$
LANGUAGE 'plpgsql';
[/CODE]
[CODE]
CREATE OR REPLACE FUNCTION CONVERT_TO_UTC_EXEC (Text, Text)
returns TIMESTAMP
as $$
declare p_time ALIAS FOR $1; p_zone ALIAS FOR $2; v_time1 TIMESTAMP with time zone; v_time2 TIMESTAMP with time zone;
v_text1text; 
begin select to_timestamp (p_time, 'DD Mon YYYY HH24:MI:SS') into v_time1 ; -- ++ Timestamp with time zone in die UTC
Zeitzoneumwandeln. ++ Select timezone( p_zone, v_time1) INTO v_time2 ; -- ++ Zeitausgabe formatieren: HH12. ++ v_text1
:=to_char(v_time2, 'DD Mon YYYY HH12:MI:SS AM'); -- ++ In Type Timestamp umwandeln. ++ RETURN to_timestamp( v_text1,
'DDMon YYYY HH12:MI:SS AM') ; 
end
$$
LANGUAGE 'plpgsql';
[/CODE]

calling:
[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]

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/convert-in-GMT-time-zone-without-summer-time-tp4304830p4304830.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.