Oracle Time vs. UNIX System Time

In article <1995Nov16.010957.16299_at_nosc.mil> Louise Miller <miller_at_louise.ucsd.edu> writes:
>I'm an applications programmer, so please bear with me.
>
>We have an odd problem with system date/time. We are running Oracle 7.1.3
>on a UNIX system V host, and the tools are running on a Novell network
>using SQLNET 2.1.4.1.4. We have users in several locations using different
>networked servers to execute their Forms.
>
>The problem: On some of the machines, when Oracle is asked for the
>system date/time, it will sometimes respond with a time that is 7 hours
>ahead of Pacific Standard Time. The hosts have the correct time. I have
>no idea where to start looking to resolve this problem, but since we
>date/timestamp the users' data entry, they are understandably annoyed. I
>can see no obvious pattern in when the time will be correct and when it will
>be incorrect. [...]

There *is* a caveat for anyone whose code (either C or PL/SQL) must read or write system time (i.e., elapsed seconds since midnight of 01/01/1970) from or to an Oracle table. I discovered this while trying to figure out why schedule times I converted from Oracle DATEs to binary time_t values were not causing certain events in my systemlevel  C program to execute at the specified times.

If you convert an Oracle DATE to system time, or vice versa, and write it to a table, the value will not be the same as the exact same time stored by system-level routines. The size of the difference depends on whether daylight savings time is currently in effect. The critical element is the TZ (time zone) environment variable, which for us, here, is PST8PDT. During PST, which is currently in effect, the system time is 28860 seconds ahead of any Oracle DATE converted to system time by use of Oracle routines. During PDT, the computed difference would be 25260 seconds.

Given that PST is 8 hours behind Greenwich Mean Time (GMT), the difference is accounted for as 28800 + 60 seconds, or eight hours and 60 seconds. A fair guesstimate (at least I think so) is that the extra minute has something to do with the internal Oracle implementation for the DATE datatype, a seven-byte entity whose values for hour, minute and second can never be zero (minute and second range from 1 to 60, as documented in the Oracle Programmer's Guide to OCI), although I am far from certain about this.

What this means is that if a system program writes a system time either directly into an Oracle table or into a real-time view which is propagated back to an Oracle table, and then, later on, an Oracle Form or Report attempts to convert this value back into an Oracle DATE datatype, the DATE will be incorrect in the sense that if you apply the reverse conversion in Oracle, you will not get the same date-time which was originally written. Conversely, if a user makes a date-time entry into a Form which is converted to binary system time, written by Oracle to a table, and made available in real time for use by a system-level program, the system-level program will read a value eight hours and one minute earlier than the intended value.

The reason for this is that the C/Unix time routines store only GMT time in time_t variables, and supply a conversion to usable time elements in the tm structure with a call to localtime(). The TZ setting affects the current setting of SYSDATE also, but the TZ correction is built into Oracle and cannot be "undone". So Oracle binary system time is correct for the current locale, but not in comparison with a GMT-based value.

Moreover, Oracle does not use system base time in any fashion visible to the PL/SQL programmer, or even to the OCI user (that I know of). The closest binary implementation of a date to which the programmer has access is the Julian date. PL/SQL routines which need to convert between Oracle and system base time are pretty much forced to use strictly Oracle functions to effect the conversion based on that Julian date, in order to avoid hard-coded dependencies on locale, on Oracle version (or on quirks appurtenant to a particular version), or on external elements which may or may not exist on delivered, installed systems.

Now, the question is, who should do the correcting? Your PL/SQL code or your C code? You don't have much choice in Oracle because, although Oracle makes use of the TZ variable, Oracle does not make it visible to your PL/SQL code. You could use the NEW_TIME function to convert a DATE to the GMT equivalent, but, unfortunately, you have to know what time zone you are converting FROM because the function wants to know that, too. (Upgrade sales hint: add a "DEFAULT" argument to the "from" TZ parameter so that Oracle gets to supply its notion of the current TZ setting, thus relieving programmers of hard-coding locale dependencies.)

There are no Oracle global variables which supply you with time zone information (that I know of), and the only other option is to hard-code the "from" time zone, which means that your PL/SQL code can only execute in one time zone, not anywhere the system is shipped to. This is definitely a problem in Forms; there may be other workarounds available in Oracle which I am not aware of.

Your C code will not suffer these limitations, so C code which is reading binary system times known to have been written by Oracle should do the conversion before doing any computations with it. C code writing binary system times which will only be read by Oracle should apply the time zone conversion before storing the value. Another advantage is that C code can do this without hard-coding locale-dependent values. Everything needed is contained in the structure element tm.tm_isdat (daylight savings time flag) and the two time_t global variables (visible when you <#include time.h>) timezone and altzone.

Computing the adjustment to the binary system time is simple:

adjustment = (time_t) (pNow->tm_isdst ? altzone : timezone) + (time_t) 60;

Add this value to any value you READ which was converted to system time by way of Oracle services; subtract it from any value you will WRITE which will only be read by Oracle. (True while west of GMT.) Other implementations  involving both reading and writing will require some coordination  to avoid reading or writing improper values.

Another caveat: I'm far from being an Oracle guru of any sort, so I may be totally wrong on a lot of things -- but I spent no small amount of time tracking down the disparity between what you can compute to *represent* Oracle-based system time and a UNIX system's idea of system time. YMMV.

Karla Johnson

-- 
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
Karla Johnson                       |      Internet: karjohn_at_kincyb.com
S/W Engr., Informax Data Systems    |                  or ab803_at_lafn.org
Los Angeles, California             |   Standard disclaimers, ad nauseam