I have looked at a few similar threads but none that I have looked at really spelled it out well . Given a series of chatrooms in openfore I want to retrieve and report on conversations in a specific room for a given day. One older thread gave exactly what I was looking for but using postgresSQL. I need it for mySQL. And the two don’t really look the same and their date functions are not the same. This should be a simple thing. But the logtime being in epoch format has me all wrapped around a pole. For laziness sake would anyone out there have done this before? Thanks to all in advance.
another interesting thingy. I am making the assumption that the logTime column in the ofMucConversationLog is an epoch time but when I plug that value into an epoch converter I get a whacko answer 3 centuries into the future. Is that value really in epoch format and if it supposed to be how the heck does one convert it to human readable?
I figured this thang out! Between the posts http://www.igniterealtime.org/community/message/165252#165252 and some googling of mySQL date functions. I got a select that works. Here it is in all it’s glory…
This one selects all the body columns, which is basically the text for the room, for a specific date.
SELECT body, from_unixtime(logTime / 1000) as ts
from ofMucConversationLog m,
ofMucRoom r
WHERE +r.name = ‘testroom’ and r.roomID = m.roomID
and date(from_unixtime(logTime/1000)) = ‘2008-09-15’
ORDER by logTime ASC;
Then there was the one I wanted. Which grabbed the text for the current date minus 1 day. I am going to CRON this guy to run every morning at 1 am. Then email the output to a user. Yeah!
SELECT body, from_unixtime(logTime / 1000) as ts
from ofMucConversationLog m,
ofMucRoom r
WHERE +r.name = ‘testroom’ and r.roomID = m.roomID
and date(from_unixtime(logTime/1000)) = date_sub(curdate(),interval 4 day)
ORDER by logTime ASC;