Subject: Number of hours from interval, how?
From: johnl@informix.com (Jonathan Leffler)
Newsgroups: comp.databases.informix
Date: 22 May 1997 10:08:11 -0400
>maybe the following is a FAQ, but I couldn't find something
>about it in that document, so here goes:
>
>Summary:
>How do I get the number of hours from a datetime difference (an interval)
>as integer value?
With difficulty.
>Longer Explanation:
>
>Example Table:
>
>CREATE TABLE mytable (
> mykey CHAR(10),
> mydatetime DATETIME YEAR TO FRACTION(3)
>);
>
>The value of mydatetime is somewhere in the future. I want to compute the
>number of hours this point of time is away from now. I can do something
>like:
>
> SELECT EXTEND (mydatetime, YEAR TO HOUR) - CURRENT YEAR TO HOUR
> FROM mytable
> WHERE mykey = 'ABC';
>
>This gives an INTERVAL DAY TO HOUR(?) value of let's
>say '10 13', saying the difference is ten days and 13 hours.
>That is the 'nearest' I could get so far.
>
>But I wish to have the number of hours (in this example 253).
>How To?
To get this to work, you need the interval equivalent of the EXTEND
function -- something that allows you to control the type of interval that
is computed. There isn't such a beastie that I know of in the engines...
>The value is needed for further computing in a function that computes kind
>of a 'most urgent' entry. A computation like:
> <constant weight factor> * <number of hours left>
>is part of this function (amongst others).
>
>I like to create a view which has a column that gives me this number of
>hours as an integer value. I also tried writing a SPL-Procedure to fill
>this column but with no success.
I think an SP should work if done correctly:
CREATE PROCEDURE hours_between(d1 DATETIME YEAR TO HOUR, d2 DATETIME YEAR TO HOUR)
RETURNING INTERVAL HOUR(9) TO HOUR;
DEFINE n INTERVAL HOUR(9) TO HOUR;
LET n = d1 - d2;
RETURN n;
END PROCEDURE;
SELECT CURRENT YEAR TO HOUR,
hours_between(CURRENT YEAR TO HOUR, DATETIME(1997-01-01 00) YEAR TO HOUR)
FROM SysTables
WHERE tabid = 1;
1997-05-21 17 3377
Additionally, I'm taking this opportunity to repost an updated version of
some code I originally posted at the end of March 1997 which deals with a
similar problem at the application level -- it allows you to determine the
number of seconds, or minutes, or hours, or days in DAY/FRACTION interval
or any subset of those fields (or the number of months or years for
YEAR/MONTH interval). I think the code should be useful. I've not written
I4GL interfaces to the code, but it wouldn't be very hard to do. The
message includes most of the original question and a slightly edited
version of my original answer, plus the updated code.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
===========================================================================
From: johnl@informix.com (Jonathan Leffler)
Date: 31 Mar 1997 16:43:06 -0500
Subject: Re: Number of months...
>From: mrh@panix.com (Michael Hoffman)
>Date: 31 Mar 1997 11:11:19 -0500
>X-Informix-List-Id: <news.35977>
>
>OK, this may be the simplest question I've ever posted, but, then again, it
>may be as tough as we've found.
It's about as tough as you found...
>What we have are 2 dates and 2 datetimes. We need to find the number of
>months between each of the dates and each of the datetimes. Since there
>is no defined "MONTH" function, we have had to come up with our own.
One reason there's no defined MONTH function (in the sense you mean it,
anyway -- the builtin function MONTH() returns the number of the month of
the year for a given DATE (or DATETIME value which includes the month
component)) is that there is no standard definition of what the difference
between two dates in terms of months means.
>They seem extremely cludgy and inefficient. I am hoping the gurus out
>there will have come across this in the past and can shed some light. I
>would post our code, but it's almost embarrassing! :-} We went so far as
>to parse the date string. As I said, it is ugly code.
This is something I've been mulling over for a few years now, and there are
several aspects to the problem, and I enclose a limited solution below.
One of the main problems is defining what is meant by the number of months
between two dates. Once you've defined what is meant, most of the rest
falls into place fairly easily (at least, by comparison with the definition
phase).
Consider the following date pairs, and specify how many months have elapsed
in each case:
1-Jan-1997 31-Jan-1997 0 or 1?
31-Jan-1997 31-Jan-1997 0
31-Jan-1997 1-Feb-1997 0 or 1?
31-Jan-1996 28-Feb-1997 1
31-Jan-1997 1-Mar-1997 1 or 2?
31-Jan-1996 28-Feb-1996 1
31-Jan-1996 29-Feb-1996 1
31-Jan-1996 1-Mar-1996 1 or 2
1-Jan-1997 30-Apr-1997 3 or 4?
With most of the questionable cases, you can make out a semi-reasonable
argument for either value. If you don't agree, I don't think you've
thought hard enough about the problem.
What I have provided below is some code that handles a somewhat different
issue, but nonetheless something which is frequently requested. The code
is 'ESQL/C' to simplify the compilation (the ESQL/C compiler provides the
correct -I option on the command line). There are 6 externally visible
functions, as listed in the ivconv.h header:
iv_seconds() returns you a decimal number of seconds (including fractions)
in any interval of the DAY to FRACTION subset.
iv_minutes() returns the decimal number of minutes (including fractions)
in any interval of the DAY to FRACTION subset.
iv_hours() returns the decimal number of hours (including fractions)
in any interval of the DAY to FRACTION subset.
iv_days() returns the decimal number of days (including fractions)
in any interval of the DAY to FRACTION subset.
iv_months() returns you a decimal number of months (no fractions)
in any interval of the YEAR to MONTH subset.
iv_years() returns you a decimal number of years (including fractions)
in any interval of the YEAR to MONTH subset.
Note that this code was originally submitted to c.d.i on 1997-03-31, but
I've upgraded it since then so that even if the input interval is:
INTERVAL(999999999 23:59:59.99999) DAY(9) TO FRACTION(5)
you still get the correct values out of iv_seconds(), etc. The code to
handle this is not pretty, but it only gets invoked when necessary. It's
only necessary for intervals over 31.7 years, so it will seldom be used.
Note that the input intervals are not labelled 'const' solely because the
underlying function libraries do not label them as const. In practice, the
code does not modify the input interval values.
If you manage to derive an INTERVAL YEAR TO MONTH which satisfies your
difference criterion, then you can use iv_months() to convert to a number
of months, but subtracting two DATETIME YEAR TO DAY values gives you an
INTERVAL DAY(8) TO DAY value. Subtracting two DATETIME YEAR TO MONTH
values gives you an INTERVAL YEAR TO MONTH value -- that's OK providing
that's what you want. Taking each of the pairs of values above, converting
the value to DATETIME YEAR TO MONTH, and then subtracting, yields:
CREATE TABLE dt_example
(
d1 DATETIME YEAR TO DAY,
d2 DATETIME YEAR TO DAY
);
SELECT
d1 AS date_1,
d2 AS date_2,
EXTEND(d1, YEAR TO MONTH) AS dtym_1,
EXTEND(d2, YEAR TO MONTH) AS dtym_2,
EXTEND(d1, YEAR TO MONTH) - EXTEND(d2, YEAR TO MONTH) AS interval_1
FROM dt_example;
date_1 date_2 dtym_1 dtym_2 interval_1
1997-01-01 1997-01-31 1997-01 1997-01 0-00
1997-01-31 1997-01-31 1997-01 1997-01 0-00
1997-01-31 1997-02-01 1997-01 1997-02 -0-01
1996-01-31 1997-02-28 1996-01 1997-02 -1-01
1997-01-31 1997-03-01 1997-01 1997-03 -0-02
1996-01-31 1996-02-28 1996-01 1996-02 -0-01
1996-01-31 1996-02-29 1996-01 1996-02 -0-01
1996-01-31 1996-03-01 1996-01 1996-03 -0-02
1997-01-01 1997-04-30 1997-01 1997-04 -0-03
If that's what you want, then you've gotten a solution. If not, you've got
some work to do.
Yours
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
Download Latest Version
tpc-c-ifmx.tar.gz (392.7 kB)
