Database Table (event): --------------------------------------- - Row_Id - Start

Question

Database Table (event):
---------------------------------------
- Row_Id - Starttime - Endtime -
---------------------------------------
- 1 - 2045 - 2215 -
---------------------------------------
- 2 - 2315 - 115 -
---------------------------------------

the time is using integer and no i can not use the datetime variable

The number comming into the method is 0 - 23 which is just the hour.

Example: (start = 2045) and (end = 2215)
need to get a count for each hour........

// returns a count for each hour including intervals
public int getNumberOfEvents(int time)
{
time = hour; // this is (0-23)
db = DBHelper.getReadableDatabase();

Cursor count = db.rawQuery("THE QUERY", null);
count.moveToFirst();
int eventcount = count.getInt(0);
count.close();
return eventcount;
}


select count() from events where event_starttime between (2000 and 2059) = 1
then check between (2100 and 2159) = 1 then check between (2200 and 2259) = 1
if it checked between (2300 - 2359) it would be 0
so the total count = 3
8 pm count is 1 - 9pm count is 1 - 10 pm count is 1
so for this event it would have a count on each one of these hours.


Note: for version 2.3.3 gingerbread

Explanation / Answer

I'm not sure you can do what you want to do the way you want to do it. The example range given means that 2 SELECT statements will be needed. One to get number of "events" in the first range (2000 - 2059) and another to get the number of "events" in the second range (2100 - 2159). However, if the range is larger than two hours, this statement will need to be updated dynamically to accommodate for the increased number of events. The way I see it you have two options. The first is to do multiple queries (SELECT count()) queries for each hour. Put the db.rawQuery(countQuery, null) in a while loop and add to "eventcount" each time through the while loop while the starting time is not equal to the beginning of the hour for the last interval needed. Example: int eventcount = 0; while(starttime

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---