SQlite: Column format for unix timestamp; Integer types

sqlite types integer unsigned unix-timestamp
SF. picture SF. · Apr 6, 2010 · Viewed 10.3k times · Source

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 INTEGER 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?

Answer

Matthew Flaschen picture Matthew Flaschen · Apr 7, 2010

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.

Related questions

How to create ENUM type in SQLite?

I need to convert a table from MySQL to SQLite, but I can't figure out how to convert an enum field, because I can't find ENUM type in SQLite. The aforementioned field is pType in the following table: CREATE TABLE `…

Read More
Comparison of database column types in MySQL, PostgreSQL, and SQLite? (Cross-Mapping)

I am trying to find some way to relate column types across the the most used Databases: MySQL, PostgreSQL, and SQLite. Here is what I have so far, but I'm afraid it's not done and I need some people with …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More