TZFor the impatient: Download, install, have fun. RTFM (included) if it doesn't work...
A timezone package for Oracle
SummaryThis is a small Oracle package that provides an interface to external C procedures to convert between different timezones.
Table Of Contents
Oracle has built-in support for timezone conversions with its
NEW_TIMEfunction. NEW_TIME, however, has no understanding of changes in the time zone for daylight-savings time or non-US timezones.
TZpackage presented here interfaces with external function found at ftp://elsie.nci.nih.gov/pub to allow for a more general timezone conversion facility.
DownloadThe 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.
TZhas 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
SQLPlus Worksheetto 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
Update: Earl C. Ruby III sent some notes about installing
TZon Oracle 9.2 - you can find them at http://weitz.de/files/ruby.txt.
The necessary files from ftp://elsie.nci.nih.gov/pub are included with this distribution. You can optionally update the two
tz*.tar.gzto newer versions, but I cannot guarantee that the installation procedure will still work in this case.
Unpack and untar the files and
timezonedirectory. Change the following variables in the first few lines of the file
- Full path to the target directory for the
zoneinfofiles. Please make sure not to overwrite your orginal
zoneinfofiles - you will mess up your operating system if you do!!!
- Full path to the directory where the shared library is to be installed.
- Where you C Compiler lives.
awkcan be found.
perlcan be found.
makewill patch the timezone library and create the shared library that will be used by
make installwill copy the shared library and the zoneinfo files to the places that you configured in the
Makefile. Also, the file
install.sqlwill be patched for your environment.
Now you can start
SQL Plusas user
system(or another user with similar rights) and execute the file
install.sqlwhich will create the library in Oracle and grant execute rights on it to everyone.
Finally, you can start
SQL Plusas a normal user to execute the file
tz.sqlwhich will create the actual
TZpackage for you.
If you have
DBD::Oracleinstalled, you can test the package with the small Perl Script
test.plthat'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
TZprovides only one function, namely
conv. It is called with three parameters - a
DATEto 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 bewhich should return your current local time converted to US/Eastern time - provided you live in Berlin or nearby...select tz.conv(sysdate, 'Europe/Berlin', 'US/Eastern') from dual;
Daylight-savings periods are automatically taken care of by the timezone library. Check the newly created
zoneinfofiles 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.
DisclaimerTHIS 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.
CopyrightCopyright (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 $