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

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.

Make sure that the field is not marked as 'not null' at the same time as you are trying to insert a default time stamp using the expression "(DATETIME('now'))"

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);
  

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
)

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!

This code example may do what you want:

http://androidcookbook.com/Recipe.seam?recipeId=413

Based on @e-satis answer I created a private method on my "DBTools" class so adding current date is now really easy:

...
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
...
        private String getNow(){
            // set the format to sql date time
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date date = new Date();
            return dateFormat.format(date);
        }
...

Use it now like this: values.put("lastUpdate", getNow());

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.

You can use the function of java that is:

ContentValues cv = new ContentValues();
cv.put(Constants.DATE, java.lang.System.currentTimeMillis());  

In this way, in your db you save a number.
This number could be interpreted in this way:

    DateFormat dateF = DateFormat.getDateTimeInstance();
    String data = dateF.format(new Date(l));

Instead of l into new Date(l), you shoul insert the number into the column of date.
So, you have your date.
For example i save in my db this number : 1332342462078
But when i call the method above i have this result: 21-mar-2012 16.07.42

Method 1

CURRENT_TIME – Inserts only time
CURRENT_DATE – Inserts only date
CURRENT_TIMESTAMP – Inserts both time and date

CREATE TABLE users(
    id INTEGER PRIMARY KEY,
    username TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Method 2

db.execSQL("INSERT INTO users(username, created_at) 
            VALUES('ravitamada', 'datetime()'");

Method 3 Using java Date functions

private String getDateTime() {
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        Date date = new Date();
        return dateFormat.format(date);
}

ContentValues values = new ContentValues();
values.put('username', 'ravitamada');
values.put('created_at', getDateTime());
// insert the row
long id = db.insert('users', null, values);

Works for me perfect:

    values.put(DBHelper.COLUMN_RECEIVEDATE, geo.getReceiveDate().getTime());

Save your date as a long.