Re: Can a Package determine db's time zone during exec?

 In article <DBIpKr.6pC_at_corsair.daytonoh.attgis.com>, Dave Podnar <dave.podnar_at_daytonOH.ATTGIS.COM> writes:
> I am looking for assistance with identifying a database's time zone. I cannot
> find any info (PL/SQL, functions, etc.) on how to determine the time zone (EST,
> EDT, etc.) of the local database (CHI) during package execution.
>
> I know there was a recent thread about storing all times in GMT but I didn't see
> anything mentioned about getting the databases time zone.
>
> All suggestions are greatly appreciated.
>
>
>
>
> dave.podnar_at_daytonOH.ATTGIS.COM
> "You can keep living in the nile, Grace." -- Jimmie
>
>
>
>

Unfortunately Oracle doesn't store the time zone information anywhere in the database. One work-around is to store this information in your own reference table. One of our applications requires to store the GMT time of a transaction so we chose to store the offset value between sysdate and GMT time in a table (ie 4 hours if you are on the east coast). We update this table periodically through an automated Unix batch job that calculates the offset based on the Unix date function. Then we created a PL/SQL fuction that returns the GMT time based on sysdate and the value stored in the offset table. This method can be ported to any database in any time zone without modifications.

-- 
------------------------------------------------------------------------------
Pierre Hollard                                 
Oracle Database Administrator                  Phone: (404) 827-0656
Turner Broadcasting System, Inc.              E-Mail: phollard_at_dev1.turner.com
------------------------------------------------------------------------------