Insert/Update Proper date in SQLLite Table in Android

Question

How to insert a proper date value using SQLiteDatabase.update/SQLiteDatabase.insert. The problem appears to be that ContentValues takes only Strings as input of its pairs.

There is a solution here

For quick reference, this is the solution provided in that question:

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

The problem with this solution is, it doesnot insert a proper 'Date' value into a Date column, but inserts the DAte as a text. I know this because when i retrieve the data (select) and do date operations on it, it doen't work.

(TO be precise, datetime(DATE_COLUMN,'localtime') returns null value)

Can someone help me with my problem? I have already wasted hours :(

Answer 1

SQLite does not support a Date type as seen in some other DBMS. Instead dates are stored either as text in a well known format or as numeric using the unix epoch.

The datetime function reads text in different formats, including YYYY-MM-DD HH:MM:SS and returns them as text in format YYYY-MM-DD HH:MM:SS, so in your case, datetime will return what is already stored in the database in the same format, but will change timezone to localtime (assuming it is in utc before).

Android will make the result of datetime available through Cursor.getString. You can get the Date object back by using DateFormat.parse.

The following should more or less work (without knowing your database schema):

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
[...]
Cursor cursor = mDb.query(DATABASE_TABLE, new String[]{"datetime("+DATE_COLUMN+",'localtime')"}, null, null, null. null, null);
cursor.moveToNext();
Date parsed = dateFormat.parse(cursor.getString(0));

Reference: https://sqlite.org/lang_datefunc.html