Caveat: schema differences in Acct(Start|Stop)Time

[Peter Nixon]

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/