SQlite: Column format for unix timestamp; Integer types

Posted by SF. on Stack Overflow See other posts from Stack Overflow or by SF.
Published on 2010-04-06T09:28:28Z Indexed on 2010/04/06 9:33 UTC
Read the original article Hit count: 528

Filed under:

sqlite

|

unix-timestamp

|

datatypes

|

integer

|

unsigned

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. Of course standard INTEGER being 4-byte signed signed stores 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?

© Stack Overflow or respective owner

Related posts about sqlite

How to add SQLite (SQLite.NET) to my C# project

as seen on Stack Overflow - Search for 'Stack Overflow'
I followed the instructions in the documentation: Scenario 1: Version Independent (does not use the Global Assembly Cache) This method allows you to drop any new version of the System.Data.SQLite.DLL into your application's folder and use it without any code modifications or recompiling… >>> More
dotConnect or "sqlite.phxsoftware.com" for using Entity Framework with SQLite

as seen on Stack Overflow - Search for 'Stack Overflow'
Which is the better choice out of dotConnect or "sqlite.phxsoftware.com" for using Entity Framework with SQLite (in C#)? >>> More
SQLite table does not exist exception for existing SQLite database (and table)

as seen on Stack Overflow - Search for 'Stack Overflow'
I've followed the instructions given here for introducing an existing SQLite database to your Android app. When I query the table "android_metadata" this is fine. But when I run a similar query on my own table "words" (which has _id for primary integer key) I get a table does not exist exception… >>> More
Android Jelly bean database is locked (code 5)

as seen on Stack Overflow - Search for 'Stack Overflow'
Im getting a database is locked (code 5) in my ListActivity the code works in the other versions of the Emulator but fails in the 4.1 version of the emulator E/SQLiteLog( 2132): (5) database is locked E/SQLiteDatabase( 2132): Failed to open database '/data/data/id.online.mydroid/databases/geo… >>> More
Android:accessing assets folder sqlite databse file with .sqlite extension

as seen on Stack Overflow - Search for 'Stack Overflow'
How to read data from the assets folder sqlite database file with .sqlite extension in my android application? Thanks >>> More

Related posts about unix-timestamp

Check whether the string is a unix timestamp

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a string and I need to find out whether it is a unix timestamp or not, how can I do that effectively? I found this thread via Google, but it doesn't come up with a very solid answer, I'm afraid. (And yes, I cribbed the question from the original poster on the aforementioned thread). http://www… >>> More
Getting the day from a Unix timestamp (PHP)

as seen on Stack Overflow - Search for 'Stack Overflow'
How do I get the day (1-7) from a Unix timestamp in PHP? I also need the day date (1-31) and month (1-12). >>> More
MySQL: What's the best to use, Unix TimeStamp Or DATETIME

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, Probably many coders want to ask this question. it is What's the adventages of each one of those MySQL time formats. and which one you will prefer to use it in your apps. For me i use Unix timestamp because maybe i find it easy to convert & order records with it, and also because i never… >>> More
How do I find the unix timestamp for the start of the next day in php?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a unix timestamp for the current time. I want to get the unix timestamp for the start of the next day. $current_timestamp = time(); $allowable_start_date = strtotime('+1 day', $current_timestamp); As I am doing it now, I am simply adding 1 whole entire day to the unix timestamp, when instead… >>> More
Oracle SQL - Column with unix timestamp, need dd-mm-yyyy timestamp

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, is there any way in Oracle, to get only the dd-mm-yyyy part from an unix timestamp in oracle? Like: select to_char(my_timestamp, 'ddmmyyyy') as my_new_timestamp from table >>> More