Hello, I am using nfacct to process flows from our routers and store them in a mysql database for processing and visualization.
I have a 'daily' table for storing the daily traffic for each IP. I've set sql_history to 1d to store one record per day per IP. This worked fine for months. Each record's time started on 00:00:00 of each day. It came to my attention today that since March the 28th all the inserted records have a time of 01:00:00. On 27th we had our clocks changed due to DST (from GMT+2 to GMT+3). The mysql daemon hasn't been restarted since late 2015 so its internal clock does not take into account the recent DST change (as far as I can tell). So after researching about it a little it seems that MySQL has an issue with the datetime type field and timezone changes (without restarting the mysql server or setting the timezone on session start). According to various articles, the 'timestamp' type field handles this better by converting any given date/time during insert from the local timezone to UTC and converting it back from UTC to the local timezone during read (select). One drawback mentioned is the range limitation of timestamp type since it's based on the Unix Epoch Time, so you can't have dates prior to 1970 or after 20385- which obviously is not an issue for storing network flows :) Should I simply change the schema of my database from datetime type to timestamp type? I am no sql expert, so I don't know what are the ramifications of a change like this. I know there's the sql_history_since_epoch config key for nfacct for storing all dates in unix_timestamp format, but this requires major changes on my frontend code and general processing of already stored data, so I would definitely like to avoid it if possible. Does anyone have a similar issue? How do you handle it? Thanks, Vaggelis. _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
