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 agin, 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?
In the questionable cases, you can make out an 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 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.
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>
: "@(#)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: Mon Mar 31 12:59:22 PST 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 (5190 characters)'
sed -e 's/^X//' >ivconv.ec <<'SHAR-EOF'
X/*
X@(#)File: ivconv.ec
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#include "ivconv.h"
X#include "esqlutil.h"
X
X#ifndef lint
Xstatic const char sccs[] = "@(#)ivconv.ec 1.1 97/03/31";
X#endif
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 return(rc);
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 o