SQlite: Column format for unix timestamp; Integer types

Original problem: What is the right column format for a unix timestamp?

The net is full of confusion: some posts claim SQLite has no unsigned types - either whatsoever, or with exception of the 64bit int type (but there are (counter-)examples that invoke UNSIGNED INTEGER). The data types page mentions it only in a bigint example. It also claims there is a 6-byte integer but doesn't give a name for it. It seems my tries with INT开发者_JAVA技巧EGER being 4-byte signed signed store unix timestamps as negative numbers. I've heard that some systems return 64-bit timestamps too. OTOH I'm not too fond of wasting 4 bytes to store 1 extra bit (top bit of timestamp), and even if I have to pick a bigger data format, I'd rather go for the 6-byte one. I've even seen a post that claims SQLite unix timestamp is of type REAL...

Complete problem: Could someone please clarify that mess?

The size of an integer

All columns in SQLite databases are internally variable-width. The file format stores integers in 1, 2, 3, 4, 6, or 8 bytes, depending on how big the number is, plus one byte in the header to indicate the size. So, in total, Unix dates stored as integers will take up 5 bytes until 2038-01-19 and 7 bytes after that.

From the point of view of the user of the C API, all integers are signed 64-bit.

The column type

It doesn't matter whether you declare your column as INTEGER, UNSIGNED INTEGER, BIGINT, or whatever. Anything with "INT" in it has integer affinity. And, as mentioned above, all integers are signed 64-bit but not usually stored that way.

SQLite does not have unsigned types. That's directly from the main author, as well as the docs. Moreover, it doesn't have fixed column widths for integers; the actual on-disk width is an implementation detail.

SQLite has no date or time datatype. However, it has date functions that can operate on ISO8601 strings (TEXT), Julian day numbers (REAL), and Unix timestamps (INTEGER).

So if you decide to make your time field a Unix timestamp, know that it can store up to 64-bit signed integers, but values you store now should actually occupy 32 bits on disk, even if the source value is a 64-bit time_t.

My preference would be for a 64-bit integer. The classic case of an unsigned 32-bit integer is with seconds since 1970-01-01 runs out in 2038. See http://en.wikipedia.org/wiki/Unix_time and http://en.wikipedia.org/wiki/Year_2038_problem . With a 64-bit unsigned integer, you're safe

Could you give an example of what you mean by "It seems my tries with INTEGER being 4-byte signed signed store unix timestamps as negative numbers."?

If you haven't already I'd suggest reading SQLite docs on datatypes (section 1.2 Date and Time Datatype) and date and time functions.

If you're on an embedded system where the memory situation is critical, you can consider dropping precision by shifting the 64-bit value several bits (resulting in a precision of 2, 4, 8... seconds instead of 1 sec) and using a 32-bit value to store it.