gnb@melba.bby.oz (Gregory N. Bond) (11/15/88)
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.
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.
date('') doesn't work, nor does int4(date_col).
What do others use here?
Greg.
P.S. Why the &^%&^*&^ doesn't ingres have a substring operator???????
--
Gregory Bond, Burdett Buckeridge & Young Ltd, Melbourne, Australia
Internet: gnb@melba.bby.oz.au non-MX: gnb%melba.bby.oz@uunet.uu.net
Uucp: {uunet,mnetor,pyramid,ucb-vision,ukc..}!munnari!melba.bby.oz!gnbpavlov@hscfvax.harvard.edu (G.Pavlov) (11/16/88)
In article <42@melba.oz>, gnb@melba.bby.oz (Gregory N. Bond) writes: > > P.S. Why the &^%&^*&^ doesn't ingres have a substring operator??????? Maybe I don't understand what you mean. But Ingres has a set of both string and date functions. greg pavlov, fstrf, amherst, ny
jkrueger@daitc.daitc.mil (Jonathan Krueger) (11/17/88)
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
bg0l+@andrew.cmu.edu (Bruce E. Golightly) (11/17/88)
You might try a slightly different set of operations. You are doing a
lot of conversions in the procedure as described which cannot help but
slow things down a lot. Try:
select int4(interval('secs', date('1/1/70')))
outside of the program loop, storing it in a static os global item. Then,
the statement
select int4(interval('secs', date_col))
Will extract the intercal in the row being manipulated at the time. Simple
arithmetic will then allow you to get the results you're looking for, I think.
Ingres substring operations are presently limited to left and right extractions.This, I agree, is a real pain. Having to do right(left(column_name, n),n) is
really annoying.
Hope this is of some help....
Bruce
Gregory Bond, Burdett Buckeridge & Young Ltd, Melbourne, Australia
Internet: gnb@melba.bby.oz.au non-MX: gnb%melba.bby.oz@uunet.uu.net
Uucp: {uunet,mnetor,pyramid,ucb-vision,ukc..}!munnari!melba.bby.oz!gnbdavek@rtech.rtech.com (Dave Kellogg) (11/21/88)
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
jas@ernie.Berkeley.EDU (Jim Shankland) (11/21/88)
In article <42@melba.oz> gnb@melba.oz.au (Gregory 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.
! The best solution I can find is something like
! [ick...]
! 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.
Here's one suggestion that may or may not work for you: forget the 'date'
datatype, and store your dates as i4's representing seconds since
1/1/70, GMT. You lose in the terminal monitor, where you find yourself
staring at large integers instead of anything meaningful to a human;
but your ESQL/C program gets to do whatever it wants with timezones,
formatting, etc.
Jim Shankland
jas@ernie.berkeley.edu
"Work, knowledge, and love are the wellsprings of human existence" -- Reich