Our organization would like to keep only 60 days worth of messages archived on the server at any time. This would be a rolling window. I have not found a way in OpenFire to tell it to keep only 60 days of messages. I am thinking that I may have to create a MySQL stored procedure that hits the ofconversation and ofmessagearchive tables. I do not want to truncate them. The challenge comes in the date format for those tables. I don’t know how to carry out a select statement on date columns using a bigint(20) type.
If OpenFire has a property that handles this which I am not aware of, please inform me. Otherwise, I’d love to see some suggestions on how to create the stored procedure that would handle the job.
Thanks for your help.
Okay,
No luck so far. Nobody appears to have any more clue than me, so here is my speculation. The fields I am dealing with contain values like this: 1236792063230. That’s 13 characters. My plinking around with SELECT statements like the one below have resulted in almost what I want, as far as bigint to human readable date is concerned:
select from_unixtime (‘lastActivity’, ‘%Y-%m-%d %H:%i:%s’) from ofconversation;
This results in output in date/time format, but the dates are all on “1969-12-31”. At least I am getting something back in date form, although it us useless.
Toad also provides this error: "Connection 3/13/2009 4:02:38 PM 1292: Truncated incorrect INTEGER value: ‘lastActivity’ ". I take it that means the date formatting string is cutting something off. Still, no idea for sure what I need to do.
Does anyone have a clue?
Thanks again.
Truly Befuddled
Okay,
I have made some more progress. The time in the OpenFire MySQL tables is stored as bigint data type. The system timestamps each entry in various fields, like lastActivity.ofconversation. The timestamps are in milli-seconds. Unix time is in seconds, so the timestamp in each of the fields you need to work with must be divided by 1000. They also need to be rounded up or down, use the round( ), so that you don’t get errors. Applying date format results in a human readable time output. My query is below:
select from_unixtime (round ((lastActivity/1000))) from ofconversation as date;
Next step is to do some date arithmetic so that it subtracts 60 days from the output of the above query and deletes it. I will then have to create a function/procedure and a nightly event trigger.
This is definitely a work in progress.
A bit less befuddled
Okay,
I did some more thinking over this past weekend. Seems to me that, although a useful excercise, it is not necessary to convert Unix time to Date time in order to select the needed rows. I can simply use some arithmetic to select records older than the current unixtime. This is a sample statement:
select * from ofconversation, ofmessagearchive
where ofconversation.conversationID = ofmessagearchive.conversationID
and ofconversation.lastActivity <= (UNIX_TIMESTAMP(now()) * 1000) - 864000000;
This selects all rows in the ofconversation and ofmessagearchive tables with matching conversationID fields. These should always match anyway, but it is a precaution. Anyhow, by using the ofconversation.lastActivity field as my parameter, I can use this query to select those records older than or equal to 10 days old. From here it should get easier. I just have to schedule a regular drop of the records selected by a modified version of this query.
Hope this is helping some other schlep out.
Julio
UnixTimeConversion.sql (79 Bytes)
Select by UnixTime.sql (197 Bytes)