title: oracle db timezone for session and database, change of session zone not working
body: <p>I am trying to get the DB to display dates in the same way it works at airports. For example, if you are in Texas and you need to take a flight to the East coast, the airport marquis will display the takeoff time and landing time <em>in your local time</em>. So, for example a flight from Dallas to New York would show the times in the local time for the area.</p>
  
  <pre><code>Marquis in Dallas :     Takeoff time : 8AM  Landing time: 10AM  
  Marquis in New York:    Takeoff time : 9AM  Landing time: 11AM
  </code></pre>
  
  <p>In order to do this, I thought the DB would store the times in UTC. I know that TIMESTAMP does not have a Zone associated with it - but - it does allow one to save time to a DB <em>with the Zone</em> attached to it - so - my thoughts were that some kind of calculation would have been performed to convert it to UTC. But, according to my little test below, this does not seem to be going on. The date stays the same no matter what I set the SESSION TIME ZONE to.</p>
  
  <p>TIA</p>
  
  <pre><code>SQL&gt; create table toast ( t timestamp );
  Table created.
  
  
  SQL&gt; insert into toast values ( TIMESTAMP '2019-09-23 16:03:11 US/Eastern');
  1 row created.
  
  
  SQL&gt; select dbtimezone from dual;
  DBT
  ---
  UTC
  
  SQL&gt; select sessiontimezone from dual;
  SESSIONTIMEZONE
  ---------------------------------------------------------------------------
  -04:00
  
  
  SQL&gt; select * from toast;
  T
  ---------------------------------------------------------------------------
  23-SEP-19 04.03.11.000000 PM
  </code></pre>
  
  <p><strong>changing the timezone in the session still gets the same date</strong></p>
  
  <pre><code>SQL&gt; alter session set time_zone = 'America/Chicago';
  Session altered.
  
  SQL&gt; select sessiontimezone from dual;
  SESSIONTIMEZONE
  ---------------------------------------------------------------------------
  America/Chicago
  
  SQL&gt; select * from toast;
  T
  ---------------------------------------------------------------------------
  23-SEP-19 04.03.11.000000 PM
  </code></pre>
  
  <p><strong>changing it again, same results</strong>  </p>
  
  <pre><code>SQL&gt; alter session set time_zone = 'Pacific/Auckland';
  Session altered.
  
  
  SQL&gt; select * from toast;
  T
  ---------------------------------------------------------------------------
  23-SEP-19 04.03.11.000000 PM
  </code></pre>
  
  <p><strong>changed it using hours instead, got same result</strong></p>
  
  <pre><code>SQL&gt; SQL&gt; alter session set time_zone = '-3:00';
  Session altered.
  
  
  SQL&gt; select sessiontimezone from dual;
  SESSIONTIMEZONE
  ---------------------------------------------------------------------------
  -03:00
  
  
  SQL&gt; select * from toast;
  T
  ---------------------------------------------------------------------------
  23-SEP-19 04.03.11.000000 PM
  </code></pre>
  
  <p><strong>Update</strong>
  Thanks so much @Alex Poole for that detailed response! </p>
  
  <p>I was working with Hibernate, Java and Oracle and was having some issues saving time-based data using Hibernate (regarding that part, I saw this post here that formats a solution using the JAVA Calendar class). The article is here: <a href="http://palashray.com/how-to-handle-oracle-timestamp-with-timezone-from-java/" rel="nofollow noreferrer">How To Handle Oracle TimeStamp with TimeZone from Java</a> I had also glanced at the paper you mentioned about the "tirade" before (and other articles as well). They seemed to discourage the use of TIMESTAMP WITH LOCAL TIMEZONE. It was only because of this info that I was kinda trying to stick with TIMESTAMP altogether :-) But, there is also the option of TIMESTAMP WITH TIMEZONE. </p>
  
  <p>Do you have any thoughts on the use of this Oracle type?</p>