From: sfc@tpghq.com (Steve Caswell)
Subject: Re: Dateformat in 6.4
Organization: Palmer & Associates, Inc., Norcross, GA
Date: Thu, 12 Nov 1992 13:33:56 GMT

In article <1992Nov5.175319.10856@hsr.no> liland@hsr.no (Trygve Liland) writes:
>
>
>I have a problem with the date datatype in Ingres. I use Embedded SQL from
>my C program. Input to the database is OK, so is the data in the database, 
>but when it comes to output from the db and into my C variable I have a problem.
>
>I have tried to get the date as date_gmt, but it comes out 6 hours wrong, and
>that is not only in my C program but also in ISQL. My platform is HP 730,
>HP-UX 8.05. (That is 6 hours wrong GMT)
>
>Is there a way to get the date, just as it is in the db ?? I have tried most 
>off the possibillities in the manual, but it still doesn't comes out rigth.
>Is it a familiar problem ?, AND is there a solution to it ??
>
>Please respond by email, I rarly have the opportunity to read this group.
>Thanks in advance
>-- 
>+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+
>Trygve Liland 			e-mail : liland@hsr.no
>SoftWare Engineer		    or : liland@ifi.hsr.no
>Unique A/S
>N-4300 SANDNES			Phone  : +47 04 662841 (home)
>NORWAY				       : +47 04 624044 (Unique)	       
>
>		"Steal a litle and they throw you in yail,
>		  steal alot and they make you king" 
>				Bob Dylan.

The following information may help.  This is an Expert Note from the INGRES
Advisor explaining a problem INGRES has in returning dates that were input
during daylight savings time.



                         Document #: US-16480,EN
------------------------------------------------------------------------------

Major subject: os_config    Minor subjects: os_compatibility

Keywords: 



Abstract:
Daylight Savings Time: there is a bug in Ingres on all Unix 
platforms regarding date data that is stored during standard 
time and retrieved during daylight time, or vice versa. 
This document describes the general problem and then provides 
the Sun workstation specific workaround.


Expert note:
                Turning Off Automatic Daylight Savings Time
                      Conversion on a Sun Workstation

1.  Background

     On most versions of the Unix Operating System, the system  clock  will
automatically  have one hour added or subtracted from it when daylight sav-
ings time begins or ends, respectively.  Because of a bug in  Ingres,  this
causes  date  data  entered during Daylight Savings Time (DST) to be inter-
preted incorrectly during Standard Time (and vice versa).

     Ingres stores all dates in Greenwich Mean Time (GMT).  In order to  do
this,  an  Ingres  frontend  uses  a Unix system call to determine how many
minutes west of GMT it  is.   This  number  is  subtracted  (or  added,  if
appropriate)  from  any  date data that is entered into a database.  If the
local time zone is one that changes the hour for DST, then  the  number  of
minutes to subtract (or add) will depend on the time of year.  For example,
if an Ingres frontend is in the Pacific Time Zone, it  is  480  minutes  (8
hours)  west  of  GMT during Pacific Standard Time (PST) and 420 minutes (7
hours) west of GMT during Pacific Daylight Time (PDT).  Ingres asks Unix if
DST  is  currently in effect to determine if it should modify the time west
of GMT by an hour.

     The problem is best illustrated by an example.  Let's assume we're  in
the  Pacific  Time Zone and we enter the date '11-apr-91 12:00:00'.  Before
storing the date into a database, Ingres must convert it  to  GMT.   To  do
this,  it  asks Unix how many minutes west of GMT it is.  In this case, the
response is 480 minutes, so this amount is added to the time,  giving  '11-
apr-91  20:00:00'.   Now  Ingres  checks  to  see  if  DST is on.  If we're
currently in daylight time, Ingres will subtract an hour  from  this  time,
giving  '11-apr-91  19:00:00'.  If we're in standard time, Ingres will just
enter '11-apr-91 20:00:00'.  In general, the  same  conversion  is  applied
when retrieving the data so everything is consistent.

     The bug appears when Ingres retrieves  a  date  during  DST  that  was
entered  during  Standard  time  (or vice versa).  Using the above example,
let's assume '11-apr-91 12:00:00' was entered during Standard time.  There-
fore,  it  was  stored in the database as '11-apr-91 20:00:00'.  Now, let's
say we try to retrieve that date during DST.  Once the backend fetches  the
date,  the  frontend  will  convert  it  by first subtracting the number of
minutes west of GMT, giving '11-apr-91 12:00:00'.  It then checks to see if
DST is currently in effect and finds that it is.  Therefore, it adds 1 hour
to the time, giving '11-apr-91 13:00:00'.  This is not the  time  that  was



                              April 11, 1991





                                   - 2 -


originally entered and this, therefore, is the bug.


2.  Workaround

     The workaround is platform specific, but it involves  instructing  the
operating  system  to  not  automatically  update the system clock when DST
begins or ends.  This will guarantee  that  Ingres  always  does  the  same
conversion  for  any date that it has stored.  The disadvantage is that the
system administrator will have to manually update the system clock twice  a
year instead of having this done automatically by Unix.  This seems a small
price to pay compared to the advantages of having a consistent database.

     On a Sun, the method for turning off the automatic DST  update  is  to
instruct  Unix  to  use  a time zone which is the correct number of minutes
from GMT, but which doesn't change for DST.  For Suns running Sun OS 4.0 or
later, this is done with the "zic" utility.

3.  Zic

     Zic is a program that is available on Sun workstations running Sun  OS
release  4.0  or  greater.  It is a tool that allows a system administrator
to:

*    specify which time zone his machine is in.

*    create a new time zone.

     We need to use zic to do both of the above.  First, we must  create  a
new time zone, and then we must apply it.  To accomplish this, follow these
steps:

1.   Create a file (the name is unimportant) which contains a line  similar
     to the following:
          Zone  <TZ file name>  <GMT offset>  -  <TZ name>
     An example for PST might look like:
          Zone  PST8  -8  -  PST
     The names "PST8" and "PST" can be set to anything.

2.   Compile the file into a Unix time zone file:
          zic <filename>

3.   Make the system use the newly created time zone:
          zic -l <TZ file name>
     In the example from step 1, the command would look like:
          zic -l PST8

4.   You are now using a time zone that is the correct  number  of  minutes
     west of GMT, but which never modifies the system clock for DST.








                              April 11, 1991




Releases affected:     all(su4.u42) -   Releases not affected: 
                       all(sun.u42) -                          
Errors:                                                             
Bugs/SIRS: 14120, 14739, 14840                                         
------------------------------------------------------------------------------

-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."
Newsgroups: comp.databases,comp.databases.ingres