SEO

I use the below query to get the Timeoffset from a given timezone.
However this query will not work for many timezone example for MST.

1. SELECT TZ_OFFSET('PST') FROM dual; -- this query works fine for
Timezone PST and returns a timeoffset correctly

2. SELECT TZ_OFFSET('SST') FROM dual; --this query does not work for
Timezone SST
ORA-01882: timezone region not found



How I can I get the timeoffset for all timezones ? Kindly help , this
has to be implemented asap!
Any input is appreciated!

regards,
Aparna

Aparna wrote:
> I use the below query to get the Timeoffset from a given timezone.
> However this query will not work for many timezone example for MST.
>
> 1. SELECT TZ_OFFSET('PST') FROM dual; -- this query works fine for
> Timezone PST and returns a timeoffset correctly
>
> 2. SELECT TZ_OFFSET('SST') FROM dual; --this query does not work for
> Timezone SST
> ORA-01882: timezone region not found

SELECT * from V$TIMEZONE_NAMES

brgds

Gunter Herrmann in Orlando

Hello Guntur,
Thanks for the response.

I am aware that for oracle function TZ_OFFSET , value from column
TZNAME from view V$TIMEZONE_NAMES has to be used.

But the problem is TZNAME column does not have entry for many
timezones , example SST, NDT, ADT . How can I get the timeoffset for
these ??
Awaiting your answer eagerly!

Thanks and regards,
Aparna

On Jan 14, 3:16*pm, Aparna <Aparna....@gmail.com> wrote:
> Hello Guntur,
> * *Thanks for the response.
>
> I am aware that for oracle function TZ_OFFSET , * value from column
> TZNAME from view V$TIMEZONE_NAMES has to be used.
>
> But the problem is TZNAME column does not have entry for many
> timezones , example SST, NDT, ADT . How can I get the timeoffset for
> these ??
> Awaiting your answer eagerly!
>
> Thanks and regards,
> Aparna

Hi Aparna,

Is this what you're looking for?

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='SST') loop
4 dbms_output.put_line('tzname '||v_rec.tzname||', offset '||
v_rec.v_offset);
5 end loop;
6 end;
7 /
tzname Pacific/Midway, offset -11:00
tzname Pacific/Pago_Pago, offset -11:00
tzname Pacific/Samoa, offset -11:00
tzname US/Samoa, offset -11:00

PL/SQL procedure successfully completed.

Regards,
Arun

Arun,
Thanks!

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.

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

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@earthlink.net, jleffler@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
15747361EBB4814CABBA7939ED426C1D3B6181F0C4EC3B5AAE 70A7DD245AB3AE

Yes, I now understand that each timezone code can multiple
offset(because of multiple region).
I had to discuss this with my lead and now they have changed the
requirement a little bit .
They are now giving me Timezone code and the region name , using this
I can get just 1 offset, for that particular region.

Thanks to all for the help.

regards,
Aparna