Re: [sqlite] Reducing size of timestamps
Use the Sqlite method, a floating point number. Inbuilt functions support that technique. Rich Rattanni wrote: > All: > > I was wondering if there was any way to reduce the 'cost' of storing a > timestamp on entries in a SQLite database. I performed a hexdump of > the file and it showed me the timestamp is stored as a 19-byte ASCII > string. One quick thing I thought of was to store the unix timestamp > in each field, then when I wanted an actual date use > datetime(mytimeField,'unixepoch','localtime') to convert it back. > This would save me 9 bytes per record, but I would (greedily) like to > save more... any thoughts? > > -- > TIA, > Richard Rattanni > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing size of timestamps
Rich Rattanni wrote: > > I was wondering if there was any way to reduce the 'cost' of storing a > timestamp on entries in a SQLite database. I performed a hexdump of > the file and it showed me the timestamp is stored as a 19-byte ASCII > string. One quick thing I thought of was to store the unix timestamp > in each field, then when I wanted an actual date use > datetime(mytimeField,'unixepoch','localtime') to convert it back. > This would save me 9 bytes per record, but I would (greedily) like to > save more... any thoughts? > Storing a unix timestamp should save you 14 bytes per record. The unix timestamp is an integer value with a value that fits into 32 bits (at least until 2038). Integers are stored in a variable size format in SQLite that should use only 5 bytes per 32 bit timestamp. Storing a unix timestamp gives you resolution to one second. If you need finer resolution you can save a double precision floating point julian day number which uses 8 bytes per record. For lower storage you will probably need to go to a delta encoding where you store the time difference between records rather than the absolute time of the record. These values are harder to use, but can save considerable space and work quite well if you have lots of data stored at fairly regular intervals. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing size of timestamps
Rich Rattanni wrote: > All: > > I was wondering if there was any way to reduce the 'cost' of storing a > timestamp on entries in a SQLite database. I performed a hexdump of > the file and it showed me the timestamp is stored as a 19-byte ASCII > string. One quick thing I thought of was to store the unix timestamp > in each field, then when I wanted an actual date use > datetime(mytimeField,'unixepoch','localtime') to convert it back. > This would save me 9 bytes per record, but I would (greedily) like to > save more... any thoughts? > There is the old fashioned way of storing the date time stamp as an Int64 showing the number of milliseconds since an arbitrary point in time. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reducing size of timestamps
All: I was wondering if there was any way to reduce the 'cost' of storing a timestamp on entries in a SQLite database. I performed a hexdump of the file and it showed me the timestamp is stored as a 19-byte ASCII string. One quick thing I thought of was to store the unix timestamp in each field, then when I wanted an actual date use datetime(mytimeField,'unixepoch','localtime') to convert it back. This would save me 9 bytes per record, but I would (greedily) like to save more... any thoughts? -- TIA, Richard Rattanni ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
