How Insert datetime value in SQLite database?

Insert DateTime value in SQLite database

Inserting the timestamp in SQLite can be done in multiple ways. You can either store complete timestamp which contains both date and time or you can store only the date or time. Check out the following ways of storing the date in the database.

Method 1

Inserting current timestamp by default

If you don’t want to insert the timestamp manually each time you create a row, you can do it by keeping the default value while creating the table. Use the DEFAULT keyword and one of the following data type.

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

Using datetime() while inserting the row

You can also insert datetime manually using datetime() function while inserting the row.

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

Method 3

Using java Date functions

You can also use java Date() and SimpleDateFormat() methods. Create a function that returns timestamp and uses the value while setting the content value for the date column.

Following function getDateTime() returns DateTime.

private String getDateTime() {

SimpleDateFormat dateFormat = new SimpleDateFormat(

"yyyy-MM-dd HH:mm:ss", Locale.getDefault());

Date date = new Date();

return dateFormat.format(date);

}

and use the value returned by getDateTime() to set content value.

ContentValues values = new ContentValues();

values.put('username', 'ckpatel');

values.put('created_at', getDateTime());

// insert the row