title: What is the inverse of SYS_EXTRACT_UTC() in Oracle?
body: <h1>The question</h1>
  
  <p>Let <code>original_date</code> be a <code>date</code>. How can I get back its value from the result of <code>SYS_EXTRACT_UTC(cast(original_date as timestamp ))</code>, exclusively by internal Oracle functions, i.e. regardless of any timezone setting of the database.</p>
  
  <h1>Background</h1>
  
  <p>Some stupid program writes the date values into one of the tables in UTC, that is</p>
  
  <pre><code>cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date) 
  </code></pre>
  
  <p>is stored instead of SYSDATE in a date type column of this table.</p>
  
  <p>In all other tables simply SYSDATE is stored in such columns. My task is to use these values together, so I want to do back the effect of the SYS_EXTRACT_UTC() function. I can solve this only if I specify manually my time zone, i.e by </p>
  
  <pre><code>cast( FROM_TZ(cast(my_utc_date as TIMESTAMP), 'UTC')  AT TIME ZONE 'Europe/Budapest' as date)
  </code></pre>
  
  <p>But if I use <code>DBTIMEZONE</code> instead of  <code>'Europe/Budapest'</code>, then I get wrong result, probably because the summer daylight saving time is ignored by <code>DBTIMEZONE</code></p>
  
  <p>For example, when to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS') = '2016-05-19 13:45:12', then program stores  </p>
  
  <pre><code> cast(SYS_EXTRACT_UTC(cast(SYSDATE as timestamp)) as date) 
  </code></pre>
  
  <p>My test query is:</p>
  
  <pre><code>SELECT  
          original_date,
          stored_utc_date,
          cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC')  AT TIME ZONE 'Europe/Budapest' as date) as reverted_good,
          cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC')  AT TIME ZONE DBTIMEZONE as date) as reverted_wrong
  from (
          select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date 
          from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
      )
  </code></pre>
  
  <p>and its result is:</p>
  
  <pre><code>ORIGINAL_DATE       STORED_UTC_DATE     REVERTED_GOOD       REVERTED_WRONG    
  ------------------- ------------------- ------------------- -------------------
  2016-05-19 13:45:12 2016-05-19 11:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12 
  </code></pre>