[andrewhallam]

Does anyone out there know if Informix has an equivalent of the TIMESTAMPDIFF function?

I'm converting our code from SQL SERVER to INFORMIX and have got around similar problems by writing my own functions (with some help from you guys).

Now I could spend some time and do an equivalent for this but it would be quite complex what with varying months days and leap years etc so I was wondering if anyone out there has found a solution already.

For example I have a table (stockbatches) with 1 row with:
receiveddate = '2005-06-01 00:00:00'
createddate = '2006-06-20 00:00:00'

The below SQL produces a result of 384

select {fn TIMESTAMPDIFF(SQL_TSI_DAY, receiveddate, createddate)}
from stockbatches

thanks for any help on this.

Andy
ahmatexeldotcodotuk

[ceinma]

Just convert to date data type...

create temp table tp01(
dt1 datetime year to second,
dt2 datetime year to second)
;
insert into tp01 values ("2005-06-01 00:00:00", "2006-06-20 00:00:00")
;
select *, (date(dt1)-date(dt2)) from tp01
;

the result:

dt1                 dt2                 (expression)

2005-06-01 00:00:00 2006-06-20 00:00:00       -384

[andrewhallam]

Thanks again ceinma.

With that I've created my own Informix version of TIMESTAMPDIFF supporting DAY, MONTH and YEAR (all that I require - for now...).

>>>
create function timestampdiff (type char(20), pDate1 datetime year to second, pDate2 datetime year to second) returning integer;

if type = 'SQL_TSI_DAY' then
return date(pDate2) - date(pDate1);
elif type = 'SQL_TSI_MONTH' then
return (month(pDate2) - month(pDate1)) + ((year(pDate2) - year(pDate1)) * 12);
elif type = 'SQL_TSI_YEAR' then
return year(pDate2) - year(pDate1);
end if;

end function
<<<

And so when running Informix my runtime parser extracts "{fn TIMESTAMPDIFF(SQL_TSI_DAY, createddate, receiveddate)}" and places quotes around SQL_TSI_DAY in order to call my function.

One last quick question, you can see I've had to pass SQL_TSI_DAY as a string - do you know if it is possible to globally define such variables in Informix? If it is then I would not have to alter the SQL string at all at runtime.

thanks

Andy

[andrewhallam]

I'll elaborate a little more on my last post:
>>>
One last quick question, you can see I've had to pass SQL_TSI_DAY as a string - do you know if it is possible to globally define such variables in Informix? If it is then I would not have to alter the SQL string at all at runtime.
<<<

By variables I mean constants. So if I could do something along the lines of:

create constant SQL_TSI_DAY = 1;
create constant SQL_TSI_MONTH = 2;
create constant SQL_TSI_YEAR = 3;

- then I could call my timestampdiff function with SQL_TSI_DAY and not as a string of 'SQL_TSI_DAY'.

Basically I don't want to have to alter the original SQL statement if at all possible, I want to leave it as:

select {fn TIMESTAMPDIFF(SQL_TSI_DAY, createddate, receiveddate)}
from stockbatches

thanks

Andy

[ceinma]

No, this is not possible...

[andrewhallam]

Using my above timestampdiff function I get an error with the following:

select receiveddate, createddate, {fn NOW()}, {fn TIMESTAMPDIFF2('SQL_TSI_DAY', createddate, {fn NOW()})}
from stockbatches

error is:

"A syntax error has occurred."

Seems that {fn NOW()} does not produce a date object or something.

Anyone know how to fix this?

thanks in advance

Andy

[ceinma]

replace now() for current