Aw: Re: Given geographic coordinates, how does oracle determine the correct timezone?
From: <rogel_at_web.de>
Date: Thu, 15 Mar 2018 07:45:54 +0100
Message-ID: <trinity-97b42c0f-92bf-4b36-b29c-889b38be6e5f-1521096354603_at_3c-app-webde-bs27>
--
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 15 2018 - 07:45:54 CET
Date: Thu, 15 Mar 2018 07:45:54 +0100
Message-ID: <trinity-97b42c0f-92bf-4b36-b29c-889b38be6e5f-1521096354603_at_3c-app-webde-bs27>
Hi Stefan,
you wrote
... For example, many of the time zones listed there can't actually be used in a timestamp at time zone 'x' expression and will return "ORA-01882: timezone region not found" despite them being listed in Oracle's "list of valid time zones". ...
This does not reproduce to me, neither on my local production DBs nor on livesql, see https://livesql.oracle.com/apex/livesql/file/content_GEP758MVIHZH0V5NTW5LIJR47.html
Matthias
Gesendet: Donnerstag, 15. März 2018 um 03:45 Uhr
Von: "Stefan Knecht" <knecht.stefan_at_gmail.com>
An: gus.spier_at_gmail.com
Cc: oracle-l <oracle-l_at_freelists.org>
Betreff: Re: Given geographic coordinates, how does oracle determine the correct timezone?
Von: "Stefan Knecht" <knecht.stefan_at_gmail.com>
An: gus.spier_at_gmail.com
Cc: oracle-l <oracle-l_at_freelists.org>
Betreff: Re: Given geographic coordinates, how does oracle determine the correct timezone?
As far as I know, it will need some work.
if you look at section 11.5 here: https://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_geocode_concepts.htm#SPATL832 the GC_AREA_* tables seem to have half the information you need. But based on how that document is written, I'm not sure if they are populated by default, or if you need to obtain (purchase?) that data somehow. I don't have a database with Spatial installed readily available right now.
Also, I believe you may need to make your own mapping from country code to time zone, though. Oracle does provide things like v$timezone_names, but it's a bit "off". For example, many of the time zones listed there can't actually be used in a timestamp at time zone 'x' expression and will return "ORA-01882: timezone region not found" despite them being listed in Oracle's "list of valid time zones".
I'd be interested to know how it goes if you make any progress with this.
Stefan
On Thu, Mar 15, 2018 at 8:03 AM, Gus Spier <gus.spier_at_gmail.com> wrote:
GusRegards,Any ideas will be appreciatedThe task is to supply the local time for a transaction when all we have are the geographic coordinates and UTC (Greenwich Mean Time). Is there no internal table that we can exploit?If not, then much skull sweat will be expended adapting tz_world (or its successors) to the purpose.
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | Support our Indiegogo campaign at igg.me/at/zztat | _at_zztat_oracle