OraFAQ Forum: SQL & PL/SQL » DBTime Zone&Off Set

Home » SQL & PL/SQL » SQL & PL/SQL » DBTime Zone&Off Set (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBTime Zone&Off Set [message #666174]

Wed, 18 October 2017 08:23

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

Report message to a moderator

Re: DBTime Zone&Off Set [message #666177 is a reply to message #666174]

Wed, 18 October 2017 08:53

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: DBTime Zone&Off Set [message #666210 is a reply to message #666177]

Fri, 20 October 2017 00:46

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 21:49:32 CDT 2024