Use INTEGER type for all time ralated database columns
Created by: E3V3A
Background
Partially discussed in #557, taken into this Issue: SQLite3 deals with time and date a little differently than other databases. One big difference is that there is no DATE or TIME related data type. It can use any type such as TEXT, INTEGER or REAL etc. This is actually a blessing, because it can greatly simplify the entry and processing of timestamps. Something we're very fond of here. But in our case there is a clear advantage to use the INTEGER type instead of any of the others. The reason is that we most easily can use Unix/Posix time which is the number of seconds since 1970-01-01 00:00:00 for timestamps for all our time related entries. Our current DB design is erroneously using the TEXT data type for time fields. This was a design mistake by me, and thus need to be changed.
Using INT allow us to use (32 bit) timestamps all the way into year 2038...which should be more than plenty. In addition, all the network stuff we can obtain, is extremely unlikely to be caught on any timescale faster than 1 second. Thus the use of milliseconds is complete overkill, at least until we can write C-code binaries to extract data directly from /dev/diag interface, which will not happen anytime soon...AFAICT, and in which case the data has to be buffered anyway.
TO DO
- Replace the TEXT data type with INTEGER in all time related table columns. These are:
time, time_first, time_last, time_add, time_exp. (We exclude theSmsData:timefrom these.) - For all the DB input/output, make sure you use seconds and not milliseconds.
Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.