Database design - need advice on good way to deal with 64 bit unsigned integers in SQL

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.
LVL 47
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can't do the maths "as is" in C, you need a specific large number library.
This means, you can't expect any maths from the SQL engine

==> You can store your 20 digit number compressed (ex: old style BCD like ) in any STRING/BLOB column

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidPresidentAuthor Commented:
Thanks - Packed decimal, haven't done that since the 80's when using COBOL ;)   Good idea, use a BLOB, and then I can have as much precision as I need.
I have been using BCD in university in Grenoble...and never since then.
What C math library did you choose/short list for this usage ?
DavidPresidentAuthor Commented:
I'm going to write a plug-in, as I don't need anything fancy. I have the luxury of having written the code that has to record the data, so will use the C interface of sqlite.   Only a few lines of code required to do even 128 bit math,  just a pair of additions or subtractions and checking for overflow/underflow.

Grenoble - been there a few times doing stuff for Bull who OEMed some software I wrote in my day job ... I envy you, beautiful area;)
A Sqlite math C plugin ! It will use some CPU on the db server side but looks great !

I am a Paris insider now...but still have some mountains days close to Grenoble area (lake, ski, trail...)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.