sqlite timestamp is not working

Question

I am trying to build an sqlite db, and having timestamp as one of the columns.

This is my structure:

CREATE TABLE logs (
log_id INTEGER PRIMARY KEY AUTOINCREMENT,
log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
error_text TEXT
)

but when I am inserting into the table the log_date field remain blank:
INSERT INTO logs(error_text)VALUES("some error text")

What am I doing wrong?

Are you sure the `log_date` field remains blank? Maybe you're not selecting it in your query. — Octoshape, Nov 27 '13 at 11:56

score 0 · Answer 1 · answered Nov 27 '13 at 11:57

0

Don't use null as placeholder in your insert query

This works

INSERT INTO logs(error_text)
VALUES("some error text");

This not

INSERT INTO logs(error_text, log_date)
VALUES("some error text", null);

SQLFiddle demo

answered Nov 27 '13 at 11:57

juergen d

201,996
37
293
362

Isn't that what he's doing? – Octoshape Nov 27 '13 at 12:00
Obviously not. If he did then the code he provided would work. – juergen d Nov 27 '13 at 12:01
1

Maybe the problem lies somewhere else? Old version of SQLite or something, but I dont know, let's wait for OP to reply. – Octoshape Nov 27 '13 at 12:02
@juergend Thanks for your response but this is exactly what I am doing... do you have any other solutions? – user2979757 Nov 27 '13 at 18:29
@Octoshape how do I check my sqLite version? – user2979757 Nov 27 '13 at 18:30
@user2979757 http://stackoverflow.com/questions/9646353/how-to-find-sqlite-database-file-version – Octoshape Nov 27 '13 at 20:17

score 0 · Answer 2 · answered Nov 27 '13 at 21:44

I just had potentially the same issue, and found your question while looking for a solution.

In my case the issue is that SQLite Administrator is showing '0' in all the log_date columns. However writing a "SELECT * from logs" in my C# application shows that the data is correct, even from previous sessions, before I'd noticed the issue (Changed names to match your schema).

So I'm putting this down to a bug in SQLite Administrator, although did not find confirmation with a quick Google search.

sqlite timestamp is not working

2 Answers2

SQLFiddle demo