update sqlite column with current date time in android

Question

I have a SQLite table in my android app which has a "MODIFIED" column. It basically stores when (date and time) the current row was first inserted or updated.

If i use update() function of SQLite DB in android, I can't figure out how to put a datetime value in the ContentValues parameter.

If I use execSql() method, i can use the DATETIME() sql function to get the current time is set it to the "MODIFIED" column. But this function does not have a return value. I wouldn't know if the update was successful or not.

Anyone who has any suggestions?

score 1 · Answer 1 · answered Apr 23 '12 at 02:15

1

You just must do something like:

ContentValues values = new ContentValues();
values.put("modified", currentTime);
db.update("table", values, "_id = XXX", null);

Replace _id = XXX with the condition you want to use to update the rows. Update method returns the number of updated rows.

Also, you can use an integer instead of DATETIME (since they actually does not exist in sqlite http://www.sqlite.org/datatype3.html) type for that column and pass System.currentTimeMillis() as currentTime.

answered Apr 23 '12 at 02:15

Cristian

198,401
62
356
264

The initial value of "MODIFIED" is of this format `YYYY-MM-DD HH:MM:SS` so if I update to `System.currentTimeMillis()`, it will have a different format and my order by clause won't work. – John Ng Apr 23 '12 at 02:45
Then just use that format, what's the problem? Take a look at the `SimpleDateFormat` class ;) – Cristian Apr 23 '12 at 02:53

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

Another approach is to create a 'TRIGGER' that updates MODIFIED automatically on each 'UPDATE' with SQLite itself, read about it here.

edited May 23 '17 at 12:21

Community

1
1

answered Apr 23 '12 at 02:55

Sam

86,580
20
181
179

this is what i'm trying right now. This seems more appropriate. – John Ng Apr 23 '12 at 03:27

update sqlite column with current date time in android

2 Answers2