On Fri 31 Aug 2007, Stefan Winter wrote:
Hi,
(this goes into a Wiki page as well)
Today I fell over some caveat when it comes to handling AcctStopTime in databases. In mysql, the schema defines
acctstarttime datetime NOT NULL default '0000-00-00 00:00:00', acctstoptime datetime NOT NULL default '0000-00-00 00:00:00',
and the accounting queries set a literal '0' on starts.
postgresql is different though:
AcctStartTime TIMESTAMP with time zone, AcctStopTime TIMESTAMP with time zone,
and doesn't set anything on starts, which makes the content a NULL.
The difference comes into play for example when you want to delete old records: a
DELETE * from radacct WHERE AcctStopTime < $YOUR_THRESHOLD_DATE
Yep. For these and other reasons I change the Postgres schema to default to NULL many years ago :-) I agree that MySQL should change also, but at the time I go pushback for making backwards incompatible schema changes which some third party billing systems seem to rely on. I think the 2.0 release is the correct time to standardise all of this though.. Cheers -- Peter Nixon http://peternixon.net/