Sorry I should have been more specific since I have tried it using the
abbreviated ones:
SQL> select systimestamp at time zone (tzabbrev) from v$timezone_names;
select systimestamp at time zone (tzabbrev) from v$timezone_names
*
ERROR at line 1:
ORA-01882: timezone region not found
On Thu, Mar 15, 2018 at 1:45 PM, <rogel@xxxxxx> wrote:
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@xxxxxxxxx>
*An:* gus.spier@xxxxxxxxx
*Cc:* oracle-l <oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxx> wrote:
The 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.
Any ideas will be appreciated
Regards,
Gus
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | Support our Indiegogo campaign at igg.me/at/zztat
| @zztat_oracle
-- //www.freelists.org/webpage/oracle-l
