Hi Vaggelis, I look forward to any thoughts about data types. Personally, the very first reaction this trigger is: the backend of the accounting system should be set to a timezone that does not change during the year and, even more ideally, to UTC. UTC is ideal because it helps when stuff is spanning across timezones. Then, if data is consumed internally, say, by scripts this may even be enough; if data is presented externally instead, any timestamp manipulation should be done in the frontend.
Cheers, Paolo On Mon, May 30, 2016 at 07:39:25PM +0300, Vaggelis Koutroumpas wrote: > 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 _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
