Best Way to Work with Dates in Android SQLite

Best way to work with dates in Android SQLite

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

What is the preferred way to work with dates in Android SQLite and WHY?

Personally I wouldn't worry all that much. I always use ints with unix timestamps, you're right they'll use less memory. Also they're pretty self explanatory, but only precise to seconds, nothing more. If you need something more precise (milliseconds etc) then you need to get creative.

However storing as plain text is fine, SQLite doesn't have the functionality to store datetime, so the three options suggested at http://www.sqlite.org/datatype3.html#datetime are TEXT, REAL and INT.

Given that these are the ways they actually tell you to store dates and time, and given that there is no official word on which is preferable, I wouldn't worry too much. Choose what you are comfortable with and which suits your needs best.

Android - How to properly store Date and Time in SQLite?

According to Sqlite documentation (section 2.2), you should store your date in one of the following ways:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

The comparison problem can be solved using the strftime function that allows you to compere dates regardeless their storage type (TEXT, REAL or INTEGER).

Let's say that you want to get all the rows in your database that have date Jenuary. You can do:

SELECT * FROM table
WHERE strftime('%m', your_date_column) == '01'

Or maybe you want get all the rows with time 09:40:

SELECT * FROM table
WHERE strftime('%H:%M', your_date_column) == '09:40'

Or also:

SELECT * FROM table
WHERE strftime('%s', your_date_column) < strftime('%s','now')

will return you all the rows with date preceding the current date (now).

SUMMARY: The function strftime abstracts the database storing mode, so it doesn't actually metter which mode you use.

How to manage dates in Android sqLite database when I care only about the date (which is to be unique) and not the time?

To get a timestamp for the start of the current day (in seconds) in SQLite you can use the strftime function like so:

strftime('%s','now','start of day')

Alternatively, you can build this value in java using Calendar:

Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);
long time = calendar.getTimeInMillis();

You can use a uniqueness constraint in your CREATE TABLE command to enforce that each combination of timestamp and value is unique across all rows.

CREATE TABLE tableName (..., UNIQUE(column1, column2));

If you want, in the same statement you can specify a conflict algorithm to handle cases where an insert or update creates a violation of this uniqueness constraint. Alternatively, you can specify it when you try to do the insert/update:

SQLiteDatabase db = ...; // get database
long rowid = db.insertWithOnConflict(TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);

CONFLICT_IGNORE won't raise an exception and won't write a new row if it creates a constraint violation. In that case insertWithOnConflict() will return -1, so that way you can tell if the insert succeeded or not.

Comparing dates in android sqlite database

The problem here is that your date literals are in a non standard (and likely incorrect) format. Appreciate that the following inequality holds true:

'2020-10-01' > '2020-1-01' AND '2020-10-01' < '2020-2-01'

This is true because the text 10 is lexicographically larger than just 1, but also less than 2. To avoid this problem, use proper date literals:

String sql = "SELECT * FROM " + tableName + " WHERE SALESDATE BETWEEN '2020-01-01' AND '2020-02-01';"
Cursor cursor = db.rawQuery(sql, null);

Note that SQLite does not actually have a formal date type. Thus, it is very important to always store your dates in SQLite using a proper ISO format.

How to add date in sqlite database

Now when you want to insert date to database, you can use this code.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String date = sdf.format(new Date());

In database insert the string 'date'

The date format in sqlite should be of following format:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD 

For more details, have a look: http://www.sqlite.org/lang_datefunc.html

Android sqlite store date types

No it is not.

But you can use Date.getTime(), to have the timestamp (long in JAVA, INTEGER in SQLite), to be able to store and retrieve the date.

To retrieve the date from database, you will do :

new Date(cursor.getLong(yourColumnIndex));

And to store it :

contentValues.put("yourDateColumn", date.getTime());

---

Related Topics