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

From: Stefan Knecht <>
Date: Thu, 15 Mar 2018 09:45:43 +0700
Message-ID: <>

As far as I know, it will need some work.

if you look at section 11.5 here: 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.


On Thu, Mar 15, 2018 at 8:03 AM, Gus Spier <> 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 | Support our Indiegogo campaign at |

Received on Thu Mar 15 2018 - 03:45:43 CET

Original text of this message