Re: Date Arithmetic in SQL or 4GL
I would extend Jonathan's admirable write up with the comment that in most cases you can have an unlimited number of digits in an Interval for years or months or for days hours minutes or seconds. The exception, in my experience is when IMPLYING an interval. When I was trying to add a unix time (seconds since 1970) to a constant "01/01/1970" to find out the current date and time I had a problem if the number of seconds was more than 9 digits. I could add 999999999 UNITS sSECONDS but I could not add 1000000000 UNITS SECONDS. And as the current time in unix speak ios more than 1000000000 this gave me a small challenge. regards Malcolm ----- Original Message ----- From: "Jonathan Leffler" <jleffler@earthlink.net> To: <informix-list@iiug.org> Sent: Saturday, August 16, 2003 3:13 AM Subject: Re: Date Arithmetic in SQL or 4GL > Neil Truby wrote: > > > It's done by representing dates and times internally as a simple number. > > It's the number of milliseconds since the assasination of the Archduke > > Ferdinand, or something like that. Is this what you meant? > > > DATE values are the integer number of days since day 0 = 1899-12-31. > Day 1 was 1900-01-01. > > DATETIME is represented by a subset of a DECIMAL value which in full > would contain yyyymmddHHMMSS.FFFFF. > > INTERVAL is similar, but different - they're still stored in a > DECIMAL, but as a subset of: > > yyyyyyyyymm > mmmmmmmmm > > dddddddddHHMMSS.FFFFF > HHHHHHHHHMMSS.FFFFF > MMMMMMMMMSS.FFFFF > SSSSSSSSS.FFFFF > > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ > sending to informix-list
malcolm.iiug wrote: > I would extend Jonathan's admirable write up with the comment that in most > cases you can have an unlimited number of digits in an Interval for years or > months or for days hours minutes or seconds. The exception, in my > experience is when IMPLYING an interval. When I was trying to add a unix > time (seconds since 1970) to a constant "01/01/1970" to find out the current > date and time I had a problem if the number of seconds was more than 9 > digits. I could add 999999999 UNITS sSECONDS but I could not add 1000000000 > UNITS SECONDS. And as the current time in unix speak is more than > 1000000000 this gave me a small challenge. The upper limit on the N in INTERVAL LEADING(N) TO TRAILING in Informix is 9 digits - that's why I used 'yyyyyyyyymm', for example. This is inadequate for seconds and minutes - there are 12 digits worth of seconds between 9999-12-31 23:59:59 and 0001-01-01 00:00:00, and 10 digits worth of minutes. And, as of September 2001, more than 9 digits worth of seconds have elapsed since 1970-01-01 00:00:00, which is why the SPL code in the IIUG Software Archive that converts from a DATETIME to a Unix time - and vice versa - has to be so careful! For all other units (hours, days, months, years), 9 digits is overkill; you can represent an interval of years 5 orders of magnitude larger than the range of valid DATETIME values. And it is still just too small to be useful on truly geological or cosmological timescales (the age of universe is about 4.5 billion (10^9) years, I believe). > From: "Jonathan Leffler" <jleffler@earthlink.net> >>Neil Truby wrote: >> >>>It's done by representing dates and times internally as a simple number. >>>It's the number of milliseconds since the assasination of the Archduke >>>Ferdinand, or something like that. Is this what you meant? >> >> >>DATE values are the integer number of days since day 0 = 1899-12-31. >>Day 1 was 1900-01-01. >> >>DATETIME is represented by a subset of a DECIMAL value which in full >>would contain yyyymmddHHMMSS.FFFFF. >> >>INTERVAL is similar, but different - they're still stored in a >>DECIMAL, but as a subset of: >> >>yyyyyyyyymm >>mmmmmmmmm >> >>dddddddddHHMMSS.FFFFF >>HHHHHHHHHMMSS.FFFFF >>MMMMMMMMMSS.FFFFF >>SSSSSSSSS.FFFFF -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/