samedi 6 février 2016

How to query to check time of event for particular day?

I have created events which has start time, end time, day of week. I am storing values in database.

Now I want to check if the events overlap with each other. I have created one query which checks if start time and end time dose overlap or not.

But it is checking for all the days of week. If event exists on monday at some time then it wont allow to create on tuesday for the same time.

I now only want to check if events overlap of a particular day. How to achieve this?

boolean doesEventOverlap(String startTime, String endTime) {
    String selectQuery = "SELECT COUNT(*) FROM " + TABLE

            + " WHERE (" + KEY_FROM_DATE + " < '" + startTime
            + "' AND '" + startTime + "' < " + KEY_TO_DATE + ") OR "

            + " (" + KEY_FROM_DATE + " < '" + endTime
            + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "

            + " (" + KEY_FROM_DATE + " < '" + startTime
            + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "

            + " ('" + startTime + "' < " + KEY_FROM_DATE
            + " AND " + KEY_TO_DATE + " < '" + endTime + "')";

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            EventData event = new EventData();
            return cursor.getInt(0) > 0;
        } while (cursor.moveToNext());
    }
    return false;
}

This is event table

  public void createTable(SQLiteDatabase db){
    String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE+ "("
            + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_TITLE + " TEXT,"
            + KEY_FROM_DATE + " DATE,"
            + KEY_TO_DATE + " DATE,"
            + KEY_DAY_OF_WEEK + " TEXT,"
            + KEY_LOCATION + " TEXT,"
            + KEY_NOTIFICATION_TIME + "TEXT" + ")";
    db.execSQL(CREATE_EVENTS_TABLE);
}

Thank you..

Aucun commentaire:

Enregistrer un commentaire