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: > * >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 =========================================================================== 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: > >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 : "@(#)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 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 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.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 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