Re: How to find time offset from timezone information in Oracle

Aparna wrote:
> Yes this is similar to what I'm looking for, BUT there is a problem
> here.
> 1. My requirement expects just 1 Time Offset value.

Your requirement is going to need adjustment to the realities of a 3-dimensional world outside your computer.

> If I use your above query on TimeZone PDT , it would return regions
> with different timeoffset. (Its the same problem with LMT)
>
> Is there any other function or means to get just one time offset value
> for a given timezone?? Any input is appreciated!
>
> ***************************
> RESULT with Timezone PDT
>
> SQL> set serveroutput on size 1000000
> SQL> begin
> 2 for v_rec in (select tzname,tz_offset(tzname) v_offset from v
> $timezone_names
> 3 where tzabbrev='PDT') loop
> 4 dbms_output.put_line('tzname '||v_rec.tzname||', offset '||
> v_rec.v_offset);
> 5 end loop;
> 6 end;
> 7 /
> tzname America/Dawson, offset -08:00
> tzname America/Dawson_Creek, offset -07:00
> tzname America/Ensenada, offset -08:00
> tzname America/Inuvik, offset -07:00
> tzname America/Juneau, offset -09:00
> tzname America/Los_Angeles, offset -08:00
> tzname America/Tijuana, offset -08:00
> tzname America/Vancouver, offset -08:00
> tzname America/Whitehorse, offset -08:00
> tzname Canada/Pacific, offset -08:00
> tzname Canada/Yukon, offset -08:00
> tzname Mexico/BajaNorte, offset -08:00
> tzname PST, offset -08:00
> tzname PST8PDT, offset -08:00
> tzname US/Pacific, offset -08:00
> tzname US/Pacific-New, offset -08:00

Time zone abbreviations are not unique - full stop. Get used to it.

EST can mean (at minimum) the eastern side of the USA, or the eastern side of Australia - with correspondingly different offsets from UTC (aka GMT). That's why the long names are used - and are necessary; they give the necessary uniqueness. (Granted, some names contain duplicate time zone defintions, so US/Pacific is the same as America/Los_Angeles.)

LMT is short for 'local mean (solar) time'; it is used in the Olson database for the time zone in a city before the time zones were standardized, and is based on the longitude of the city.

If you really insist on one zone, can't you use ROWNUM? What you get is indeterminate, but at least you only get one of them.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-2277 ripemd256 2008-01-15 03:00:05
15747361EBB4814CABBA7939ED426C1D3B6181F0C4EC3B5AAE70A7DD245AB3AE