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
|
|
|
Re: DBTime Zone&Off Set [message #666177 is a reply to message #666174] |
Wed, 18 October 2017 08:53 |
cookiemonster
Messages: 13925 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 |
|
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
|
|
|