Sqlite datetime insertion

In Sqlite if you insert a datetime as time in milliseconds or normal date time with your hand into a datetime column, Sqlite understands/inserts them as a regular text but does not give an error. And you need to use datetime(millisTill70, 'unixepoch') for the milliseconds or you need any other functions which are specified in Sqlite in order to convert the inserted regular "text" value to a date time which Sqlite can understand on queries.

In order to let the Sqlite understand the date time value you inserted directly w/o explicit conversion I found two options;
1. You need to find a function that returns the number of days since noon in Greenwich on November 24, 4714 B.C. to now in Java for Julian time which Sqlite's default.
2. Convert the input date time in ISO8601 format which Sqlite understands as a date time value.

I have chosen the second alternative;
/**
* Get at the moment within ISO8601 format.
* @return
* Date and time in ISO8601 format.
*/
private String getNow()
{
Calendar c = Calendar.getInstance();
SimpleDateFormat sdf =
new SimpleDateFormat(
getResources().getString(R.string.DATE_FORMAT_ISO8601));
return sdf.format(c.getTime());
}

In strings.xml specify the ISO standard;
"yyyy-MM-dd'T'HH:mm:ss.SSS"

Comments

Unknown said…
Thanks, just what I needed!
Anonymous said…
Many thanks i were searching for this as well.
as using the "SETDATE" just saves a integer and does not work as expected, but using this method works.

Popular posts from this blog

3 Must Haves of a Status Report

AWS encryption chart (SSE-S3 vs SSE-KMS vs SSE-C)

Configure hosts File in Android