Re: Date and time calculation woes

Paul Watson wrote: > Without altering the table > > then extend each date column to datetime year to second and then > add the time element. The key point, shown but not explained in my answer, is that you cannot simply add the time because you cannot simply add two DATETIME values - you can only add an INTERVAL to a DATETIME. And since no-one ever stores the time portion as an interval, you have to convert the stored DATETIME into an interval, by subtracting the DATETIME for midnight from the actual time value. > The subtract the two values and you'll > get an interval day to second. > > If you need to do this alot then I'd alter the table, if that's not > allowed then trigger into a secondary table containing the > existing primary key and 'full' datetime columns > > Andreas Schlegel wrote: >>I am confused about calculating the time difference from some DATETIME >>columns. >> >>The table has 4 relevant DATETIME columns. 1 contains the date and 1 the >>time (don't ask me why the architect splitted the information on 2 >>fields :-(. 1 pair saves the start and the other pair the end time. >> >>I have to calculate the difference in hours and minutes between both >>pairs. An action could have started yesterday and ends today. So the >>date is important. >> >>I tried simple addition (+) and subtraction (-) operations, I tried to >>use the expand and interval functions, but I never get a working solution. >> >>Does anyone has an idea how to handle it? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/