Informix Error -561: Sums and averages cannot be computed on datetime values.

Reasons / Common Causes

-561 fires when SUM or AVG is applied directly to a DATETIME column — those aggregates require arithmetic across the aggregated values, and Informix doesn't define arithmetic directly on DATETIME values the way it does on numeric types.

1. SUM(datetime_column) or AVG(datetime_column), per the official guidance — the direct, only cause.
2. A misunderstanding of what's actually being computed — wanting an average interval between datetimes (e.g. average processing time) rather than an average of the datetime values themselves.
3. Generated reporting SQL applying the same aggregate template to every numeric-looking column, without excluding DATETIME columns from that treatment.

Solutions / Resolution

1. Revise the query, per the official guidance — there's no direct workaround for summing or averaging DATETIME values themselves, since the operation isn't semantically defined.
2. If an average interval/duration was actually intended, compute the difference as an INTERVAL first, then aggregate that:

   SELECT AVG(ship_date - order_date) FROM orders;
   

   subtracting two DATETIME values yields an INTERVAL, which arithmetic (and therefore AVG) is defined on.
3. If a representative single value (not a true average) is what's needed, consider MIN/ MAX instead, which are defined on DATETIME since they only require ordering, not arithmetic.

Examples

The disallowed aggregate

SELECT AVG(order_date) FROM orders;
-- -561: arithmetic isn't defined on DATETIME

Averaging the interval instead

SELECT AVG(ship_date - order_date) FROM orders WHERE ship_date IS NOT NULL;

MIN/MAX work directly on DATETIME

SELECT MIN(order_date), MAX(order_date) FROM orders;

Diagnostic Checks

1. Identify which aggregate function is applied to which column, and confirm the column's declared type is DATETIME (or an interval-incompatible date type) rather than a numeric type.

Related Errors / Related Topics

No closely related error codes are cross-referenced for -561 in this set yet.

SUM/AVG aren't defined on DATETIME directly — if an average duration is the actual goal, subtract two DATETIME values into an INTERVAL first, then aggregate that.