Date and time calculation woes

Hi, 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? Greetings, Andreas

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? ALTER TABLE X ADD (dt_start DATETIME YEAR TO SECOND, dt_end YEAR TOSECOND); UPDATE x SET dt_start = EXTEND(start_date, YEAR TO SECOND) + (start_time - DATETIME(0:0:0) HOUR TO SECOND), dt_end = EXTEND(end_date, YEAR TO SECOND) + (end_time - DATETIME(0:0:0) HOUR TO SECOND); ALTER TABLE x DROP start_date, start_time, end_date, end_time;RENAME TABLE x AS y; CREATE VIEW x AS SELECT ...; Oh well, you probably didn't want to do all that - but the expressions in the SET clause produce the DATETIME YEAR TO SECOND values corresponding to the separate date and time components, and you can take the difference between the end and start values to obtain the interval. Did you have any particular units in mind for the interval? Unless you take steps, you will get INTERVAL DAY TO SECOND; if you want, say, hours and minutes (as indicated in the question), then you need to prefix your computation by an appropriate zero interval: INTERVAL(0:0) HOUR(9) TO MINUTE + ((EXTEND(end_date, YEAR TO SECOND) + (end_time - DATETIME(0:0:0) HOUR TO SECOND)) - (EXTEND(start_date, YEAR TO SECOND) + (start_time - DATETIME(0:0:0) HOUR TO SECOND))) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Without altering the table then extend each date column to dateime year to second and then add the time element. 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: > > Hi, > > 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? > > Greetings, > Andreas -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #