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

I actually find it fairly curious:

col chk for a30
col tzabbrev for a30
with function check_tz (abbrev in varchar2) return varchar2 as
l_ts timestamp with time zone;
begin
l_ts := systimestamp at time zone (abbrev); return 'VALID';
exception when others then return 'INVALID'; end;
select unique check_tz(tzabbrev) as chk, tzabbrev from v$timezone_names; /

On Thu, Mar 15, 2018 at 1:52 PM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> 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_GEP758M
>> VIHZH0V5NTW5LIJR47.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
>

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