CODEWITHSUNDEEP
androidsqlite
tenten
tenten

Reputation: 1276

Getting date difference in sqlite

I want to get date difference between today and expiring day. This is the code I implemented. But this is not returning the right output.

public String[] getDaysList(){
    Cursor cursor = db.query("COUPON", null, null, null, null, null, null );
    if(cursor.getCount()<1){
        cursor.close();
        return null;
    }
    String[] array = new String[cursor.getCount()];
    int i=0;
    while(cursor.moveToNext()){

        String days = "(julianday('now') - julianday(EXPIRED_DATE))";
        array[i] = days;
        i++;
    }
    return array;
}

This returns (julianday('now') - julianday(EXPIRED_DATE)). Please help me to get date difference as string to a array here.

Upvotes: 3

Views: 840

Answers (2)

CL.
CL.

Reputation: 180030

The now modifier returns not only the date but also the time.

To change the timestamp to the start of the date, use the date() function:

SELECT julianday(date('now')) - julianday(EXPIRED_DATE) FROM ...

(If the expired column also contains time values, you have to use date() for it, too.)

And to actually execute this, you have to give it to the database:

public String[] getDaysList() {
    String days = "julianday(date('now')) - julianday("+EXPIRED_DATE+")";
    Cursor cursor = db.query("COUPON",
                             new String[]{ days },  // query returns one column
                             null, null, null, null, null);
    try {
        String[] array = new String[cursor.getCount()];
        int i = 0;
        while (cursor.moveToNext()) {
            array[i++] = cursor.getString(0);       // read this column
        }
        return array.length > 0 ? array : null;
    } finally {
        cursor.close();
    }
}

(And the number of days is not a string; consider using int[] instead.)

Upvotes: 1

Ganesh Gudghe
Ganesh Gudghe

Reputation: 1387

Hi please try these one 

    Cursor  cursor = db.rawQuery("SELECT julianday('now') - julianday(DateCreated) FROM COUPON", null);

 if (cursor.moveToFirst()) {
                do {
                   array[i]=cursor.getString(0)
                } while (cursor.moveToNext());

            }

Upvotes: 0

Related Questions