Home » SQL & PL/SQL » SQL & PL/SQL » DBTime Zone&Off Set (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
DBTime Zone&Off Set [message #666174] Wed, 18 October 2017 08:23 Go to next message
Revathi.orafaq
Messages: 21
Registered: September 2017
Junior Member
Hi All,

What is the relation between Time Zone Off set and Time Zone Abbreviation.

From the following query one OFF SET is mapped to the multiple TZABBREV
and one TZABBREV is mapped to multiple time Zones OFF SET .


select  dbtimezone , sessiontimezone  , TZ_OFFSET(TZNAME) OFF_SET  , TZNAME  , N.TZABBREV  from  GV$TIMEZONE_NAMES N
WHERE TRIM(TZ_OFFSET(TZNAME))  LIKE '%-04:00%';

or  
select    dbtimezone , sessiontimezone , TZNAME  , N.TZABBREV  , TZ_OFFSET(TZNAME)   off_Set from  GV$TIMEZONE_NAMES N
WHERE  TZABBREV = 'IST';


My question is how to get the exact location by passing the TIMEZONE from the database

Thanks
Revathi.T
Re: DBTime Zone&Off Set [message #666177 is a reply to message #666174] Wed, 18 October 2017 08:53 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
Timezones are messy like that.
they are roughly related to longitude, but if you look at a timezone map (here's one I googled earlier) then you'll see it's only roughly.
Things are complicated further by summer time off sets - and generally they're defined by individual countries. And some countries don't bother with it.
I'm in the UK, which is currently in BST (british summer time) which is +1, but will soon go back to GMT (+0).
In the country of niger they are on WAT (west africa time) which is +1. They have no summer time offset.

So if you just say you're in offset +1 are you on BST or WAT or one of the others that are currently +1?
If you use the abbreviation that's an improvement, but even then - some countries use summer time adjustments and others don't.
If you look at WAST (west african summer time) you'll see one country uses it as an offset from WAT.
But if you look at WAT you'll see more than one country uses that.
So you can't work out where you are from the abbreviation either, unless it happens to only be used in one place.
Re: DBTime Zone&Off Set [message #666210 is a reply to message #666177] Fri, 20 October 2017 00:46 Go to previous message
Revathi.orafaq
Messages: 21
Registered: September 2017
Junior Member
Hi Thank you very much for clear explanation.
Our Database is also at London.It seems to be DBTIMEZONE component
doesn't includes DST, but systimestamp , calculated offset includes it based on the following results .

SELECT SYSTIMESTAMP ,
  SYS_EXTRACT_UTC(SYSTIMESTAMP) UTC_TIME ,
  dbtimezone ,
  sessiontimezone ,
  TZNAME ,
  N.TZABBREV ,
  TZ_OFFSET(TZNAME) off_Set
FROM GV$TIMEZONE_NAMES N
WHERE tzname   = 'Europe/London'
AND N.TZABBREV ='BST'

SYSTIMESTAMP              UTC_TIME              dbtimezone OFFSET   sessiontimezone TZNAME         TZA   
20-OCT-17 06.37.56.318AM  20-OCT-17 05:37:56AM +00:00      +01:00    Europe/London  Europe/London   BST   
.

Thanks
Revathi.T

Previous Topic: Restrictions on DDL-triggers in PL/SQL
Next Topic: REPLACE Function
Goto Forum:
  


Current Time: Thu Dec 14 23:05:40 CST 2017

Total time taken to generate the page: 0.01728 seconds