Query the history of a groupchat

Openfire Openfire Support
1

Hello,

Some users requess some history from a specific ROOM. The problem is the activity logs all the events and create a File every xx minutes (av 1 hour).

Is it possible to do a MySQL QUERY that can shows the full history for a specific room and for a certain amount of time ?

I started to do something, but I’m not familiar with the Database structure .

Thanks.

Js

2

Some more info …

I can do some stuffs

And the dummy query :

SELECT fromJID, sentDate, body

FROM entMessageArchive

WHERE toJID like ‘roomname%’

AND sentDate between 1201882834012 and 1202453643493

But it’s not really optimum.

Any recommendation ?

Regards;

3

Hi,

Here is a query I do in PostgreSQL to query a rooms log for a given date:

SELECT *, (timestamp 'epoch' + int8(logtime) / 1000 * interval '1 second') as tsfrom mucconversationlog m, mucroom r WHERE r.name = 'blahblah' and r.roomid = m.roomid and date(timestamp 'epoch' + int8(logtime) / 1000 * interval '1 second') = '2008-02-01' ORDER by logtime ASC

daryl

4

Hello

I’ll do some test to convert it to a fully MySQL compatible.

Posgresql :

+SELECT *, (timestamp ‘epoch’ + int8(logtime) / 1000 * interval ‘1 second’) as tsfrom mucconversationlog m, mucroom r WHERE+ r.name += ‘blahblah’ and r.roomid = m.roomid and date(timestamp ‘epoch’ + int8(logtime) / 1000 * interval ‘1 second’) = ‘2008-02-01’ ORDER by logtime ASC+

MySQL (TODO):

+SELECT *, (timestamp ‘epoch’ + int8(logtime) / 1000 * interval ‘1 second’) as tsfrom mucconversationlog m, mucroom r WHERE +r.name = ‘blahblah’ and r.roomid = m.roomid and date(timestamp ‘epoch’ + int8(logtime) / 1000 * interval ‘1 second’) = ‘2008-02-01’ ORDER by logtime ASC

Thanks a lot for this .