Monitoring : Clearing Chat Archive History

Version 3

    Hello Everybody,

     

     

    I would like to know if there is a way to clear or reset the chat archive within the monitoring plugin.  How bout the stats?

     

     

    You can use the following statements to delete "old" chat archiving data. Replace 1192224791872 with whatever timestamp you want.

     

    The time stamp is calculated in milliseconds that passed since 1.1.1970. To calculate the timestamp for the Jan 1st 2010 (1262304000000) you may use this Excel formular: =(DATE(2010;1;1)-DATE(1970;1;1))*24*60*60*1000

     

    The SQL statements in an earlier post do not execute on PostGRE, hence here is an alternative set of delete statements:

     

    delete FROM ofMessageArchive
    WHERE ofMessageArchive.conversationID in (select ofConversation.conversationID from ofConversation where ofConversation.startDate <= 1345420800000)

     

    delete FROM ofConParticipant
    WHERE ofConParticipant.conversationID in (select  ofConversation.conversationID from ofConversation where  ofConversation.startDate <= 1345420800000)

     

    delete FROM ofConversation
    WHERE ofConversation.conversationID in (select  ofConversation.conversationID from ofConversation where  ofConversation.startDate <= 1345420800000)

     

    The ones below work well on MySQL:

     

    DELETE ofMessageArchive.* 
    FROM ofMessageArchive, ofConversation
    WHERE ofMessageArchive.conversationID = ofConversation.conversationID
    AND ofConversation.startDate <= 1192224791872;
    
    DELETE ofConParticipant.* 
    FROM ofConParticipant, ofConversation
    WHERE ofConParticipant.conversationID = ofConversation.conversationID
    AND ofConversation.startDate <= 1192224791872;
    
    DELETE ofConversation.* 
    FROM ofConversation
    WHERE ofConversation.startDate <= 1192224791872;
    

     

    Regarding statistics there is no need to delete old data since we are not storing historical data in a sequential manner. Instead we are using an RRD database that keeps always the same size. It is around 16K per file no matter the history we are keeping. Yes, I know...it is some kind of black magic. RRD is cool.

     

    Regards,

     

      -- Gato

     

     

     

    The following query will return all messages older than 1 month

     

     

     

    select * FROM ofMessageArchive o

    where sentDate/1000 &lt; unix_timestamp(now())-2629743

     

     

    Just make it Delete and your good. Next  I'm going to work on this running every night so I know there's only 30 days of message history.

     

     

    DELETE FROM ofMessageArchive o

    where sentDate/1000 &lt; unix_timestamp(now())-2629743

     

     

     

     

     

     

     

     

     

    How can i execute those queryies on  embedded database ?

     

    There is no documented way to delete the information from the embedded DB.

     

     

     

    Dieses Dokument wurde aus folgender Diskussion erzeugt: Monitoring : Clearing Chat Archive History