Best way to work with dates in Android SQLite [clo

Question

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions:

1. What type should I use to store dates in SQLite (text, integer, ...)?
2. Given the best way to store dates how do I store It properly using ContentValues?
3. What's the best way to retrieve the date from the SQLite database?
4. How to make a sql select on SQLite, ordering the results by date?

Answer 1

1 -Exactly like StErMi said.

2 - Please read this: http://www.vogella.de/articles/AndroidSQLite/article.html

3 -

Cursor cursor = db.query(TABLE_NAME, new String[] {"_id", "title", "title_raw", "timestamp"}, 
                "//** YOUR REQUEST**//", null, null, "timestamp", null);

see here:

Query() in SQLiteDatabase

4 - see answer 3

Answer 2

"SELECT  "+_ID+" ,  "+_DESCRIPTION +","+_CREATED_DATE +","+_DATE_TIME+" FROM "+TBL_NOTIFICATION+" ORDER BY "+"strftime(%s,"+_DATE_TIME+") DESC";

Answer 3

The best way is to store the dates as a number, received by using the Calendar command.

//Building the table includes:
StringBuilder query=new StringBuilder();
query.append("CREATE TABLE "+TABLE_NAME+ " (");
query.append(COLUMN_ID+"int primary key autoincrement,");
query.append(COLUMN_DATETIME+" int)");

//And inserting the data includes this:
values.put(COLUMN_DATETIME, System.currentTimeMillis()); 

Why do this? First of all, getting values from a date range is easy. Just convert your date into milliseconds, and then query appropriately. Sorting by date is similarly easy. The calls to convert among various formats are also likewise easy, as I included. Bottom line is, with this method, you can do anything you need to do, no problems. It will be slightly difficult to read a raw value, but it more than makes up that slight disadvantage with being easily machine readable and usable. And in fact, it is relatively easy to build a reader (And I know there are some out there) that will automatically convert the time tag to date as such for easy of reading.

It's worth mentioning that the values that come out of this should be long, not int. Integer in sqlite can mean many things, anything from 1-8 bytes, but for almost all dates 64 bits, or a long, is what works.

EDIT: As has been pointed out in the comments, you have to use the cursor.getLong() to properly get the timestamp if you do this.

Answer 4

1. As presumed in this comment, I'd always use integers to store dates.

2. For storing, you could use a utility method

   public static Long persistDate(Date date) {
       if (date != null) {
           return date.getTime();
       }
       return null;
   }
   

   like so:

   ContentValues values = new ContentValues();
   values.put(COLUMN_NAME, persistDate(entity.getDate()));
   long id = db.insertOrThrow(TABLE_NAME, null, values);
   

3. Another utility method takes care of the loading

   public static Date loadDate(Cursor cursor, int index) {
       if (cursor.isNull(index)) {
           return null;
       }
       return new Date(cursor.getLong(index));
   }
   

   can be used like this:

   entity.setDate(loadDate(cursor, INDEX));
   

4. Ordering by date is simple SQL ORDER clause (because we have a numeric column). The following will order descending (that is newest date goes first):

   public static final String QUERY = "SELECT table._id, table.dateCol FROM table ORDER BY table.dateCol DESC";
   
   //...
   
       Cursor cursor = rawQuery(QUERY, null);
       cursor.moveToFirst();
   
       while (!cursor.isAfterLast()) {
           // Process results
       }
   

Always make sure to store the UTC/GMT time, especially when working with java.util.Calendar and java.text.SimpleDateFormat that use the default (i.e. your device's) time zone. java.util.Date.Date() is safe to use as it creates a UTC value.

Answer 5

Best way to store datein SQlite DB is to store the current DateTimeMilliseconds. Below is the code snippet to do so_

1. Get the DateTimeMilliseconds

public static long getTimeMillis(String dateString, String dateFormat) throws ParseException {
    /*Use date format as according to your need! Ex. - yyyy/MM/dd HH:mm:ss */
    String myDate = dateString;//"2017/12/20 18:10:45";
    SimpleDateFormat sdf = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);
    Date date = sdf.parse(myDate);
    long millis = date.getTime();

    return millis;
}

2. Insert the data in your DB

public void insert(Context mContext, long dateTimeMillis, String msg) {
    //Your DB Helper
    MyDatabaseHelper dbHelper = new MyDatabaseHelper(mContext);
    database = dbHelper.getWritableDatabase();

    ContentValues contentValue = new ContentValues();
    contentValue.put(MyDatabaseHelper.DATE_MILLIS, dateTimeMillis);
    contentValue.put(MyDatabaseHelper.MESSAGE, msg);

    //insert data in DB
    database.insert("your_table_name", null, contentValue);

   //Close the DB connection.
   dbHelper.close(); 

}

Now, your data (date is in currentTimeMilliseconds) is get inserted in DB .

Next step is, when you want to retrieve data from DB you need to convert the respective date time milliseconds in to corresponding date. Below is the sample code snippet to do the same_

1. Convert date milliseconds in to date string.

public static String getDate(long milliSeconds, String dateFormat)
{
    // Create a DateFormatter object for displaying date in specified format.
    SimpleDateFormat formatter = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);

    // Create a calendar object that will convert the date and time value in milliseconds to date.
    Calendar calendar = Calendar.getInstance();
    calendar.setTimeInMillis(milliSeconds);
    return formatter.format(calendar.getTime());
}

2. Now, Finally fetch the data and see its working...

public ArrayList<String> fetchData() {

    ArrayList<String> listOfAllDates = new ArrayList<String>();
    String cDate = null;

    MyDatabaseHelper dbHelper = new MyDatabaseHelper("your_app_context");
    database = dbHelper.getWritableDatabase();

    String[] columns = new String[] {MyDatabaseHelper.DATE_MILLIS, MyDatabaseHelper.MESSAGE};
    Cursor cursor = database.query("your_table_name", columns, null, null, null, null, null);

    if (cursor != null) {

        if (cursor.moveToFirst()){
            do{
                //iterate the cursor to get data.
                cDate = getDate(cursor.getLong(cursor.getColumnIndex(MyDatabaseHelper.DATE_MILLIS)), "yyyy/MM/dd HH:mm:ss");

                listOfAllDates.add(cDate);

            }while(cursor.moveToNext());
        }
        cursor.close();

    //Close the DB connection.
    dbHelper.close(); 

    return listOfAllDates;

}

Hope this will help all! :)

Answer 6

You can use a text field to store dates within SQLite.

Storing dates in UTC format, the default if you use datetime('now') (yyyy-MM-dd HH:mm:ss) will then allow sorting by the date column.

Retrieving dates as strings from SQLite you can then format/convert them as required into local regionalised formats using the Calendar or the android.text.format.DateUtils.formatDateTime method.

Here's a regionalised formatter method I use;

public static String formatDateTime(Context context, String timeToFormat) {

    String finalDateTime = "";          

    SimpleDateFormat iso8601Format = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");

    Date date = null;
    if (timeToFormat != null) {
        try {
            date = iso8601Format.parse(timeToFormat);
        } catch (ParseException e) {
            date = null;
        }

        if (date != null) {
            long when = date.getTime();
            int flags = 0;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
            flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;

            finalDateTime = android.text.format.DateUtils.formatDateTime(context,
            when + TimeZone.getDefault().getOffset(when), flags);               
        }
    }
    return finalDateTime;
}