CODEWITHSUNDEEP
javaandroidsqlite
gael phasepro
gael phasepro

Reputation: 3

SQLite in android order by date and time not working

I'm currently developing an application for android and I'm trying to get the last entry in my database ordered by a column that contains a String with the date and time.

My problem is that the result of the query is always the first date and not the last one.

I already tried ordered my column by "date", "datetime" and by "DESC" and "ASC".

the following is the query to create the table that contains the date in question

private static final String CREATE_TABLE_TIMBRAGE="CREATE TABLE "
            + TABLE_TIMBRAGE + "(" + COLUMN_T_TIMBRAGE_PK_TIMBRAGE + " INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
            + COLUMN_T_TIMBRAGE_DATETIMBRAGE + " DATETIME NOT NULL, " + COLUMN_T_TIMBRAGE_INOUT + " INT NOT NULL, "
            + COLUMN_T_TIMBRAGE_FK_EMPLOYE + " INT NOT NULL, "+ "FOREIGN KEY(" + COLUMN_T_TIMBRAGE_FK_EMPLOYE + ") REFERENCES " + TABLE_EMPLOYE + "("+COLUMN_T_EMPLOYE_PK_EMPLOYE+") "

here is my formatter for the dates

SimpleDateFormat FORMATTER=new SimpleDateFormat("dd.MM.yyyy'-'HH:mm:ss", Locale.FRANCE);

here is how I'm adding into this table

ContentValues values = new ContentValues();
        values.put(COLUMN_T_TIMBRAGE_DATETIMBRAGE, FORMATTER.format(timbrage.getDateTimbrage()));
        values.put(COLUMN_T_TIMBRAGE_INOUT, timbrage.getInOut());
        values.put(COLUMN_T_TIMBRAGE_FK_EMPLOYE, timbrage.getFK_Employe());

        SQLiteDatabase db = this.getWritableDatabase();
        db.insert(TABLE_TIMBRAGE, null, values);
        db.close();

and finally here is my query that should get the last entry

String query2 = "Select * FROM " + TABLE_TIMBRAGE + " WHERE " + COLUMN_T_TIMBRAGE_FK_EMPLOYE + " =  \"" + FK_Employe + "\"" + " ORDER BY datetime("+ COLUMN_T_TIMBRAGE_DATETIMBRAGE +") ASC LIMIT 1 ";
                Cursor cursor2 = db.rawQuery(query2, null);

The result that I get is

"Thu May 23 15:34:58 GMT+02:00 2019"

but I should get

"Thu May 23 15:56:32 GMT+02:00 2019"

Upvotes: 0

Views: 197

Answers (2)

H_K
H_K

Reputation: 60

Try this:

SELECT  * FROM  + YOUR_TABLE_NAME +  ORDER BY + YOUR_DATETIME_COLUMN_NAME + DESC

And I have a suggestion that you should use DATETIME DEFAULT CURRENT_TIMESTAMP as your DATETIME column type. This auto-updates the DATETIME whenever you insert or update the row.

Upvotes: 0

Bled
Bled

Reputation: 66

Instead of saving date as a String, it is a good idea to convert said date to milliseconds since 01 Jan 1970. This way, the sorting is very straightforward and comparing numbers is more efficient. You can then, upon querying the data convert the milliseconds to a formatted Date.

Upvotes: 1

Related Questions