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 TIMESTAMP with 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 in Text 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;
Select CONVERT_TO_UTC_EXEC(v_text1, 'UTC') into v_time1;
elsif(v_zone in ('JST', 'KST', 'MHT', 'WDT', 'AWSST')) then
SET TIME 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';
end if;
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_text1 text;
begin
select to_timestamp (p_time, 'DD Mon YYYY HH24:MI:SS') into v_time1 ;
-- ++ Timestamp with time zone in die UTC Zeitzone umwandeln. ++
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, 'DD Mon 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
