Re: Date and time calculation woes

Hello Jonathan, thanks for your help. It's running now. Greetings, Andreas Jonathan Leffler wrote: > 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 TO> SECOND); > 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))) > >