> The column expected_by contains an estimated time of arrival for a
particular
> conveyance. When a row is initialized this value is unknown some of the
> time. The expected_by value is reset to the arrived_at value on UPDATE if
> and only if expected_by is greater than arrived_at.
>
> Conveyances that have +infinite expected_by time-stamps are considered
> pending. At some point conveyance rows that are never going to arrive are
> otherwise flagged. On the other hand, rows with overdue expected_by
> values are given somewhat more attention, to put it mildly. So, we either
fix
> the problem with AR, possibly by moving to Sequel ORM for this case,
> although I have not yet received an answer as to whether it does any
better;
> Or we trap and override NULL values with infinity in a trigger; Or we
choose
> for the default value a fixed date far, far into the future.
>
> +Infinity was chosen as a default to avoid the complexities of
> dealing with NULL logic in SELECTS. I suppose that the simplest solution
is to
> go with a date of 9999-12-31 and treat that value like infinity.
The "just make it work" solution has many merits - I would also probably
just use 9999-12-31 as a close approximation for +infinity; which itself is
just there because you are avoiding "estimate is unknown".
Why bother updating the "expected_by" value once the conveyance is no longer
pending? Do you not really care if something arrived early? Even if you do
not currently it seems a waste to throw out the data when you can readily
get the same result as-needed (CASE WHEN expected_by <= arrived_at THEN
arrived_at ELSE expected_by END) without giving up the ability to calculate
early-ness. It would make more sense to set expected = arrived if and only
if expected = 'Infinity'. Still, it would at least seem reasonable to guess
a reasonable expected date if one is not otherwise provided - possibly with
a flag indicating that it is a true guestimate instead of a estimate.
David J.