Path: utzoo!attcan!uunet!husc6!mailrus!ncar!ames!lll-tis!daitc!jkrueger@daitc.daitc.mil
From: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: Ingres ESQL/C & Unix date formats
Message-ID: <239@daitc.daitc.mil>
Date: 16 Nov 88 18:23:03 GMT
References: <42@melba.oz>
Sender: jkrueger@daitc.daitc.mil
Reply-To: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Organization: Defense Applied Information Technology Center
Lines: 85
In-reply-to: gnb@melba.bby.oz (Gregory N. Bond)

In article <42@melba.oz>, gnb@melba (Gregory N. Bond) writes:
>I'm trying to use embedded SQL from C, calling the ingres database to
>extract and process data.  The problem I'm having is converting Ingres
>dates to unix time(3)-style dates (i.e. #secs since 1/1/70).
>
>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.

An operation INGRES defines on dates is generate printable rep, the
result of which is sent to front ends as a 26 character string.  A
complementary function INGRES defines on strings is parse date.

>The best solution I can find is something like
>	select int4(interval('secs', date_col - date('1/1/70')))
>	from ....
>which almost seems to work, but problems with timezones seem to make
>this unreliable (we just went on to DST here...) as Ingres is converting
>the '1/1/70' to local time....  Not to mention SLOW.

Your solution is close to the best possible.

INGRES date operators say that the difference between two absolute
dates is a relative date:
	A1 - A2 = R1
	16 Nov 1988 12:00:00 - 14 Nov 1988 11:59:20 ==> 40
	16 Nov 1988 15:00:00 - 14 Nov 1988 14:00:00 ==> 2 days 1 hour

But you're trying to interpret the result as an absolute date:
	A1 - 1 Jan 1970 00:00:00 ==> a1
	40 ==> 1 Jan 1970 00:00:40
	2 days 1 hour ==> 3 Jan 1970 01:00:00

So what's wrong with that?  Absolute dates have a notion of time zone.
Their printable rep might display it, for instance:
	16 Nov 1988 12:00:00 DST ==> 16 Nov 1988 09:00:00 PST
Relative dates have no notion of time zone:
	40 ==> 40, wherever you go and regardless of what the locals call
		   the date that occurs 40 seconds after 1 Jan 1970
	2 days 1 hour ==> 176400 seconds, wherever you go . . .

Hence your problem: whenever daylight savings time shifts your local
time an hour from GMT, interpreting the relative date as an absolute
date yields an incorrect answer.  To correct the problem, you could
enter a rule and a table of exceptions, much as time(3) does.

This problem is much on my mind because right now, I'm trying to feed
over 4000 time stamps generated by different operating systems around
the world into INGRES dates.  It would be nice to preserve time zone
information, for instance "when this event happened, at the place in
the world where it happened they all figured it was 2 in the morning".
But I don't want to lose temporal ordering, of course: "ten minutes
later another thing happened, it wasn't five hours earlier because it
happened halfway across the globe".  When the much-vaunted ADT
facility of INGRES 6.0 arrives, its value to us will be measured by
(among other things) its ability to extend the date type by operations
on it which parse, store, and generate time zone information.  The
rule and table of exceptions I suggest above would thus be migrated
into a definition of the type itself, making it non-subvertible.

In the meantime, you can simulate it by building the rule and table
into all interface software.  Or you can build it into one conversion
program and then stop using INGRES dates altogether, in favor of
time(3) style dates stored as int4's.  Sounds like that's your goal.
INGRES users have found performance improvements this way, at the
expense of larger date ranges, and programmer time: the back end can't
generate the printable rep or parse a printable rep and return the
int4 date, each front end must explicitly do this work.  Since QBF is
one front end that can't be taught how, and in general ABF-generated
front ends won't know how, this is a loss that must be traded off
against performance improvements.

>P.S. Why the &^%&^*&^ doesn't ingres have a substring operator???????

Beats me.  Sure would be useful.  It's simulated by macro expansion in
the terminal monitor.  Why not support it elsewhere?  While we're at
it, why not extended regular expression match?

	retrieve (trailblanks = sum(length(match(" +$", textcol))))

	retrieve (inparens = match("\(/[^)]+\\)", textcol)
		where t.textcol = "*[(?*)]*"

-- Jon