convert AT TIME ZONE ...

Indarija · #1 (**permalink**) 04-12-11, 04:09

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?

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:

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';

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

must come out:

Code:

WINTER	            | SUMMER
--------------------+-------------------------
2011-03-22 13:17:00 | 2011-04-22 12:17:00

Internet Services

Web Development

Digital Marketing

Consumer Tech