Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!mailrus!ames!amdahl!rtech!davek
From: davek@rtech.rtech.com (Dave Kellogg)
Newsgroups: comp.databases
Subject: Re: Ingres ESQL/C & Unix date formats
Keywords: INGRES, dates
Message-ID: <2546@rtech.rtech.com>
Date: 20 Nov 88 19:18:11 GMT
References: <42@melba.oz> <239@daitc.daitc.mil>
Reply-To: davek@rtech.UUCP (Dave Kellogg)
Organization: Relational Technology Inc, Alameda CA
Lines: 54

In article jkrueger@daitc.daitc.mil (Jonathan Krueger) writes:
>In article <42@melba.oz>, gnb@melba (Gregory N. Bond) writes:
>>
>>Ingres insists on considering dates as 26 character strings.
>
>No, INGRES dates are 12 byte integers encoding seconds since some base
>date about 200 years ago.
>

Actually, you're both 75% correct.  Dates are stored as 12 bytes in the 
database (but not as a 12 byte integer) and they are presented to the 
front-ends as 26 byte character strings.  Dates are actually a 12 byte data 
structure which does include number of seconds since the base time, but which
also includes flags to say whether it's an absolute date or interval (among 
other things).  For example, recall that both "12/5/78 23:14:23" and "3 hours"
may both be stored in a date column.

As Jonathan correctly notes, the frontends do not fully understand what dates
are, and thus they are handled in the FE as 26 character strings.  Thus, in
4GL fake-selects are generally used to exploit the date operators and functions.

With the advent of ADF (as Jonathan again notes), the FEs fully understand the
dates and their associated functions and operators. Thus, commands like 

	field := "12/12/87" + "45 days";

should work in the 4GL without running it to the DBMS by embedding it in a 
select (or retrieve for you quel fans!).

Being a VMS person I'm not totally familiar with the problems UNIX people 
generally have with dates and surprises in the changes from standard to 
daylight time.  Thus, I'm not 100% sure if this will help you, but here it 
goes...

INGRES provides three functions that allow you to store "dates" as int's.
They are: _date, _time, and _bintim.  All three take 'int' arguments and
_date converts an int to a date, _time converts an int to a time, and 
_bintim(0) is generally used to append "now" in integer seconds into the 
database.  

Storing dates in this way causes you to sacrifice direct use of all the fancy
date operators and functions, but if all you want is to do is store a date 
as an I4 number of seconds since a base time, then this should let you
do it.  I believe/hope that the base time used is the exact same as UNIX's 
which is 1/1/70.  A few experiments should confirm that, too.  

In any case, those functions are documented in SQL/QUEL reference manuals
and (for one example) are used in the system catalogs-- See "relation.relstamp"
for an example of such a column.

Cheers,

David Kellogg
Relational Technology (INGRES) New York City