Logtime format in postgresql database and log reader from database

Openfire Openfire Support
1

Hi,

i’m searching a software to read the chatroom logs directly from database (we are using postgresql for database), because pidgin (gaim) on linux does not cache all logs from databse, but only part of them. Is there around this kind of software ?

If not i can code a web based application, that will read from openfire database and display the logs, but i kind understand the timelog format "

001201014469139. Could you explain me how can i conver this to human readable format and what kid of format is used when save log time in database?

Thanks in advanced!

2

Hi anebi,

http://www.igniterealtime.org/community/message/165198

daryl

3

Thanks That answer my question

4

How i need to change the query to get the time to be + 2 hours, because the time is 2 hour lesser than real time, this is probably because timezone. I need to change this.

5

Hi anebi,

For example

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

So just do a +‘2 hours’::interval

daryl

6

Thanks a lot, this helped.

Regards, Ali Nebi!