How to insert a SQLite record with a datetime set to 'now' in Android application?

Question

Say, we have a table created as:

  
create table notes (_id integer primary key autoincrement, created_date date)

To insert a record, I'd use

ContentValues initialValues = new ContentValues(); 
initialValues.put("date_created", "");
long rowId = mDb.insert(DATABASE_TABLE, null, initialValues);

But how to set the date_created column to "now"? To make it clear, the

initialValues.put("date_created", "datetime('now')");

Is not the right solution. It just sets the column to "datetime('now')" text.

Answer 1

To me, the problem looks like you're sending "datetime('now')" as a string, rather than a value.

My thought is to find a way to grab the current date/time and send it to your database as a date/time value, or find a way to use SQLite's built-in (DATETIME('NOW')) parameter

Check out the anwsers at this SO.com question - they might lead you in the right direction.

Hopefully this helps!

Answer 2

There are a couple options you can use:

1. You could try using the string "(DATETIME('now'))" instead.
2. Insert the datetime yourself, ie with System.currentTimeMillis()
3. When creating the SQLite table, specify a default value for the created_date column as the current date time.
4. Use SQLiteDatabase.execSQL to insert directly.

Answer 3

You cannot use the datetime function using the Java wrapper "ContentValues". Either you can use :

• SQLiteDatabase.execSQL so you can enter a raw SQL query.

  mDb.execSQL("INSERT INTO "+DATABASE_TABLE+" VALUES (null, datetime()) ");
  

• Or the java date time capabilities :

  // set the format to sql date time
  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
  Date date = new Date();
  ContentValues initialValues = new ContentValues(); 
  initialValues.put("date_created", dateFormat.format(date));
  long rowId = mDb.insert(DATABASE_TABLE, null, initialValues);
  

Answer 4

In my code I use DATETIME DEFAULT CURRENT_TIMESTAMP as the type and constraint of the column.

In your case your table definition would be create table notes (_id integer primary key autoincrement, created_date date default CURRENT_DATE).

Answer 5

hey what is the use of second argument in "insert" which is set as null. Can u give me description of that parameter.

Answer 6

If I try to insert one more row using contentvalue.put(, ),the previous row is getting overridden. when I used "select" query only one row which is last insertd row is coming. How is insert multiple rows.?