"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.