Issues with Time?

[mnettel]

Hello,
    We are trying to install Queuemetrics on a separate server. Our Asterisk box is a FreePBX Distro running Asterisk 13.2.0. We're running Qloaderd to upload the queue log to mysql on the queuemetrics server. I see that the data is getting into the DB and the heartbeat log increments. Timezone seems to be set correctly on both servers and they're both sync'd to the same NTP server. Running activity reports for the same day shows no calls. Running it in the past shows some calls. I'm starting to think there is an issue with time. The timestamp (epoch) is correct in the DB. The realtime monitoring page in QM shows the correct time, but no data is displayed, even though agents are logged in. The interesting thing is- the heartbeat time in the Mysql Storage Information page is wrong by about 8 hours. Could this be an internal timezone issue? Do I need set timezone in QM? If that's the case, how does it handle Daylight Savings Time?

Thank you.

[mnettel]

Another interesting thing- While the date is usually the correct date, the hour in the Mysql informatino page stays on 02:xx. So if it's 11:30AM the time will show 02:30. If it's 10:45PM, the time will show 02:45

[mirkox]

Hi mnettel,

probably what happens is the following:

- qloader loads new data from queue_log into the database only if the last record in the db has a timestamp < than the one of the new data
- every 15 minutes qloader writes an 'heartbeat' record using the mysql function NOW() to determine the timestamp, if the time is some hour beyond then qloader won't load any new data from queue_log

You can turn off the heartbeat from the qloader settings but what I'd do is to verify and in case fix the mysql time:

http://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql

After that stop qloader; go to /usr/local/qloader; use queuePartialUpdater to reload the queue_log file(s); restart qloader

queuePartialUpdater: http://manuals.loway.ch/QLoader-chunked/ar01s03.html

[mnettel]

Thanks for the reply. Checking the time via mysql shows our correct time. I should also be clear that the row count is incrementing as expected. It looks like the qloader has been working correctly as expected, but the heartbeat times are still off. Epoch timestamps in the table are correct as well. I'm not sure where to troubleshoot

[mirkox]

Hi mnettel,
please open /usr/local/qloader/qloader.pl

and search: "my $timezone_offset"

By default it looks like,

my $timezone_offset  = 0 * 3600;  # in seconds

and normally Perl takes the correct time from system:

Do you have a different value?
If not probably you need to set the correct offset (8*3600 or -8*3600)

Remember to stop the qloaderd service, TRUCATE the queue_log table on the database; if your log is rotated use queuePartialUpdater to reload everything (if you have just one queue_log file that's not needed; then restart the qloaderd service

Mirko