CODEWITHSUNDEEP
javaandroidsqlitedatetime
sjain
sjain

Reputation: 23354

How to check that certain datetime is in between the adjacent row dates?

I have the following table column values:

2013-06-28T13:00:00.000Z    Motion
2013-06-28T18:15:00.000Z    Static  (row2)
2013-07-03T13:57:22.000Z    Moving  (row3)
2013-07-03T13:59:00.000Z    Motion

I want to check if dates 29-06-2013T01:00:00Z and 29-06-2013T05:00:00Z are both Static. Similarly, I want to check for dates 30-06-2013T01:00:00Z and 30-06-2013T05:00:00Z. Obviously this is true if we consider the above table values because both the dates are between row2 and row3.

How to do that with sqlite ?

I am getting the dates 29-06-2013 and 30-06-2013. So first I need to set times 1am and 5am to both of these dates and then check if they both are Static.

I tried the following:

Calendar cal1 = Calendar.getInstance();
cal1.setTime(input1.parse(startDate.get(0))); //29-06-2013
cal1.add(Calendar.AM, 1); //Added 1am to date 29-06-2013
weekHours = cal1.getTime(); 

Cursor chkCur1 = dbAdapter.rawQuery("select movement from user where Date <= '"
    + input1.format(weekHours) + "' order by Date asc LIMIT 1", null);

chkCur1.moveToFirst();

if(!chkCur1.isAfterLast() && chkCur1.getString(0).equals("Static"))
{
cal1.add(Calendar.AM, 5); //Added 5am to date 29-06-2013
weekHours = cal1.getTime();
}

chkCur1 = dbAdapter.rawQuery("select movement from user where Date <= '"
    + input1.format(weekHours) + "' order by Date asc LIMIT 1", null);

chkCur1.moveToFirst();

if(!chkCur1.isAfterLast() && chkCur1.getString(0).equals("Static")){
cal1.add(Calendar.DATE, 1);
cal1.add(Calendar.AM, 1); //Added 1am to date 30-06-2013
weekHours = cal1.getTime();


}

chkCur1 = dbAdapter.rawQuery("select movement from user where Date <= '"
    + input1.format(weekHours) + "' order by Date asc LIMIT 1", null);

chkCur1.moveToFirst();

if(!chkCur1.isAfterLast() && chkCur1.getString(0).equals("Static")){
cal1.add(Calendar.AM, 5); //Added 5am to date 30-06-2013
weekHours = cal1.getTime();

}

chkCur1 = dbAdapter.rawQuery("select movement from user where Date <= '"
    + input1.format(weekHours) + "' order by Date asc LIMIT 1", null);

chkCur1.moveToFirst();

if(!chkCur1.isAfterLast() && chkCur1.getString(0).equals("Static")){
System.out.println("Both dates are in between")
}

I used 4 sql queries:

1) Setting time 1am to date1 and check if its previous is Static.

2) Setting time 5am to date1 and check if its previous is Static.

3) Setting time 1am to date2 and check if its previous is Static.

4) Setting time 5am to date2 and check if its previous is Static.

If all conditions satisfies then both dates (date1 and date2) are in between i.e. Static.

But this is the repeat of sql queries and thus slowing down the process.

Upvotes: 0

Views: 142

Answers (1)

CL.
CL.

Reputation: 180162

You could put all the four checks into subqueries:

SELECT (SELECT movement FROM user WHERE Date <= ? ORDER BY Date) = 'Static'
   AND (SELECT movement FROM user WHERE Date <= ? ORDER BY Date) = 'Static'
   AND (SELECT movement FROM user WHERE Date <= ? ORDER BY Date) = 'Static'
   AND (SELECT movement FROM user WHERE Date <= ? ORDER BY Date) = 'Static';

This will give you a single result record with a single boolean column.

Upvotes: 1

Related Questions