TZ
A timezone package for Oracle

For the impatient: Download, install, have fun. RTFM (included) if it doesn't work...


Summary

This is a small Oracle package that provides an interface to external C procedures to convert between different timezones.

Table Of Contents

Introduction

Usage

Miscellaneous

Introduction

Oracle has built-in support for timezone conversions with its NEW_TIME function. NEW_TIME, however, has no understanding of changes in the time zone for daylight-savings time or non-US timezones.

The TZ package presented here interfaces with external function found at ftp://elsie.nci.nih.gov/pub to allow for a more general timezone conversion facility.

Download

The package together with the documentation (i.e., this website) is available at http://www.weitz.de/files/timezone.tgz. The current version is 20010130. The size of the file is about 200 kB.

Requirements

TZ has been tested successfully with Oracle 8.0.5, 8.1.5, 8.1.6, and 8.1.7 on different Linux and Solaris versions. I think it should work with all recent Oracle 8 and 8i releases and on all supported platforms. Please let me know about your attempts (whether successful or not) with other operating systems or other versions of Oracle.

You'll need a working C compiler and its associated utilities as well as an Oracle client like SQL Plus or SQLPlus Worksheet to install the package. You will also need to have the necessary rights to create libraries and grant execute rights on them in Oracle. Also, ensure that your DBA has enabled a listener for external procedures in your Net8 environment. (This involves changes to tnsnames.ora and listener.ora.)

Update: Earl C. Ruby III sent some notes about installing TZ on Oracle 9.2 - you can find them at http://weitz.de/files/ruby.txt.

Installation

The necessary files from ftp://elsie.nci.nih.gov/pub are included with this distribution. You can optionally update the two tz*.tar.gz to newer versions, but I cannot guarantee that the installation procedure will still work in this case.

Unpack and untar the files and cd into the timezone directory. Change the following variables in the first few lines of the file Makefile:

TIMEZONE_DIR
Full path to the target directory for the zoneinfo files. Please make sure not to overwrite your orginal zoneinfo files - you will mess up your operating system if you do!!!
TARGET_DIR
Full path to the directory where the shared library is to be installed.
CC
Where you C Compiler lives.
AWK
Where awk can be found.
PERL
Where perl can be found.

make will patch the timezone library and create the shared library that will be used by TZ. make install will copy the shared library and the zoneinfo files to the places that you configured in the Makefile. Also, the file install.sql will be patched for your environment.

Now you can start SQL Plus as user system (or another user with similar rights) and execute the file install.sql which will create the library in Oracle and grant execute rights on it to everyone.

Finally, you can start SQL Plus as a normal user to execute the file tz.sql which will create the actual TZ package for you.

If you have DBD::Oracle installed, you can test the package with the small Perl Script test.pl that's included with this distribution. The output should look like this: 

Berlin: 1985-10-08 17:20:00
London: 1985-10-08 17:20:00

Berlin: 2000-10-08 12:13:55
London: 2000-10-08 11:13:55

Berlin: 2000-10-01 00:03:54
London: 2000-09-30 23:03:54

Usage

TZ provides only one function, namely conv. It is called with three parameters - a DATE to convert, the timezone to convert from (VARCHAR2), and the timezone to convert to (also VARCHAR2). The function returns the converted DATE. A sample call would be 
select tz.conv(sysdate, 'Europe/Berlin', 'US/Eastern') from dual;
which should return your current local time converted to US/Eastern time - provided you live in Berlin or nearby...

Daylight-savings periods are automatically taken care of by the timezone library. Check the newly created zoneinfo files or the documentation that comes with the timezone library to find out which timezones are available and how they are spelled.

The package doesn't check whether you provide correct timezones. If you supply invalid timezones, they will usually default to GMT, but don't count on that.

Miscellaneous

Disclaimer

THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Copyright

Copyright (c) 2001 Dr. Edmund Weitz. All rights reserved.
$Header: /usr/local/cvsrep/weitz.de/timezone.html,v 1.7 2004/12/25 21:17:38 edi Exp $