I'm setting up an event logging database and need a sanity check on best practices to deal with 64-bit unsigned data. First, the number of records generated would typically be under 100 writes/second per computer, but could be aggregated hundreds or more systems once the event loggers are aggregated onto a common remote database server. Each computer needs it's own database as I am making the proverbial switch from fopen() to sqlite3 for internal files & structures. Event data and status will be kept in local database and I can add a layer to aggregate into an enterprise class database as a separate task.
So with that in mind, I don't want to reinvent the wheel, so I need to maintain compatibility and don't want to do anything that would cause grief using features unique to different sql databases.
Many of the log entries will consist of historical data that uses 64-bit UNSIGNED integer counters. I don't want roundoff, so have to use integer math. Sqlite3 and others use 64-bit signed integers. The counters will always either stay the same, or increase, and are non-volatile. So if you start collecting data at any point in time, you know the number will always increase.
Only time counter would get smaller is if it rolls over past 2^64 - 1. I'll be doing reports that compare counters and look at timestamps and such, so need to have something that won't require a lot of extra steps for somebody to just use off-the-shelf reporting... Most of the time people are going to care about the rate of change during a period of time for any counter, and compare that rate of change over time to others.
So what do I do? I'm thinking to create a table that establishes a normalized starting value the first counter is read, and then when the agent writes a log entry with the new data, I subtract the starting value? If I do that then I can use the standard 64-bit integer, and then I'll be OK, at least until there is a rollover.
Or just use 20 digit numeric numbers and not worry? I just hate the idea of inefficiency but need something easy. The program that generates the data is written in c, so I can massage the data any way I want before pushing it into SQL.