Why does strftime() insert string into integer column

Question

I have a 'created' column which is INTEGER and this is how I insert sqlite date into it:

 ContentValues vals = new ContentValues();
 vals.put(Column._CREATED_DATE, "strftime('%s','now')");
 db.insert(Table.ARTICLE.name, null, vals);

Unbelievably he inserts strftime('%s','now') string into INTEGER column!

What the heck?

EDIT: when I run insert into article (created) values (strftime("%s","now")) in an SQLite browser, proper integer value is inserted...

Typically, when you cram stuff into ContentValues expect them to be properly escaped & put into SQLite exactly as you specify them, i.e. you cannot use them as a shortcut to execute functions on data. Wouldn't it just be simpler to just add a default value, e.g. created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP or something like that. — Jens
@Jens right on, and how can I format this value when querying the table? BTW I consider this an answer, so if you want to, write this post as an answer... — myro

Jens Jens · Accepted Answer · 2012-05-07T13:08:28

Add a default value to your table instead, e.g.

CREATE TABLE article (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    _created_date INTEGER DEFAULT (strftime('%s', 'now'))
);

This will put unix epoch in _created_date if you do not set its column value when creating the row. If you want a timestamp (e.g. 2012-05-07 13:07:58) you define it as

_created_date INTEGER DEFAULT CURRENT_TIMESTAMP

Why does strftime() insert string into integer column

1 Answers