Time in SQLite as insert (and update / select)

I have an SQLite db with a table that contains 4 fields. The create statement is:

CREATE TABLE [time] (

[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

[from] TIME NOT NULL,

[until] TIME NOT NULL,

[message] TEXT NOT NULL,

)

After creation I would like to add some test data. For this I use:

INSERT IN开发者_JAVA技巧TO time('from','until','message') VALUES('00:00','01:00','test')

The result in the database is

id = 1

from = 0:00:00

until = 0:00:00

message = test

Obviously the time is incorrect, meaning that it is not what I want it to be. What is wrong in my insert statement? I already found this website, but I am not sure how it should help me. Of course, sample code could help me a great deal.

I couldn't reproduce your results either, I got the same result as dkarp. You don't have to assign a datatype to columns in sqlite tables so apparently if you assign an invalid one it is ignored. In your case your text values were saved as text.

sqlite has no TIME datatype, according to sqlite.org/datatype3.html so you can assign TEXT as the affinity for the 'from' and 'until' columns instead. It makes more sense to save your values as strings containing the date and the time rather than time alone... something like the following:

sqlite> CREATE TABLE [time] (
   ...> [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   ...> [from] TEXT NOT NULL,
   ...> [until] TEXT NOT NULL,
   ...> [message] TEXT NOT NULL
   ...> );
sqlite> 
sqlite> 
sqlite> INSERT INTO time('from','until','message') VALUES(date('now') || ' 00:00',date('now') || ' ' || '01:00','test');
sqlite> INSERT INTO time('from','until','message') VALUES(date('now') || ' 14:00',date('now') || ' ' || '15:30','test');
sqlite> 
sqlite> SELECT * from time;
1|2010-12-30 00:00|2010-12-30 01:00|test
2|2010-12-30 14:00|2010-12-30 15:30|test