DateTime Calculations

[djburnheim]

Not sure I'm putting this post in the right forum but hope somebody can help. I have a Informix database that I'm reporting on with three columns I'm trying to do calculations on but am having trouble...

agerecord.Secondstart - datetime
idrecord.Intialstart - datetime
agerecord.Length - integer (number of seconds)

My calculation would be :
agerecord.Secondstart - idrecord.Intialstart + agerecord.Length

But I can't workout how to do the calculations on datetime. I thought I might be able to use DATEDIFF but this doesn't seem to work. The select statement below returns the data I want and I can then do the calculation in Excel by converting all the dates to numbers but I would like to do the calculations when I query the database.

any suggestions?

Thanks
Dave


SELECT agerecord.Secondstart, idrecord.Intialstart, agerecord.Length
FROM informix.agerecord agerecord, informix.idrecord idrecord
WHERE agerecord.id = idrecord.id
AND ((idrecord.Intialstart Between {ts '2003-06-02 00:00:00'} And {ts '2003-06-03 00:00:00'})

 

[Siggy19]

Datetime fields are stored as Real Numbers... the Integer portion is the number of days (since the year .), while the decimal portion is the time.

However, datediff will do most of the work for you and therefore, your calculation needs to be;

SELECT (datediff(s, idrecord.Intialstart, agerecord.Secondstart) + agerecord.Length)
FROM agerecord, idrecord
WHERE agerecord.id = idrecord.id
AND ((idrecord.Intialstart Between {ts '2003-06-02 00:00:00'} And {ts '2003-06-03 00:00:00'})

 

[djburnheim]

Thanks for the pointer but when I use DATEDIFF I get an error "routine datediff can not be resolved". I didn't realise the dates where stored as real numbers so I can do the calculation with the two datetime fields fine but run into problems when I try to add the integer (number of seconds). I thought the other way to do it would be to covert the integer record to datetime but again I can't work out how to do it. Do I need to declare a Cast or is there a convert function in informix I could use?

I tried select Date(agerecord.Length) and it did return yyyy-mm-dd but i need to convert it to yyyy-mm-dd hh:mm:ss.sss

I'm not sure this would work anway...The records in this column are numbers of seconds. (eg the record might be 12 and I would want to convert it to 2003-06-02 00:00:12.000, the ymd being the same as the one being used in the WHERE clause)

 

[djburnheim]

I have worked out how to do the calculation...see below

SELECT (agerecord.Secondstart -idrecord.Intialstart) + int2interval(agerecord.Length)
FROM agerecord, idrecord
WHERE agerecord.id = idrecord.id
AND ((idrecord.Intialstart Between {ts '2003-06-02 00:00:00'} And {ts '2003-06-03 00:00:00'})

Thanks for the help

Dave