Subject: Number of hours from interval, how?
From: [email-masked] (Jonathan Leffler)
Date: 22 May 1997 10:08:11 -0400
Newsgroups: comp.databases.informix

>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 ([email-masked]) #include <disclaimer.h>

===========================================================================

From: [email-masked] (Jonathan Leffler)
Date: 31 Mar 1997 16:43:06 -0500
Subject: Re: Number of months...

>From: [email-masked] (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 ([email-masked]) #include <disclaimer.h>

:	"@(#)shar.sh	1.9"
#! /bin/sh
#
#	This is a shell archive.
#	Remove everything above this line and run sh on the resulting file.
#	If this archive is complete, you will see this message at the end:
#	"All files extracted"
#
#	Created: Wed May 21 17:29:36 PDT 1997 by johnl at Informix Software Ltd.
#	Files archived in this archive:
#	ivconv.ec
#	ivconv.h
#
#--------------------
if [ -f ivconv.ec -a "$1" != "-c" ]
then echo shar: ivconv.ec already exists
else
echo 'x - ivconv.ec (8423 characters)'
sed -e 's/^X//' >ivconv.ec <<'SHAR-EOF'
X/*
X@(#)File:            ivconv.ec
X@(#)Version:         1.4
X@(#)Last changed:    97/05/21
X@(#)Purpose:         Convert interval to decimal values
X@(#)Author:          J Leffler
X@(#)Copyright:       (C) JLSS 1997
X@(#)Product:         :PRODUCT:
X*/
X
X/*TABSTOP=4*/
X
X#include <assert.h>
X#include "ivconv.h"
X
X#ifndef lint
Xstatic const char sccs[] = "@(#)ivconv.ec	1.4 97/05/21";
X#endif
X
X/*
X** Convert a very large interval into a number of seconds.
X**
X** The basic conversion overflows if there are more than 1.0E9 seconds in
X** the interval, which corresponds to 11574 days, or over 31 years.  The
X** function iv_extra_seconds() caters for the overflow by calculating the
X** number of whole days in the interval, multiplying that by 86400
X** (24*60*60), and then calculating the number of seconds in the non-DAY
X** portion of the interval.  This is incredibly messy, which is why it is
X** only done if the initial conversion attempt fails.
X*/
Xstatic int iv_extra_seconds(intrvl_t *iv, dec_t *result)
X{
X	intrvl_t i1;
X	intrvl_t i2;
X	int rc;
X	dec_t t1;
X	dec_t t2;
X
X	/* Convert input interval to INTERVAL DAY(9) to DAY */
X	i1.in_qual = TU_IENCODE(9, TU_DAY, TU_DAY);
X	rc = invextend(iv, &i1);
X	assert(rc == 0);
X	/* 1000000 - Divisor to convert INTERVAL DAY TO DAY to number of days */
X	/*   86400 - Multiplier to convert days to seconds */
X	/* Hence composite multiplier is 0.086400 */
X	rc = deccvasc("0.086400", sizeof("0.086400") - 1, &t1);
X	assert(rc == 0);
X	rc = decmul(&i1.in_dec, &t1, &t2);
X	assert(rc == 0);
X	/* t2 contains the number of seconds due to the number of days */
X	/* Deduce interval corresponding to iv - i1 */
X	rc = decsub(&iv->in_dec, &i1.in_dec, &i1.in_dec);
X	assert(rc == 0);
X	i1.in_qual = TU_IENCODE(2, TU_HOUR, TU_F5);
X	/* i1 now contains the INTERVAL HOUR TO FRACTION(5) part of iv */
X	/* Convert i1 into INTERVAL SECOND TO FRACTION(5) */
X	i2.in_qual = TU_IENCODE(9, TU_SECOND, TU_F5);
X	rc = invextend(&i1, &i2);
X	assert(rc == 0);
X	/* Add the two numbers of seconds together for the final answer. */
X	rc = decadd(&t2, &i2.in_dec, result);
X	assert(rc == 0);
X	return(rc);
X}
X
X/*
X** Convert a DAY..FRACTION interval to seconds.
X** The basic conversion overflows if there are more than 1.0E9 seconds, but
X** that corresponds to 11574 days, or over 31 years.  The error code
X** generated for that is -1265 and the function iv_extra_seconds() caters
X** for that.  Unfortunately, you can't cheat with invextend() by giving
X** ni.in_qual a length of 20; that yields error -1268 (invalid qualifier).
X*/
Xstatic int iv_total_seconds(intrvl_t *iv, dec_t *result)
X{
X	intrvl_t ni;
X	int rc;
X
X	ni.in_qual = TU_IENCODE(9, TU_SECOND, TU_F5);
X	if ((rc = invextend(iv, &ni)) != 0)
X	{
X		if (rc == -1265)
X			rc = iv_extra_seconds(iv, result);
X		return(rc);
X	}
X	*result = ni.in_dec;
X	return(0);
X}
X
X/* Convert a YEAR/MONTH interval to an integral number of months */
X/*
X** NB: The in_dec component of a YEAR/MONTH interval is a fixed point number
X**     with 8 zeroes between the least significant digit of the interval and
X**     the decimal point (corresponding to the missing fields dd hh:mm:ss).
X*/
Xstatic int iv_total_months(intrvl_t *iv, dec_t *result)
X{
X	intrvl_t ni;
X	int rc;
X	dec_t divisor;
X
X	ni.in_qual = TU_IENCODE(9, TU_MONTH, TU_MONTH);
X	if ((rc = invextend(iv, &ni)) != 0)
X		return(rc);
X	if ((rc = deccvasc("100000000", sizeof("100000000")-1, &divisor)) != 0)
X		return(rc);
X	if ((rc = decdiv(&ni.in_dec, &divisor, &ni.in_dec)) != 0)
X		return(rc);
X	*result = ni.in_dec;
X	return(0);
X}
X
X/* Convert DAY/FRACTION interval to units of seconds */
Xint iv_seconds(intrvl_t *iv, dec_t *seconds)
X{
X	int fr = TU_START(iv->in_qual);
X
X	if (fr == TU_YEAR || fr == TU_MONTH)
X		return(-1268);	/* Invalid datetime or interval qualifier. */
X
X	return(iv_total_seconds(iv, seconds));
X}
X
X/* Convert DAY/FRACTION interval to units of minutes */
Xint iv_minutes(intrvl_t *iv, dec_t *minutes)
X{
X	int fr = TU_START(iv->in_qual);
X	dec_t seconds;
X	dec_t divisor;
X	int rc;
X
X	if (fr == TU_YEAR || fr == TU_MONTH)
X		return(-1268);	/* Invalid datetime or interval qualifier. */
X
X	if ((rc = iv_total_seconds(iv, &seconds)) != 0)
X		return(rc);
X	if ((rc = deccvint(60, &divisor)) != 0)
X		return(rc);
X	return(decdiv(&seconds, &divisor, minutes));
X}
X
Xint iv_hours(intrvl_t *iv, dec_t *hours)
X{
X	int fr = TU_START(iv->in_qual);
X	dec_t seconds;
X	dec_t divisor;
X	int rc;
X
X	if (fr == TU_YEAR || fr == TU_MONTH)
X		return(-1268);	/* Invalid datetime or interval qualifier. */
X
X	if ((rc = iv_total_seconds(iv, &seconds)) != 0)
X		return(rc);
X	if ((rc = deccvint(60 * 60, &divisor)) != 0)
X		return(rc);
X	return(decdiv(&seconds, &divisor, hours));
X}
X
Xint iv_days(intrvl_t *iv, dec_t *days)
X{
X	int fr = TU_START(iv->in_qual);
X	dec_t seconds;
X	dec_t divisor;
X	int rc;
X
X	if (fr == TU_YEAR || fr == TU_MONTH)
X		return(-1268);	/* Invalid datetime or interval qualifier. */
X
X	if ((rc = iv_total_seconds(iv, &seconds)) != 0)
X		return(rc);
X	if ((rc = deccvint(24 * 60 * 60, &divisor)) != 0)
X		return(rc);
X	return(decdiv(&seconds, &divisor, days));
X}
X
X
X/* Require input INTERVAL in the YEAR-MONTH class */
Xint iv_months(intrvl_t *iv, dec_t *months)
X{
X	int fr = TU_START(iv->in_qual);
X
X	if (fr != TU_YEAR && fr != TU_MONTH)
X		return(-1268);	/* Invalid datetime or interval qualifier. */
X
X	return(iv_total_months(iv, months));
X}
X
Xint iv_years(intrvl_t *iv, dec_t *years)
X{
X	int fr = TU_START(iv->in_qual);
X	dec_t months;
X	dec_t divisor;
X	int rc;
X
X	if (fr != TU_YEAR && fr != TU_MONTH)
X		return(-1268);	/* Invalid datetime or interval qualifier. */
X
X	if ((rc = iv_total_months(iv, &months)) != 0)
X		return(rc);
X	if ((rc = deccvint(12, &divisor)) != 0)
X		return(rc);
X	return(decdiv(&months, &divisor, years));
X}
X
X#ifdef TEST
X#include <stdio.h>
X#include "sqlhdr.h"
X
X#define DIM(x)	(sizeof(x)/sizeof(*(x)))
X
Xstatic char *df_intervals[] =
X{
X	"0 0:0:0.0",
X	"0 0:0:0.1",
X	"0 0:0:1.0",
X	"0 0:1:0.0",
X	"0 1:0:0.0",
X	"1 0:0:0.0",
X	"1234 23:32:43.98765",		/* Seconds =      +106702363.98765 */
X	"12345 23:32:43.98765",		/* Seconds =     +1066692763.98765 */
X	"-1234 23:32:43.98765",		/* Seconds =      -106702363.98765 */
X	"-12345 23:32:43.98765",	/* Seconds =     -1066692763.98765 */
X	"999999999 23:59:59.99999",	/* Seconds = +86399999999999.99999 */
X};
X
Xstatic char *ym_intervals[] =
X{
X	"0-0",
X	"0-1",
X	"1-0",
X	"2000-03",
X	"-2000-03",
X};
X
Xstatic char ofmt[] = "++++++++++++++&.&&&&&&&&&&&&&&&";
X
Xstatic void int_error(const char *s, int i)
X{
X	fprintf(stderr, "%s: rc = %d\n", s, i);
X	exit(1);
X}
X
Xstatic void test_df_interval(char *str)
X{
X	intrvl_t iv;
X	dec_t dp;
X	int n;
X	char	buffer[BUFSIZ];
X
X	printf("initial string     = %s\n", str);
X	iv.in_qual = TU_IENCODE(9, TU_DAY, TU_F5);
X	if ((n = incvasc(str, &iv)) != 0)
X		int_error("initial conversion failed", n);
X	intoasc(&iv, buffer);
X	printf("DAY/FRACTION value = %s\n", buffer);
X
X	if ((n = iv_seconds(&iv, &dp)) != 0)
X		int_error("conversion to seconds failed", n);
X	n = rfmtdec(&dp, ofmt, buffer);
X	printf("converted seconds  = %s\n", buffer);
X
X	if ((n = iv_minutes(&iv, &dp)) != 0)
X		int_error("conversion to minutes failed", n);
X	n = rfmtdec(&dp, ofmt, buffer);
X	printf("converted minutes  = %s\n", buffer);
X
X	if ((n = iv_hours(&iv, &dp)) != 0)
X		int_error("conversion to hours failed", n);
X	n = rfmtdec(&dp, ofmt, buffer);
X	printf("converted hours    = %s\n", buffer);
X
X	if ((n = iv_days(&iv, &dp)) != 0)
X		int_error("conversion to days failed", n);
X	n = rfmtdec(&dp, ofmt, buffer);
X	printf("converted days     = %s\n", buffer);
X
X}
X
Xstatic void test_ym_interval(char *str)
X{
X	intrvl_t iv;
X	dec_t dp;
X	int n;
X	char	buffer[BUFSIZ];
X
X	printf("initial string   = %s\n", str);
X	iv.in_qual = TU_IENCODE(9, TU_YEAR, TU_MONTH);
X	if ((n = incvasc(str, &iv)) != 0)
X		int_error("initial conversion failed", n);
X	intoasc(&iv, buffer);
X	printf("YEAR/MONTH value = %s\n", buffer);
X
X	if ((n = iv_months(&iv, &dp)) != 0)
X		int_error("conversion to months failed", n);
X	n = rfmtdec(&dp, ofmt, buffer);
X	printf("converted months = %s\n", buffer);
X
X	if ((n = iv_years(&iv, &dp)) != 0)
X		int_error("conversion to years failed", n);
X	n = rfmtdec(&dp, ofmt, buffer);
X	printf("converted years  = %s\n", buffer);
X}
X
Xint main(int argc, char **argv)
X{
X	int i;
X
X	printf("\nTesting INTERVAL DAY/FRACTION values\n");
X	for (i = 0; i < DIM(df_intervals); i++)
X		test_df_interval(df_intervals[i]);
X
X	printf("\nTesting INTERVAL YEAR/MONTH values\n");
X	for (i = 0; i < DIM(ym_intervals); i++)
X		test_ym_interval(ym_intervals[i]);
X
X	return(0);
X}
X
X#endif /* TEST */
SHAR-EOF
chmod 444 ivconv.ec
if [ `wc -c <ivconv.ec` -ne 8423 ]
then echo shar: ivconv.ec unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f ivconv.h -a "$1" != "-c" ]
then echo shar: ivconv.h already exists
else
echo 'x - ivconv.h (888 characters)'
sed -e 's/^X//' >ivconv.h <<'SHAR-EOF'
X/*
X@(#)File:            ivconv.h
X@(#)Version:         1.1
X@(#)Last changed:    97/03/31
X@(#)Purpose:         Convert interval to decimal values
X@(#)Author:          J Leffler
X@(#)Copyright:       (C) JLSS 1997
X@(#)Product:         :PRODUCT:
X*/
X
X/*TABSTOP=4*/
X
X#ifndef IVCONV_H
X#define IVCONV_H
X
X#ifdef MAIN_PROGRAM
X#ifndef lint
Xstatic const char ivconv_h[] = "@(#)ivconv.h	1.1 97/03/31";
X#endif	/* lint */
X#endif	/* MAIN_PROGRAM */
X
X#include <datetime.h>
X
X/* Require input INTERVAL in the DAY-SECOND class */
Xextern int iv_seconds(intrvl_t *iv, dec_t *seconds);
Xextern int iv_minutes(intrvl_t *iv, dec_t *minutes);
Xextern int iv_hours(intrvl_t *iv, dec_t *hours);
Xextern int iv_days(intrvl_t *iv, dec_t *days);
X
X/* Require input INTERVAL in the YEAR-MONTH class */
Xextern int iv_months(intrvl_t *iv, dec_t *months);
Xextern int iv_years(intrvl_t *iv, dec_t *years);
X
X#endif	/* IVCONV_H */
SHAR-EOF
chmod 444 ivconv.h
if [ `wc -c <ivconv.h` -ne 888 ]
then echo shar: ivconv.h unpacked with wrong size
fi
# end of overwriting check
fi
echo All files extracted
exit 0