Aw: Re: Re: Given geographic coordinates, how does oracle determine the correct timezone?

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_at_web.de> 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_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:

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 | _at_zztat_oracle

-- http://www.freelists.org/webpage/oracle-l

 

 

--

//

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