How to get dates from database and check if they overlaps?

Question

I have a sqlite database in which I store all events. I store StartTime and EndTime against each events in date format.

I have fragments for days with tabs and one activity which adds events. Events are shown in another fragments.

Now I want to check if events do overlap with each other and based on which app should display a message.

How to display message in added event activity when the user saves events.

How can I do this?

I have tried this way but still events get added with same start time and end time:

ImageButton imageButton = (ImageButton)findViewById(R.id.imgbtn_fab);
imageButton.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {
        eventTitle = title.getText().toString();
        EventTableHelper db = new EventTableHelper(getApplication());
        List<EventData> events;
        events = db.getAllEvents();

        for (EventData eventData : events) {

            String datefrom = eventData.getFromDate();

            String[] times = datefrom.substring(11, 16).split(":");
            minutesFrom = Integer.parseInt(times[0]) * 60 + Integer.parseInt(times[1]);

            String dateTo = eventData.getToDate();

            //times = dateTo.substring(11,16).split(":");
            String[] times1 = dateTo.substring(11, 16).split(":");
            minutesTo = Integer.parseInt(times1[0]) * 60 + Integer.parseInt(times1[1]);
        }

        if (eventTitle.length() == 0) {
            Toast.makeText(getApplicationContext(), "Title can not be empty.", Toast.LENGTH_LONG).show();
        }
        else if(minutesFrom == minutesTo) {
            Toast.makeText(getApplicationContext(),"Event with same time exists.",Toast.LENGTH_LONG).show();
        }
        else
        {
            db.addEvent(new EventData(eventTitle, startTime, endTime, dayOfWeek, location));
            setResult(RESULT_OK, i);
            finish();
        }
    }
});

I get dates like this:

c = Calendar.getInstance();

year = c.get(Calendar.YEAR);
month = c.get(Calendar.MONTH);
day = c.get(Calendar.DAY_OF_MONTH);

hour = c.get(Calendar.HOUR_OF_DAY);
minute = c.get(Calendar.MINUTE);

df = new SimpleDateFormat("HH:mm a");
datefrom = c.getTime();
startTime = String.valueOf(datefrom);

aTime = df.format(datefrom.getTime());
showFromTime.setText(aTime);

c.add(Calendar.HOUR_OF_DAY, 1);
dateto = c.getTime();
endTime = String.valueOf(dateto);

bTime = df.format(dateto.getTime());
showToTime.setText(bTime);

I have created an EventTableHelper:

public class EventTableHelper extends SQLiteOpenHelper {

    private static final String TABLE = "event";
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_FROM_DATE = "datefrom";
    private static final String KEY_TO_DATE = "dateto";
    private static final String KEY_LOCATION = "location";
    private static final String KEY_DAY_OF_WEEK = "dayofweek";

    public EventTableHelper(Context context) {
        super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    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" +  ")";
        db.execSQL(CREATE_EVENTS_TABLE);
    }
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS " + TABLE);

       // createTable(db);
       // onCreate(db);
    }

    public void addEvent(EventData event) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_TITLE,event.getTitle());
        values.put(KEY_FROM_DATE, event.getFromDate());
        values.put(KEY_TO_DATE,event.getToDate());
        values.put(KEY_DAY_OF_WEEK,event.getDayOfWeek());
        values.put(KEY_LOCATION,event.getLocation());

        db.insert(TABLE, null, values);

        db.close();
    }

   EventData getEvent(int id) {

        SQLiteDatabase db = this.getReadableDatabase();
        EventData eventData = new EventData();
        Cursor cursor = db.query(TABLE, new String[]{KEY_ID,
                        KEY_TITLE, KEY_FROM_DATE, KEY_TO_DATE, KEY_LOCATION}, KEY_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);

       if( cursor != null && cursor.moveToFirst() ) {
          eventData = new EventData(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2),
                   cursor.getString(3), cursor.getString(4));

       }
       return eventData;
    }

    public List<EventData> getAllEvents() {
        List<EventData> conList = new ArrayList<EventData>();

        String selectQuery = "SELECT  * FROM " + TABLE;

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

        if (cursor.moveToFirst()) {
            do {

                EventData event = new EventData();

                event.setId(Integer.parseInt(cursor.getString(0)));
                event.setTitle(cursor.getString(1));
                event.setFromDate(cursor.getString(2));
                event.setToDate(cursor.getString(3));
                event.setLocation(cursor.getString(4));

                conList.add(event);
            } while (cursor.moveToNext());
        }

        return conList;
    }
}

I tried to use getAll events function in an activity to get data.

Edit: Here's my current code.

@Override
public void onClick(View v) {
    eventTitle = title.getText().toString();
    EventTableHelper db = new EventTableHelper(getApplication());
    List<EventData> events;
    // events = db.getOverlappingEvents(startTime,endTime);

    if (eventTitle.length() == 0) {
        Toast.makeText(getApplicationContext(), "Title can not be empty.", Toast.LENGTH_LONG).show();
    }
    else if(db.doesEventOverlap(startTime,endTime))
    {
        Toast.makeText(getApplicationContext(), "Event exists", Toast.LENGTH_LONG).show();
    }
    else
    {
        db.addEvent(new EventData(eventTitle, startTime, endTime, dayOfWeek, location));
        setResult(RESULT_OK, i);
        finish();
    }
}

Answer 1

minutesTo, minutesFrom will only have the value for the last line in the db.

I would try to do a new function in EventTableHelper:

List<EventData> getOverlappingEvents(Date startTime, Date endTime) {
    List<EventData> conList = new ArrayList<EventData>();

    String selectQuery = "SELECT  * 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();

            event.setId(Integer.parseInt(cursor.getString(0)));
            event.setTitle(cursor.getString(1));
            event.setFromDate(cursor.getString(2));
            event.setToDate(cursor.getString(3));
            event.setLocation(cursor.getString(4));

            conList.add(event);
        } while (cursor.moveToNext());
    }
    return conList;
}

and in onClick:

events = db.getOverlappingEvents();
...

and instead of:

else if(minutesFrom == minutesTo)

this:

else if(events.size() > 0)

Actually it could be even further optimized, because it looks like you don't need the overlapping events data, only a boolean if there's at least one overlapping event:

boolean doesEventOverlap(Date startTime, Date 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;
}

and in onClick instead of:

else if(minutesFrom == minutesTo)

this:

else if(db.doesEventOverlap())