SQL to convert TimeZone of TimeStamp column from A to B when system is in TimeZone C
<p><code>BirthDatabase</code> table in Oracle 11g has following five columns:</p>

<ol>
<li><code>ID</code> - Not Null - Number</li>
<li><code>Name</code> - Varchar2(32) </li>
<li><code>DOB</code> - Date</li>
<li><code>Place</code> - Varchar2(32) </li>
<li><code>BirthTime</code> - TimeStamp(6)</li>
</ol>

<p><code>BirthTime</code> column is not TimeZone aware but has DateTime data which is in <code>UTC</code>.</p>

<p>However, (most importantly) system timestamp for Database is in <code>Europe\London</code>.</p>

<p>This implies that <code>BirthTime</code> data is not <code>timezone</code> aware and is in a different TZ as compared to system TZ.</p>

<p><strong>Task:</strong> write a SQL query to fetch <code>BirthTime</code> in London local time (i.e., UTC when DST is OFF and UTC+1 when DST is ON)</p>

<p>My approach:</p>

<p>I have tried to play with <code>cast</code> function in SQL but didn't quite achieve the desired output</p>

<pre><code>select
    ID, Name, DOB, Place,
    BirthTime as orig_BT,
    cast(BirthTime as timestamp with time zone) as BT_withTz, 
    cast(cast(BirthTime as timestamp with time zone) at time zone 'Europe/London' as timestamp) BT_BST,
    cast(cast(BirthTime as timestamp with time zone) at time zone 'UTC' as timestamp) BT_UTC
from
    BirthDatabase
</code></pre>

<p>This returns the following output:</p>

<pre><code>ID  NAME    DOB         PLACE   ORIG_BT                         BT_WITHTZ                                   BT_BST                          BT_UTC
________________________________________________________________________________________________________________________________________________________________________
1   John    28-OCT-16   Bristol 28-OCT-16 10.48.12.000000000    28-OCT-16 10.48.12.000000000 EUROPE/LONDON  28-OCT-16 10.48.12.000000000    28-OCT-16 09.48.12.000000000
2   Jane    01-NOV-16   London  01-NOV-16 11.48.29.000000000    01-NOV-16 11.48.29.000000000 EUROPE/LONDON  01-NOV-16 11.48.29.000000000    01-NOV-16 11.48.29.000000000
</code></pre>

<p>My desired output is following:</p>

<pre><code>ID  NAME    DOB         PLACE   ORIG_BT                         BT_DESIRED
____________________________________________________________________________________________
1   John    28-OCT-16   Bristol 28-OCT-16 10.48.12.000000000    28-OCT-16 11.48.12.000000000
2   Jane    01-NOV-16   London  01-NOV-16 11.48.29.000000000    01-NOV-16 11.48.29.000000000
</code></pre>

<p>I think one of the quick and dirty way to get <em>Desired output</em> using the output of above query is to modify the SQL to compute the following:</p>

<pre><code>BT_DESRIED = ORIG_BT + (BT_BST - BT_UTC)
</code></pre>

<p>My question are as follows:</p>

<ol>
<li><p>What is the syntax to compute above expression of <code>BT_DESRIED = ORIG_BT + (BT_BST - BT_UTC)</code></p></li>
<li><p>Is there a more elegant way to achieve this?</p></li>
</ol>