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

                                  *

> 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

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