SQLite allows you to insert string data into a datetime column

Question

Table:

CREATE TABLE logaction ( actype varchar(8) not null, actime DATETIME not null, devid int not null ); 

SQL:

 insert into logaction values ('TEST', '2013-08-22', 1); insert into logaction values ('TEST', '2013-08-22 09:45:30', 1); insert into logaction values ('TEST', 'xyz', 2); // shouldn't this fail? 

The last entry does this in the table, regardless of the non-datetime value for the actime column. Why and how can I enforce only good data?

Here is the SQL Fiddle .

Answer 1

Well, there is simply no DATETIME type in Sqlite ...

SQLite does not have a storage class reserved for storing dates and / or times. Instead, SQLite's built-in date and time functions can store dates and times as TEXT, REAL, or INTEGER values:

 TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 BC according to the proleptic Gregorian calendar. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. 

Applications can choose to save dates and times in any of these formats and freely convert between formats using the built-in date and time function.

see doc

You could create your table in this way; it would not change anything.

 CREATE TABLE logaction ( actype varchar(8) not null, actime NonexistingType not null, devid int not null ); 

Answer 2

Unfortunately, not really in sqlite - it is not of type datetime - see section 1.2 in here

Answer 3

Here's a simple job:

 CREATE TABLE logaction ( actype varchar(8) not null, actime DATETIME not null check( DATETIME(actime) is not null ), devid int not null ); sqlite> insert into logaction values ('TEST', '2013-08-22', 1); sqlite> insert into logaction values ('TEST', '2013-08-22 09:45:30', 1); sqlite> insert into logaction values ('TEST', 'xyz', 2); Error: constraint failed sqlite>