Hi Informixers,

in an ESQL/C-Program, I have to add a given amount of minutes
(represented as an integer value) to an existing datetime variable
(datetime year to minute).

In SQL, this would be very easy with the UNITS keyword, like in
SELECT datetime_column + 135 UNITS MINUTE ...

The UNITS keyword doesn't seem to exist in ESQL/C. When I use the
"dtaddinv()" function, do I really first have to convert the
minutes into an interval hour to minute variable (or in case
there are more than 1440 minutes, an interval day to minute
variable) and then apply that function, or is there an easier
method to accomplish this?

Maybe someone has a ready-to-use function to convert a number
of minutes into a suitable interval variable?

Regards, Richard

"Richard Spitz" <Richard.Spitz@med.uni-muenchen.de> wrote in message
news:uje5m0pt8akcncu2s2707e7s26cg98i55o@4ax.com...
> Hi Informixers,
>
> in an ESQL/C-Program, I have to add a given amount of minutes
> (represented as an integer value) to an existing datetime variable
> (datetime year to minute).
>
> In SQL, this would be very easy with the UNITS keyword, like in
> SELECT datetime_column + 135 UNITS MINUTE ...
>
> The UNITS keyword doesn't seem to exist in ESQL/C. When I use the
> "dtaddinv()" function, do I really first have to convert the
> minutes into an interval hour to minute variable (or in case
> there are more than 1440 minutes, an interval day to minute
> variable) and then apply that function, or is there an easier
> method to accomplish this?
>
> Maybe someone has a ready-to-use function to convert a number
> of minutes into a suitable interval variable?
>
> Regards, Richard


I have always found computing with date and time to be a big pain,
till I found a solution using Jonathan's Leffler's function.

For example to add 2 hours to current time, I would do the
following:

select CURRENT,unixtime_to_dt(to_unix_time(CURRENT)+7200)
from systables where tabid = 1

(expression) (expression)

2004-10-05 12:28:49.000 2004-10-05 14:28:49

The SQL uses two function to_unix_time which converts a datetime into unix
epoch time. Since it deals with seconds, I am adding 7200 for 2 hours. The
function unixtime_to_dt is the inverse of to_unix_time, that is, it converts
unix epoch time to an Informix datetime.

I find this approach extremely convenient. Somehow I find INTERVAL,
UNITS etc to add/subtract time inside a SQL unmaintainable.

You can search for Leffler's function in google or you can email me for the code.
BTW my code is idx 9.x specific because it uses int8.

Richard Spitz wrote:
> In an ESQL/C-Program, I have to add a given amount of minutes
> (represented as an integer value) to an existing datetime variable
> (datetime year to minute).
>
> In SQL, this would be very easy with the UNITS keyword, like in
> SELECT datetime_column + 135 UNITS MINUTE ...
>
> The UNITS keyword doesn't seem to exist in ESQL/C. When I use the
> "dtaddinv()" function, do I really first have to convert the
> minutes into an interval hour to minute variable (or in case
> there are more than 1440 minutes, an interval day to minute
> variable) and then apply that function, or is there an easier
> method to accomplish this?
>
> Maybe someone has a ready-to-use function to convert a number
> of minutes into a suitable interval variable?

Yes, you do have to create an interval type to deal with it.

You would normally use an INTERVAL MINUTE(9) TO MINUTE variable for
the number of minutes - so you don't have to convert more than 60
minutes into hours and minutes, or more than 1440 minutes into days,
hours and minutes.

I don't have the function pre-coded. At the IIUG, there is code in
intvl_conv that is the closest shot - it converts incoming intervals
into total number of seconds, or total number of minutes, or whatever,
as a decimal number with a fractional part too.

You probably need to do something like:

int iv_cvminutes(intvl_t *iv, int minutes)
{
int rc;
char buffer[24]; /* Big enough for 64-bit int */
sprintf(buffer, "%d", minutes);
iv->in_qual = TU_IENCODE(9, TU_MINUTE, TU_MINUTE);
rc = incvasc(buffer, iv);
return(rc);
}

I like to keep a local variable for the return value from incvasc() so
it is easier to track problems with a debugger -- you could eliminate
it and return the value from incvasc() directly if you prefer.

Note that generalizing this to handle any interval is easy (and you
could rewrite iv_cvminutes() in terms of this function if you preferred):

int iv_cvunits(intvl_t *iv, int units, int value)
{
int rc;
char buffer[24]; /* Big enough for 64-bit int */
sprintf(buffer, "%d", value);
iv->in_qual = TU_IENCODE(9, units, units);
rc = incvasc(buffer, iv);
return(rc);
}

Use: if ((rc = iv_cvunits(&my_interval, TU_MINUTES, i)) != 0)
...handle error...

Obviously, you need to supply headers, test code, etc. This also has
not been past the compiler - there could be bugs to be found.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler <jleffler@earthlink.net> wrote:

>> The UNITS keyword doesn't seem to exist in ESQL/C. When I use the
>> "dtaddinv()" function, do I really first have to convert the
>> minutes into an interval hour to minute variable (or in case
>> there are more than 1440 minutes, an interval day to minute
>> variable) and then apply that function, or is there an easier
>> method to accomplish this?
>>
>> Maybe someone has a ready-to-use function to convert a number
>> of minutes into a suitable interval variable?
>
>Yes, you do have to create an interval type to deal with it.
>
>You would normally use an INTERVAL MINUTE(9) TO MINUTE variable for
>the number of minutes - so you don't have to convert more than 60
>minutes into hours and minutes, or more than 1440 minutes into days,
>hours and minutes.

Thanks, also to Art S. Kagel for his helpful mail. As it turned
out, an INTERVAL MINUTE(4) TO MINUTE worked beautifully. It's a
bit awkward that there is no direct conversion function available
between "int" and "interval", I have to convert the "int" to a
string using "sprintf" and then use the "incvasc()" function to
get the interval, but that is just a little nuisance.

>I don't have the function pre-coded. At the IIUG, there is code in
>intvl_conv that is the closest shot - it converts incoming intervals
>into total number of seconds, or total number of minutes, or whatever,
>as a decimal number with a fractional part too.

Thanks for your code examples. The above was totally sufficient,
all I wanted was having to convert a number of minutes into
days, hours and minutes. I had misinterpreted the code example
for the incvasc() function in the ESQL/C manual.

Regards, Richard