Inserting current date and time in SQLite database

Question

I want to create a table in SQLite in which one of the field is for date, in which date and time of current instance should save. Which data type should I use?

I'm planning to use 'timestamp'. How to insert current timestamp value to the field? Also how to write content values for this date field?

Answer 1

SQLite supports the standard SQL variables CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP:

INSERT INTO Date (LastModifiedTime) VALUES(CURRENT_TIMESTAMP) 

The default data type for dates/times in SQLite is TEXT.

ContentValues do not allow to use generic SQL expressions, only fixed values, so you have to read the current time in Java:

cv.put("LastModifiedTime",        new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())); 

Answer 2

INSERT INTO Date (LastModifiedTime) VALUES(DateTime('now')) 

Use this site for further reference.

Answer 3

To get the current local(system) time, add the 'localtime' option:

select datetime('now', 'localtime');