Re: Set Timezone of Oracle Server

> I have studied as much of the Oracle doc's as I could stand and only found
> a weak time conversion SQL function. Oracle seems timezone illiterate.
> Surely there must be a standard solution to this problem.

I assure you that this is not the case. If you studied as much or Oracle Documentation you would have found the following :

• Queries run through SQL*Net

    If the query is run through SQL*Net, Oracle will return     the time zone based on the setting of the UNIX environment     variable "TZ" for the user who started the listener.

• Queries run without going through SQL*Net

    If the query is run locally, Oracle will return the time zone     based on the "TZ" environment variable setting of the user running     the query.

    With local queries, you can change the time zone for a particular user     by simply changing the setting of "TZ". To check the current setting,     issue the UNIX "date" command.

Unix Timezones

  Unix has a pretty good idea about timezones and if set up correctly and left
  alone will do the hard work for you. Unfortunately this is usually not the
  case. The usual scenario is as follows. The clocks have gone forward (or   backward) and the system clock, for some reason, is now out of step with   reality. So the system administrator moves the system clock with the date   command. This however, can have untoward side effects with programs which   are already running.

  The root cause is that the machine has not be set up properly to deal with   the shifts in time and no-one can be bothered to correct the setup. They   assume that they have to alter the time by hand, as they do with their own   watches. Even more confusion can be caused if the timezone information is   not correctly specified with the system then reverting to reporting the time
  with respect to some hard to determine default timezone.

  With respect to Oracle, these problems are usually noticed because the   database is reporting a different time to the system clock. That is, either
  sysdate or the time reported on the banners of the products are apparently   incorrect. These problems can usually be attributed to the incorrect setup of
  the timezone information. For example, on a System V machine as you will see
  later, the timezone information can be quite complex and if incorrect the   system cannot simply fail to return a time, some sort of value must be   returned. It is this default action which causes the confusion. If this   happens look at the time being returned, it is usually out of step by a value
  equal to the timezone's offset from the Greenwich Meridian. Or worse still,
  the timezone information may be specified correctly with respect to syntax,
  but may be semantically incorrect, i.e. setting up the timezone to be 999 hours
  east of Greenwich, which is legal but is nonsensical.

  As is normal in the Unix world distinctions can be drawn between the BSD and
  System V flavors and their hybrids. A simple statement would be that System
  V systems use just an environment variable and BSD/System V hybrids use both
  an environment variable and system files. However, it must be pointed out   that these distinctions tend to blur when examined closely as each   manufacturer adds their 'personal' touches. For detailed information on the
  various associated commands you should refer to your system's documentation.
  Possible entries to look at are timezone, date, ctime, gettimeofday, tzset and
  zic.

  System V

  On these machines the TZ environment variable is used to completely specify
  the timezone, no use is made of timezone rule files. Please refer to your   system's documentation for precise syntax details.

  Typically the TZ environment variable takes the form:

          stdoffset[dst[offset][,start[/time],end[/time]]]

  Where the various portions of the string have the following meaning:

  std and dst     Three or more characters that are used to designate the
                  standard (std) or summer (dst) time zones.  Only std is
                  required; if dst is missing then summer time does not
apply in
                  this zone.

  offset          This is the value which should be added to the local time
to
                  arrive at the Coordinated Universal Time (CUT).  The
offset
                  takes the form:

                                  hh[:mm[:ss]]

                  The offset following std is required.  If no offset
follows
                  dst then summer time is taken to be one hour ahead of
standard
                  time.  The offset can be signed, positive values are west
and
                  negative values east of the Prime Meridian.

  start and end These indicate when to change to and back from summer time, ie

                  start specifies when summer time begins and end when it
ends.
                  The format of these times can take one of the following:

                  Jn      The Julian day (1<=n<=365).  Leap days are not
                          counted, so February 28 is J59 and March 1 is J60.
                          February 29 can not be referenced.

                  n       The zero based Julian day (0<=n<=365).  Leap days
are
                          counted and February 29 can be referenced.

                  Mm.n.d  The nth d day of month m (1<=n<=5, 1<=m<=12,
0<=d<=6)
                          When n is 5 it refers to the last d day of the
month
                          m.  Day 0 is Sunday.

  time            The time field describes the time when the changes to and
from
                  summer time occur.  The time is always specified in the
current
                  time.  If no time is given it defaults to 02:00:00, in
                  addition no leading sign (+ or -) is allowed.

  NB If the start and end times are not specified then the points when summer
  time comes in and out of effect depends on the implementation of the operating
  system. Typically these days are the first Sunday in April and the last   Sunday in October (ie US transitional dates).

  In addition the use of Julian days to specify the time changes means that the
  timezone information must be updated every year. This is because the changes
  usually happen on a Sunday, and there is no guarantee that the Sunday will   fall on the same Julian day, eg the first Sunday in April is the 5th in 1992
  and is J96 in 1992, in 1993 it is the 4th, and is J95. The SQL statement,

          SELECT TO_CHAR(TO_DATE('04-APR-92'),'DDD') FROM DUAL;   can be used to determine the Julian day (1<=n<=366). Note that an incorrect
  use is being made of 'Julian day' as this is not the day's position in the   year, but should be in actual fact the number of days since January 1st, 4712
  BC!

  So an example of a simple timezone could be:

          GMT0BST   which is a value which could be used for the United Kingdom except that this
  would change the time on the wrong days of the year. A more complete and   correct specification of the timezone is:

          GMT00:00:00BST-01:00:00,M3.5.0/02:00:00,M10.4.0/02:00:00   which can be simplified by accepting the default values to:

          GMT0BST,M3.5.0,M10.4.0   Some systems also require the use of quotes around the timezone value. Note
  that the GMT0BST specification is not accurate enough as the times when summer
  time goes in and out of effect are typically not the same as the default times.

  British summer time begins on the last Sunday in March and ends on the first
  Sunday in October which is later than the 23rd, ie the fourth Sunday in   October as it is possible to have five Sundays in October (eg in 1993).   Therefore using GMT0BST as the specification of the timezone could lead to the
  system clock moving to and from summer time after the changes actually took
  place.

  BSD/Sys V Hybrids

  On these machines (Sun, Dec Risc-Ultrix for example) the TZ environment   variable, when set, refers the system to a file containing the rules for the
  particular timezone chosen. The variable either refers to the file using an
  absoulte path, eg /usr/lib/zoneinfo/EST, or just to the file which is taken to
  be relative to the system time information directory. When TZ is not set then
  a file called localtime is looked for, usually this has been created during
  installation or by the system administrator simply by copying the file which
  refers to their timezone to localtime. If the pre-generated timezone files
  do not include your timezone then a set of source files are also provided   which can be compiled by the zic command.

  Details of the format of these source files is not given here as a complete
  set of timezone source files are provided. However, if the System V format of
  TZ is used on a hybrid machine then some confusion can result. This stems   from the system's behaviour when it cannot find the timezone file specified,
  ie if TZ is set to PST, then, if the timezone file PST exists (as it usally
  does) then the correct time will be reported. However, if TZ is set to   PST8PDT and no such file exists then the system will revert to some default
  timezone, possibly determined from the kernel, or simply just GMT. In these
  cases it is necessary to create such a file, and this is where some alteration
  of the timezone source files is necessary.

  How do we go about creating the new file? Firstly it is not simply a case of
  renaming an existing file, eg if TZ is going to be set to PST8PDT, the file
  PST cannot be renamed PST8PDT, as this file does not contain information about
  the PST8PDT timezone. Instead a LINK is added to the source file, in this   case northamerica, which links the new TZ name to an existing zone in the   file. In this example the line which needs to be added, usually at the end of
  the file, is:

          Link US/Pacific PST8PDT

  where US/Pacific is the timezone defined in the file and PST8PDT is the name
  of the file we wish to create. The file can be recompiled using zic and the
  PST8PDT file will be created.

  Now a couple of examples of how the time is determined on some systems. This
  list is definitely not meant to be exhaustive, as it would take too long   to check this information against every machine Oracle runs against.

  Sun (BSD/Sys V)

  The timezone rule directory is /usr/share/lib/zoneinfo. The date command will
  report the system's current time using the value of TZ as the name of the   timezone. If TZ is not set at all then date first looks for a file called   localtime, if that doesn't exist then it looks for the GMT file, and if that
  doesn't exist it reports the current system time as GMT.

  Sun Solaris 2.X

  At a system level the TZ environment variable is set in /etc/default/init   which is read by the init process at boot time to set machine specific   environment variables. If a TZ entry is added to this file the machine will
  need to be rebooted for it to take effect. The other file in which the   timezone can be set is in /etc/default/login. In this file the TIMEZONE   variable must be set to the TZ string that is required. This change is picked
  up on the next login.

  Dec Risc-Ultrix (BSD/Sys V)

  The timezone rule directory is /etc/zoneinfo. If the file specified by TZ does
  not exist (or involves a circular reference, ie TZ=GMT and the file GMT refers
  to the file GMT,...) or if TZ is not set and the localtime file does not exist
  then the timezone specified when the unix kernel was built is used. This   value takes the form:

          x dst y

  where x is the offset from the prime meridian, and y the amount of daylight
  saving to apply when summer time is in effect.

  SCO (Sys V)

  Sco Unix provides a utility /etc/tz which can be used to construct timezones.
  The TZ environment variable is set in the /etc/TIMEZONE file, as with most   System V Release 4 systems. With SCO this file must exist as it is used   during the
  boot procedure to set the TZ for the system. Please refer to the manual page
  for timezones for the various formats which can be used.

  Sequent DYNIX/ptx (Sys V)

  Again the TZ environment variable is setup in /etc/TIMEZONE. This file is read
  by /etc/profile during the login procedure to setup the user's timezone.

  IBM RS/6000 AIX 3.2.X (Sys V)

  The TZ environment variable is setup in /etc/environment which is used to setup
  the user's environment on logon. The settings in this file can be overridden
  by the user's$HOME/.env file.

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

Jerry Althoff <jerome50_at_ix.netcom.com> wrote in message news:jerome50-0312992002030001_at_sdn-ar-002ohdaytp087.dialsprint.net...
> We will have a database with clients scattered across US timezones. Much
> of the time based data in the database will be stored in GMT timezone. We
> need to timestamp various transactions from the clients using SYSDATE
> which also need to be in GMT for correllation. How can we get the timezone
> of the Solaris 8.0.5 server set to GMT?
>
> When I test with a SQL*NET (not Net8) client with its TZ environment
> variable set to GMT, the database, on an Eastern Standard Time (EST)
> machine, stores the value of SYSDATE as EST. However, if on the same EST
> server, I set the client's TZ to GMT and use SQLPLUS, the SYSDATE is
> stored as GMT.
>
> How can I reboot the Solaris Oracle 8.0.5 instance only with the TZ=GMT?
> I added TZ=GMT to the /etc/rc.d script, but it had no effect. I hesitate
> to use the Unix date command to change the time and timezone of the entire
> server because that will mess with non-Oracle apps and other independent
> Oracle instances.
>
> I have studied as much of the Oracle doc's as I could stand and only found
> a weak time conversion SQL function. Oracle seems timezone illiterate.
> Surely there must be a standard solution to this problem.
Received on Fri Dec 03 1999 - 23:02:54 CST