Re: averaging the datetime values

SlowG wrote in message <10d65b21.0308151433.72f1639b@posting.google.com>... >I want to average the datetime value in my query but it gives me the >error saying average and sum cannot be used on datetime. >Can someone please help me with this? > >Thanks Your problem is that SUM and AVG need to add the datetimes. You can subtracts two datetimes, and get an interval, but it makes no sense to add datetimes. What would the answer mean ? (Datetimes are just like pointers, I guess) So, you'll have to cheat ... turn the datetimes into intervals and average those. -- some test data ... create table dt_test ( id serial, dt datetime year to second ); insert into dt_test values(0, current); insert into dt_test values(0, current); insert into dt_test values(0, current); insert into dt_test values(0, current); insert into dt_test values(0, current); update dt_test set dt = dt - id units day where 1=1; select dt from dt_test; dt 2003-08-15 13:40:15 2003-08-14 13:40:15 2003-08-11 13:40:15 2003-08-10 13:40:15 2003-08-09 13:40:15 -- and the cheat ... -- need to smallest datetime ... select min(dt) min_dt from dt_test into temp dt_min with no log; -- (dt - min_dt) is a interval, so you can avg it ... select avg(dt - min_dt) avg_dt from dt_test, dt_min into temp dt_avg with no log; -- add the min back ... select min_dt + avg_dt from dt_min, dt_avg; (expression) 2003-08-11 13:40:15 Which is not at all pretty, I'm sure that there must be a better way ... -- RH