How to corectly insert a datetime value in SQLite under Android?

Question

I'm working on a dummy android project to get a hang of things. The following line of code breaks my android application without any error. I'm using Idea as an IDE.

database.execSQL("Insert Into todos(uid, name, created_on, changed_on) values('6a7047ed-c2f9-407f-a774-1c02b0fe9caf', 'todo', DATETIME('NOW'), DATETIME('NOW'))");

I'm sure the database has the given columns, and if I just use:

database.execSQL("Insert Into todos(uid, name) values('6a7047ed-c2f9-407f-a774-1c02b0fe9caf', 'todo')");

The application works the way it should.

I tried to insert dates like this (based on answered question related to this matter):

 '2012-05-25 16:52:00'

or

 date('now')

I get the same crash with no error or debug log.

What am I missing here?

Updated

As requested, the code used to create the database table.

    public static final String TABLE_TODOS = "todos";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_UID = "uid";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_CHECKED = "checked";
    public static final String COLUMN_TO_DELETE = "must_delete";
    public static final String COLUMN_CHANGED_ON = "changed_on";

    public static final String COLUMN_CREATED = "created_on";

    private static final String DATABASE_CREATE = "create table "
            + TABLE_TODOS + "( "
            + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_UID + " varchar(50), "
            + COLUMN_NAME + " text not null, "
            + COLUMN_CHECKED + " integer default 0, "
            + COLUMN_TO_DELETE + " integer default 0, "
            + COLUMN_CHANGED_ON + " datetime "
            + COLUMN_CREATED + " datetime "
            +");";

have a look at http://stackoverflow.com/questions/754684/how-to-insert-a-sqlite-record-with-a-datetime-set-to-now-in-android-applicatio — John Boker, May 25 '12 at 17:02
How are those columns declared when you create the database? The most likely answer is that you are violating a constraint. — Barak, May 25 '12 at 17:04
@JohnBoker, I think I am using DATETIME as a string with the ' at the begging and the end of the DATETIME function, and I am already using executeSQL instead of the android insert function. — Vlad Nicula, May 25 '12 at 18:03
@Barak, updated the question, with the String quest to create the database table. — Vlad Nicula, May 25 '12 at 18:04

score 5 · Accepted Answer · answered May 25 '12 at 18:11

Your problem is you are declaring a datatype that doesn't exist in SQlite. From the docs:

" 1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing 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 B.C. according to the proleptic Gregorian calendar. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions."

So change your datatype in the database, recreate it, and change your methods to insert/update/retrieve data from those columns to match the proper datatype and things should work better for you.

SQLite and databases in general are not my strong point. Thanks! — Vlad Nicula, May 25 '12 at 18:18
What about timestamps like in this example: http://androidcookbook.com/Recipe.seam?recipeId=413 — Kristy Welsh, Mar 11 '13 at 23:23

score 3 · Answer 2 · answered May 25 '12 at 18:22

3

You are missing a comma before your created_on column in your table create statement.

+ COLUMN_CHANGED_ON + " datetime "

should be

+ COLUMN_CHANGED_ON + " datetime, "

answered May 25 '12 at 18:22

Shellum

3,159
2
21
26

score 2 · Answer 3 · edited Jul 30 '13 at 13:14

According to their own documentation SQLite, would use the correct data type NUMERIC to record DATETIME http://www.sqlite.org/datatype3.html following the rules of affinity data type. See the table of Affinity.

COLUMN_CHANGED_ON + " datetime "

should be

COLUMN_CHANGED_ON + " NUMERIC, "

Using correct affinity datatype you can compare values e.g.

SELECT b < '40', b < '60', b < '600' FROM t1;

How to corectly insert a datetime value in SQLite under Android?

3 Answers3