convert in GMT time zone without summer time - Mailing list pgsql-sql
From | LaraK |
---|---|
Subject | convert in GMT time zone without summer time |
Date | |
Msg-id | 1302848430605-4304830.post@n5.nabble.com Whole thread Raw |
Responses |
Re: convert in GMT time zone without summer time
|
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.