CODEWITHSUNDEEP
androidsqlitecontent-values
droidguy
droidguy

Reputation: 1101

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.

Upvotes: 110

Views: 234631

Answers (9)

AlokJoshiOfAarmax
AlokJoshiOfAarmax

Reputation: 5

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'))"

Upvotes: 0

Rikin Patel
Rikin Patel

Reputation: 9383

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

Upvotes: 35

Cedric Simon
Cedric Simon

Reputation: 4659

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

Upvotes: 3

Gautier Hayoun
Gautier Hayoun

Reputation: 2922

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
)

Upvotes: 45

Stefan Beike
Stefan Beike

Reputation: 11

Works for me perfect: 

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

Save your date as a long.

Upvotes: 1

Marco Gallella
Marco Gallella

Reputation: 813

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

Upvotes: 5

Bite code
Bite code

Reputation: 596623

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

Upvotes: 199

sooniln
sooniln

Reputation: 14646

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.

Upvotes: 8

Jared Harley
Jared Harley

Reputation: 8337

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!

Upvotes: 7

Related Questions