Re: averaging the datetime values

SlowG wrote: > 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? Hmmm... You can add intervals, so you can probably average or sum them. I'm not sure what siginificance you could ever attach to the sum of a set of DATETIMES other than as a prelude to averaging them, anyway. So, you should probably simply decide to do something like: SELECT DATETIME(2000-01-01 00:00:00) YEAR TO SECOND + AVG(dtcolumn - DATETIME(2000-01-01 00:00:00) YEAR TO SECOND) When you do an AVG like this, the reference date (Y2K in the example) is immaterial as long as it is consistent. If you really, really want a SUM instead of an AVG, then you have to be careful about what you choose as a reference date -- though I would still question your understanding of the situation (or mine). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote: > SlowG wrote: >> 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? > You can add intervals, so you can probably average or sum them. > I'm not sure what siginificance you could ever attach to the sum of a > set of DATETIMES other than as a prelude to averaging them, anyway. > > So, you should probably simply decide to do something like: > > SELECT DATETIME(2000-01-01 00:00:00) YEAR TO SECOND + > AVG(dtcolumn - DATETIME(2000-01-01 00:00:00) YEAR TO SECOND) > > When you do an AVG like this, the reference date (Y2K in the example) is > immaterial as long as it is consistent. If you really, really want a > SUM instead of an AVG, then you have to be careful about what you choose > as a reference date -- though I would still question your understanding > of the situation (or mine). Urgh...you'd need to verify whether the difference between two DATETIME YEAR TO SECOND values is a sensibly-qualified INTERVAL DAY(N) TO SECOND value. If not, add INTERVAL(0 0:0:0) DAY(9) TO SECOND before the subtraction: AVG(INTERVAL(0 0:0:0) DAY(9) TO SECOND + (dtcolumn - DATETIME(2000-01-01 00:00:00) YEAR 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/