SQL to convert TimeZone of TimeStamp column from A to B when system is in TimeZone C

BirthDatabase

table in Oracle 11g has following five columns:

ID

- Not Null - Number

Name

- Varchar2(32)

DOB

- Date

Place

- Varchar2(32)

BirthTime

- TimeStamp(6)

BirthTime

column is not TimeZone aware but has DateTime data which is in

UTC

.

However, (most importantly) system timestamp for Database is in

Europe\London

.

This implies that

BirthTime

data is not

timezone

aware and is in a different TZ as compared to system TZ.

Task: write a SQL query to fetch

BirthTime

in London local time (i.e., UTC when DST is OFF and UTC+1 when DST is ON)

My approach:

I have tried to play with

cast

function in SQL but didn't quite achieve the desired output

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