How to get dates from a database and check if they overlap?

Question

I have a sqlite database that stores all events. I save StartTime and EndTime for each event in a date format.

I have snippets for days with tabs and one activity that adds events. Events are shown in other fragments.

Now I want to check if events overlap with each other and based on which application the message should be displayed.

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

How can i do this?

I tried this way, but still events are added with the same start 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 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 using the getAll event function in activity to get data.

Edit: Here is 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 a value for the last line in db.

I would try to make 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) 

 else if(events.size() > 0) 

In fact, it could be further optimized, because it seems that you do not need data from overlapping events, only logical, if there is 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:

 else if(minutesFrom == minutesTo) 

 else if(db.doesEventOverlap())