title: How to handle time zone issue without storing date time as TIMESTAMP WITH TIME ZONE
body: <p>We are developing a mobile app that retrieve and display data from a legacy system.. In the legacy system the date time column is stored in Date data type, not  TIMESTAMP WITH TIME ZONE. Now we need to handle time zone issue in the mobile app.</p>
  
  <p>We want the time for data to reflect the time zone (offset to GMT) for the user that entered it (or at least be stored in GMT) so that we can interpret it properly on behalf of the user that views it.</p>
  
  <p>Our "extreme case" situation is a legacy system user in time zone A entering orders for time zone A, the database server in time zone B, and a mobile app user in time zone C.</p>
  
  <p>The data appears to have no offset information and is not in GMT, so the time zone is "inferred" at each step:</p>
  
  <ul>
  <li><p>A user in time zone A sees the "correct" time since it was entered
  for that time zone and is therefore interpreted correctly.</p></li>
  <li><p>The mobile app dataservice (which persist/serialize the dataset)
  reads data from the database server in time zone B and    interprets
  the time as being in time zone B, and the time is off by    some
  number of hours.</p></li>
  <li><p>The mobile app user in time zone C interprets the time as being in
  time zone C, and the time is off by some other number of    hours.</p></li>
  </ul>
  
  <p>If we had time zone information for the order as entered we would be able to make decisions about how to display it:</p>
  
  <ul>
  <li><p>In terms of the "originator" time zone</p></li>
  <li><p>In terms of the "viewer" time zone</p></li>
  </ul>
  
  <p>If we had an absolute time in GMT we would be able to display it in terms of the "viewer" time zone (not knowing the "originator" time zone).</p>
  
  <p>Changing the legacy DATE data type to TIMESTAMP WITH TIME ZONE is not an option because it is too big effort for us to do and may have other complications.</p>
  
  <p>Here is my band-aid solution</p>
  
  <ol>
  <li><p>Create a location table that contains UTC info.</p></li>
  <li><p>Add a loc_code column as FK to orders table.</p></li>
  <li><p>Join orders and location table and convert the order_date to  TIMESTAMP WITH TIME ZONE.</p></li>
  </ol>
  
  <p>I know there are holes in this solution and I am looking for better ways to achieve our goal. Any input would be greatly appreciated.</p>
  
  <p>Below is the code,</p>
  
  <pre><code>DROP TABLE location;
  CREATE TABLE location
  (
    loc_code                    VARCHAR2(6)  NOT NULL,
    descr                       VARCHAR2(40) NOT NULL,
    utc_time_zone_offset        VARCHAR2(6)  NULL,
    daylight_savings_start_date DATE         NULL,
    daylight_savings_end_date   DATE         NULL,
    CONSTRAINT locationp1 PRIMARY KEY (loc_code)
  );
  
  INSERT INTO location VALUES ('-5','EST','-05.00',To_Date('2013-03-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),To_Date('2013-11-03 2:00:00','yyyy-mm-dd hh24:mi:ss'));
  INSERT INTO location VALUES ('-6','CST','-06.00',To_Date('2013-03-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),To_Date('2013-11-03 2:00:00','yyyy-mm-dd hh24:mi:ss'));
  INSERT INTO location VALUES ('-7','MST','-07.00',To_Date('2013-03-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),To_Date('2013-11-03 2:00:00','yyyy-mm-dd hh24:mi:ss'));
  INSERT INTO location VALUES ('-8','PST','-08.00',To_Date('2013-03-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),To_Date('2013-11-03 2:00:00','yyyy-mm-dd hh24:mi:ss'));
  INSERT INTO location VALUES ('-9','ALST','-09.00',To_Date('2013-03-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),To_Date('2013-11-03 2:00:00','yyyy-mm-dd hh24:mi:ss'));
  INSERT INTO location VALUES ('-10','HST','-10.00',To_Date('2013-03-10 2:00:00','yyyy-mm-dd hh24:mi:ss'),To_Date('2013-11-03 2:00:00','yyyy-mm-dd hh24:mi:ss'));
  COMMIT;
  
  DROP TABLE orders
  CREATE TABLE orders (order_id VARCHAR2(10),loc_code VARCHAR2(6), order_date DATE);
  
  SELECT order_id,order_date,
         cs_timezone.to_UCT(loc_code,order_date),
         cs_timezone.to_viewer_time(loc_code,order_date)  
    FROM orders 
  
  CREATE OR REPLACE PACKAGE cs_timezone AUTHID CURRENT_USER AS
  -- 1. Only SYSDATE is affected by SYSTIMESTAMP which the timestamp on the server machine itself.
  --    If we do not use SYSDATE, we do not have to worry about SYSTIMESTAMP.
  -- 2. When insert a DATE value Oracle only stores the date time value without any knowledge of time zone.
  --    When we convert to local time we only care about time zone offset for the user who save the data.
  
    FUNCTION to_viewer_time (p_loc_code IN VARCHAR2, p_date IN DATE) RETURN DATE;
    FUNCTION to_UCT (p_loc_code IN VARCHAR2, p_date IN DATE) RETURN TIMESTAMP WITH TIME ZONE;
  END;
  /
  
  CREATE OR REPLACE PACKAGE BODY cs_timezone
  AS
  FUNCTION to_viewer_time (p_loc_code IN VARCHAR2, p_date IN DATE) RETURN DATE
  IS
    v_offset_hrs INT;  v_utc_time_zone_offset VARCHAR2(10); v_daylight_savings_start_date DATE; v_daylight_savings_end_date DATE;
  BEGIN
    SELECT   utc_time_zone_offset,   daylight_savings_start_date,   daylight_savings_end_date
      INTO v_utc_time_zone_offset, v_daylight_savings_start_date, v_daylight_savings_end_date
      FROM location
     WHERE loc_code = p_loc_code;
    IF InStr(v_utc_time_zone_offset,'+') &gt; 0 THEN
      IF To_Date(To_Char(p_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') BETWEEN To_Date(To_Char(v_daylight_savings_start_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') AND To_Date(To_Char(v_daylight_savings_end_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') THEN
         v_utc_time_zone_offset := SubStr(v_utc_time_zone_offset,1,1)||To_Char(To_Number(v_utc_time_zone_offset)+1);
      END IF;
      v_offset_hrs :=  extract(TIMEZONE_HOUR FROM current_timestamp) - To_Number(v_utc_time_zone_offset);
    ELSIF InStr(v_utc_time_zone_offset,'-') &gt; 0 THEN
      IF  To_Date(To_Char(p_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') BETWEEN To_Date(To_Char(v_daylight_savings_start_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') AND To_Date(To_Char(v_daylight_savings_end_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') THEN
         v_utc_time_zone_offset := To_Char(To_Number(v_utc_time_zone_offset)+1);
      END IF;
      v_offset_hrs := To_Number(v_utc_time_zone_offset) - extract(TIMEZONE_HOUR FROM current_timestamp);
    ELSE
      Raise_Application_Error(-20001,'Offset format missing - or +');
    END IF;
    Dbms_Output.put_line(v_offset_hrs); 
    RETURN p_date+(v_offset_hrs/24);
  EXCEPTION WHEN OTHERS THEN RETURN 'ERROR: '||SQLERRM;
  END to_viewer_time;
  
  FUNCTION to_UCT (p_loc_code IN VARCHAR2, p_date IN DATE) RETURN TIMESTAMP WITH TIME ZONE
  IS
    v_utc_time_zone_offset VARCHAR2(10); v_daylight_savings_start_date DATE; v_daylight_savings_end_date DATE;
  BEGIN
    SELECT   utc_time_zone_offset,   daylight_savings_start_date,   daylight_savings_end_date
      INTO v_utc_time_zone_offset, v_daylight_savings_start_date, v_daylight_savings_end_date
      FROM location
     WHERE loc_code = p_loc_code;
    IF InStr(v_utc_time_zone_offset,'+') &gt; 0 THEN
      IF To_Date(To_Char(p_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') BETWEEN To_Date(To_Char(v_daylight_savings_start_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') AND To_Date(To_Char(v_daylight_savings_end_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') THEN
        v_utc_time_zone_offset := SubStr(v_utc_time_zone_offset,1,1)||To_Char(To_Number(v_utc_time_zone_offset)+1);
        IF InStr(v_utc_time_zone_offset,'-') = 0 OR InStr(v_utc_time_zone_offset,'+') = 0 THEN
          v_utc_time_zone_offset := '+'||v_utc_time_zone_offset;
        END IF;
        IF InStr(v_utc_time_zone_offset,'.00') = 0 THEN
          v_utc_time_zone_offset := v_utc_time_zone_offset||':00';
        ELSE
          v_utc_time_zone_offset := REPLACE(v_utc_time_zone_offset,'.',':');
        END IF;
      ELSE
        v_utc_time_zone_offset := REPLACE(v_utc_time_zone_offset,'.',':');
      END IF;
    ELSIF InStr(v_utc_time_zone_offset,'-') &gt; 0 THEN
      IF To_Date(To_Char(p_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') BETWEEN To_Date(To_Char(v_daylight_savings_start_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') AND To_Date(To_Char(v_daylight_savings_end_date,'mm-dd hh24:mi:ss'),'mm-dd hh24:mi:ss') THEN
        v_utc_time_zone_offset := To_Char(To_Number(v_utc_time_zone_offset)+1);
        IF InStr(v_utc_time_zone_offset,'-') = 0 OR InStr(v_utc_time_zone_offset,'+') = 0 THEN
          v_utc_time_zone_offset := '+'||v_utc_time_zone_offset;
        END IF;
        IF InStr(v_utc_time_zone_offset,'.00') = 0 THEN
          v_utc_time_zone_offset := v_utc_time_zone_offset||':00';
        ELSE
          v_utc_time_zone_offset := REPLACE(v_utc_time_zone_offset,'.',':');
        END IF;
      ELSE
        v_utc_time_zone_offset := REPLACE(v_utc_time_zone_offset,'.',':');
      END IF;
    ELSE
      Raise_Application_Error(-20001,'Offset format missing - or +');
    END IF;
    Dbms_Output.put_line(''); 
    RETURN TO_TIMESTAMP_TZ(To_Char(p_date,'YYYY-MM-DD HH24:MI:SS')||' '||v_utc_time_zone_offset, 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
  EXCEPTION WHEN OTHERS THEN RETURN 'ERROR: '||SQLERRM;
  END to_UCT;
  
  END cs_timezone;
  </code></pre>
  
  <p>Thanks,</p>
  
  <p>Shawn</p>