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

This database seems to have everything, including the time zones:

http://download.geonames.org/export/dump/

This could give you what you need with a single select statement.

I could think of some fun stuff to do with that data :)

Stefan

On Thu, Mar 15, 2018 at 9:45 AM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

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

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