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

From: Arun Mathur <themathurs_at_gmail.com>
Date: Mon, 14 Jan 2008 12:28:40 -0800 (PST)
Message-ID: <cafd85ed-2204-469f-9b7e-f1fed5693e4c@n20g2000hsh.googlegroups.com>

On Jan 14, 3:16 pm, Aparna <Aparna...._at_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 Received on Mon Jan 14 2008 - 14:28:40 CST