Friday, 23 September 2011

SQLite - Saving date as integer

One of the differences between the "normal" SQL database and SQLite database is in the second one, date is not introduced as basic type. Because the importance of the use of date, I am going to introduce one way to save the date as integer.

As I have explained in this post, for all the unix systems, there is something called "The epoch", which is 1st January 1970 00:00:00 GMT. The date library of Java allow the user to create a date using the milliseconds since "The epoch". In fact, the method System.currentTimeMillis() in Java returns the current time in milliseconds regarding "The epoch". The problem is the constructor of date doesn't accept integer as parameter, so, a cast to long is needed.

Saving the date as integer
The follow example of code allow the user to save the actual time as integer in the database, where the column "time" has been defined as integer.

long nowLong = System.currentTimeMillis()/1000;
int nowInt = (int)nowLong;
Log.i(LOG_TAG, "nowInt " + nowInt);
ContentValues contentValues = new ContentValues();
contentValues.put("time", nowInt);
database.insert(DATABASE_TABLE2, null, contentValues);

Retrieve the value in the database
The follow code retrieve the first value in the database in the column "time" and convert it to Date.

Cursor dateCursor = database.query (DATABASE_TABLE2, new String[] {"time"},
null, null, null, null, null);
int dateInt = 0;
if (dateCursor.getCount() != 0) {
dateCursor.moveToFirst();
dateInt = dateCursor.getInt(dateCursor.getColumnIndex("time"));
}
long dateLong = new Long(dateInt);
dateLong *= 1000;
Date date = new Date (dateLong);

You can check the post about the Cursor to retrieve more data. 

No comments:

Post a Comment