Rahul Rahul - 29 days ago 10
SQL Question

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:


  1. ID
    - Not Null - Number

  2. Name
    - Varchar2(32)

  3. DOB
    - Date

  4. Place
    - Varchar2(32)

  5. 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


This returns the following output:

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


My desired output is following:

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


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

BT_DESRIED = ORIG_BT + (BT_BST - BT_UTC)


My question are as follows:


  1. What is the syntax to compute above expression of
    BT_DESRIED = ORIG_BT + (BT_BST - BT_UTC)

  2. Is there a more elegant way to achieve this?


Answer

You can use the following query:

select
   ID, Name, DOB, Place, BirthTime as orig_BT,
   FROM_TZ(BirthTime, 'UTC') AT TIME ZONE 'Europe/London' AS BT_BST
FROM BirthDatabase;

When you make cast(BirthTime as timestamp with time zone) the Oracle takes your SESSIONTIMEZONE for conversion - not the database system time zone.

cast(BirthTime as timestamp with time zone) at time zone 'Europe/London' will give correct results when you execute alter session set time_zone = 'UTC'; beforehand.

Comments