How about this scenario:
(say it is March 1, SQL and col names from memory...)
The 'live' table keeps up to two months (and a few hours) of data
(January, February, March 1)
Some time on March 1 you rotate out January
CREATE TABLE jan_04 LIKE.....
INSERT INTO jan_04 SELECT * FROM radacct WHERE
acctStartTime <= "2004-02-01"
UPDATE jan_04 SET acctStopTime = "2004-01-31 23:59:58",
acctSessionTime = acctSessionTime - 1 WHERE
acctStopTime = "2004-01-31 23:59:59"
UPDATE jan_04 SET acctStopTime = "2004-01-31 23:59:59" WHERE
acctStopTime > "2004-01-31 23:59:59"
UPDATE jan_04 SET acctSessionTime =
UNIX_TIMESTAMP(TIMEDIFF(acctStopTime, acctStartTime) WHERE
acctStopTime = "2004-01-31 23:59:59"
DELETE FROM radacct WHERE acctStartTime <= "2004-02-01"
UPDATE radacct SET acctStarTime = "2004-02-01 00:00:00" WHERE
acctStartTime < "2004-02-01 00:00:00"
UPDATE radacct SET acctSessionTime =
UNIX_TIMESTAMP(TIMEDIFF(acctStopTime, acctStartTime) WHERE
acctStarTime = "2004-02-01 00:00:00"
You don't have to wait a month to do this, but that would pretty much
guarantee that all of the to-be-rotated sessions have been closed.
Any sessions that you see that end exactly at 23:59:59 on the last day
of the month are ones that are have continuation into the next month.
And yes, I recognize that this is the same as some y2k issues: 9/9/99,
12/31/99 meaning 'never' or 'infinite'.. But your only out a second.
On Fri, 2004-03-12 at 13:37, Guy Fraser wrote:
> The main issue I come across with tables that span no more than one
> month, is
> that if a user is online past the end of the month, their time is
> accounted in the
> next month. I need to come up with a way to "split" the records that
> overlap the
> end of a month {or year} that does not damage the integrity of the
> record. I have
> been considering a termination code of "Split" or somthing like it
> indicating the
> record "Continues" into the next table, but I havn't yet decided how to
> indicate
> that a record is a "Continuation" from a previous table.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
