Joanne Pham wrote:
I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then do the conversion in the GUI code to convert from INTEGER TO DATETIME.

    2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME then what is the command to bind this column.

You need to familiarize yourself with SQLite's type system. See for the fundamental data types that SQLite supports, and how it handles them.

A column with a declared type of DATETIME, has an column affinity of NUMERIC. And such a column can store any of the supported datatypes.

The preferred method of storing time and date information in SQLite is using a Julian Day number (see which is floating point number where the integral part represents the date, and the fractional part represents the time. SQLite can use this format to efficiently order and compare dates and times in SQL queries. You can use the builtin date and time functions (see to convert the stored Julian Day numbers to standard (at least nearly ISO-8601 standard) date and time strings for display or manipulation by the language you are using to drive SQLite.

If you use Julian Day numbers you can declare your timetsamp column REAL or FLOAT (or possibly JULIAN or even DATETIME if you wish), and then use the sqlite3_bind_double() and sqlite3_column_double() to insert and extract the raw floating point values, or sqlite3_column_text() to extract a time or date strings returned by the builtin conversion functions.

   create table test (id integer primary key, data text, created julian);

Note you can't use the "default current_timestamp" clause because it will insert a string version of the date and time. If you want automatic inserts of a Julian Day number you need to use a trigger.

   create trigger set_created after insert on test begin
       update test set created = julianday('now') where rowid = new.rowid;

If you add an index on the created column you can quickly search for dates and times, or date or time ranges.

   create index created_idx on test(created);

You can now easily search for ranges and convert the result for display.

select id, data from test where created > julianday(date('now', '-30 days'));

   select id, data, date(created)  from test order by created desc limit 1;

Most of the same things can be accomplished if you store the ISO-8601 string verisons of the date and time in the database, but that requires 19 or more bytes per timestamp (and the timestamp value is duplicated in each index as well), whereas the Julian Day number requires only 8 bytes per timestamp.

Dennis Cote

To unsubscribe, send email to [EMAIL PROTECTED]

Reply via email to