问题描述
在我的SQLite数据库的表中有一个时间戳（毫秒）列。该表确实是巨大的，我想查询所有theire timestamp列是指在特定的一天行的efficent方式（在这种情况下，我想获得属于今日enteries）。
我想出了这一点，但它不工作，它很慢。任何想法将appriciated。 （不，我不能在数据库中的任何变化，如增加一个DATETIME列...我有时间戳做到这一点）。
公共布尔isTimestampInToday（长时间戳）{ //从今天时间戳
长todayStamp = System.currentTimeMillis的（）; 日历C = Calendar.getInstance（）;
c.setTimeInMillis（todayStamp）;
c.setTimeZone（TimeZone.getDefault（））; c.set（Calendar.HOUR_OF_DAY，0）;
c.set（Calendar.MINUTE，0）;
c.set（Calendar.SECOND，0）;
c.set（Calendar.MILLISECOND，0）; 日期的startDate = c.getTime（）; //开始一天 c.set（Calendar.HOUR_OF_DAY，23）;
c.set（Calendar.MINUTE，59）;
c.set（Calendar.SECOND，59）;
c.set（Calendar.MILLISECOND，999）; 日期结束日期= c.getTime（）; //结束日 龙startStamp = startDate.getTime（）;
龙endStamp = endDate.getTime（）; 如果（时间标记＆GT; = startStamp和放大器;＆安培;时间戳＆LT; endStamp）{
返回true;
}其他{
返回false;
}
}
我appriciate全部来自expirienced家伙的答案，但说实话一个，我能理解来自@iaune答案。现在，我已经这样做了查询本身内：
日历GC =新的GregorianCalendar（）;
gc.setTimeZone（TimeZone.getDefault（））;
长toBeg = gc.getTimeInMillis（）;
toBeg - = toBeg％（24 * 60 * 60 * 1000）;
长toEnd = toBeg + 24 * 60 * 60 * 1000;
光标光标= mDb.rawQuery（
SELECT * FROM
+ DbHelper.TABLE_PA_DATA
+WHERE
+ DbHelper.COLUMN_TIMESTAMP
+BETWEEN
+ toBeg
+和
+ toEnd
， 空值）;
这有什么不对的做法还是可以进一步提高？
更新
我意识到，使用 INTEGER 时间戳列类型是错误的。我添加了一个 DATETIME 专栏......我现在应该使用SQLite的直接查询功能，我猜。任何想法？
下面是我所得到的，当我转储表：
ID DATETIME TIMESTAMP STEPS CALORIES
1 2014年7月6日7点18分55秒169629539 3 0
2 2014年7月6日7时19分10秒169644509 4 0
3 2014年7月6日八点15分36秒173030229 1 0
4 2014年7月6日8点16分04秒173058397 4 0
5 2014年7月6日8时31分39秒173993598 2 0
6 2014年7月6日8点33分二十秒174094714 5 0
7 2014年7月6日9点31分54秒177609142 1 0
8 2014年7月6日9时42分24秒178238517 1 0
9 2014年7月6日10时37分37秒181551849 1 0
10 2014年7月6日11时○○分31秒182925950 1 0
11 2014年7月6日12时17分42秒187557035 1 0
12 2014年7月6日14点14分39秒194573993 2 0
确定我设法与一些除了做才能解决@laune：
日历CAL = Calendar.getInstance（）;
//偏移量增加，由于我们不是UTC
长偏移量= cal.get（Calendar.ZONE_OFFSET）+
cal.get（Calendar.DST_OFFSET）;
长toBeg = cal.getTimeInMillis（）;
toBeg - =（toBeg +偏移量）％（24 * 60 * 60 * 1000）;
长toEnd =（toBeg）+ 24 * 60 * 60 * 1000;
使用这个查询：
光标光标= mDb.rawQuery（
SELECT * FROM
+ Database.TABLE_PA_DATA
+WHERE
+ Database.COLUMN_TIMESTAMP
+BETWEEN
+ toBeg
+和
+ toEnd，空
）;
简单的算术运算，可避免昂贵的字符串格式化操作。不知道是否大卫是什么意思，我建议，但也许code EX presses更清楚：
日历GC =新的GregorianCalendar（）;
长toBeg = gc.getTimeInMillis（）;
toBeg - = toBeg％（24 * 60 * 60 * 1000）;
长toEnd = toBeg + 24 * 60 * 60 * 1000;长今= gc.getTimeInMillis（）;
如果（toBeg＆LT; =＆现在功放;＆安培;现在＆LT; toEnd）{
的System.out.println（今天）;
}
的值toBeg和toEnd，计算前场，可用于运行查询，根据是否。 （要precise，注意使用＆LT; = 和＆LT; ）
In a table of my SQLite database there is a timestamp (millisec) column. This table is really huge and I want a efficent way of querying all the rows that theire timestamp column refers to a specific day (in this case I want to get all enteries that belong to TODAY).
I came up with this, but it does not work and its quite slow. Any ideas will be appriciated. (NO I can not make any change in the database such as adding a DATETIME column...I have to do it with the timestamp).
public boolean isTimestampInToday(long timestamp){
//get a timestamp from today
long todayStamp = System.currentTimeMillis();
Calendar c = Calendar.getInstance();
c.setTimeInMillis(todayStamp);
c.setTimeZone(TimeZone.getDefault());
c.set(Calendar.HOUR_OF_DAY, 0);
c.set(Calendar.MINUTE, 0);
c.set(Calendar.SECOND, 0);
c.set(Calendar.MILLISECOND, 0);
Date startDate = c.getTime(); //START OF DAY
c.set(Calendar.HOUR_OF_DAY, 23);
c.set(Calendar.MINUTE, 59);
c.set(Calendar.SECOND, 59);
c.set(Calendar.MILLISECOND, 999);
Date endDate = c.getTime(); //END OF DAY
Long startStamp = startDate.getTime();
Long endStamp = endDate.getTime();
if(timestamp >= startStamp && timestamp < endStamp) {
return true;
} else {
return false;
}
}
I appriciate all the answers from expirienced guys, but to be honest the one that I could understand was the answer from @iaune. Now I have done this inside the query itself:
Calendar gc = new GregorianCalendar();
gc.setTimeZone(TimeZone.getDefault());
long toBeg = gc.getTimeInMillis();
toBeg -= toBeg % (24*60*60*1000);
long toEnd = toBeg + 24*60*60*1000;
Cursor cursor = mDb.rawQuery(
"SELECT * FROM "
+ DbHelper.TABLE_PA_DATA
+ " WHERE "
+ DbHelper.COLUMN_TIMESTAMP
+ " BETWEEN "
+ toBeg
+ " AND "
+ toEnd
, null);
Is there anything wrong with this approach or can it be improved more?
UPDATE
I realised that using INTEGER for timestamp column type is wrong. I added a DATETIME column...I should now use direct SQLite query functions I guess. any ideas?
Here is what I get when I dump the table:
ID DATETIME TIMESTAMP STEPS CALORIES
1 2014-07-06 07:18:55 169629539 3 0
2 2014-07-06 07:19:10 169644509 4 0
3 2014-07-06 08:15:36 173030229 1 0
4 2014-07-06 08:16:04 173058397 4 0
5 2014-07-06 08:31:39 173993598 2 0
6 2014-07-06 08:33:20 174094714 5 0
7 2014-07-06 09:31:54 177609142 1 0
8 2014-07-06 09:42:24 178238517 1 0
9 2014-07-06 10:37:37 181551849 1 0
10 2014-07-06 11:00:31 182925950 1 0
11 2014-07-06 12:17:42 187557035 1 0
12 2014-07-06 14:14:39 194573993 2 0
OK I managed to do it with some addition to @laune solution:
Calendar cal = Calendar.getInstance();
// offset to add since we're not UTC
long offset = cal.get(Calendar.ZONE_OFFSET) +
cal.get(Calendar.DST_OFFSET);
long toBeg = cal.getTimeInMillis();
toBeg -= (toBeg + offset) % (24*60*60*1000);
long toEnd = (toBeg) + 24*60*60*1000;
Using this query:
Cursor cursor = mDb.rawQuery(
"SELECT * FROM "
+ Database.TABLE_PA_DATA
+ " WHERE "
+ Database.COLUMN_TIMESTAMP
+ " BETWEEN "
+ toBeg
+ " AND "
+ toEnd, null
);
Simple arithmetic operations can avoid the costlier string and formatting operations. Not sure whether David meant what I propose, but maybe code expresses it more clearly:
Calendar gc = new GregorianCalendar();
long toBeg = gc.getTimeInMillis();
toBeg -= toBeg % (24*60*60*1000);
long toEnd = toBeg + 24*60*60*1000;
long now = gc.getTimeInMillis();
if( toBeg <= now && now < toEnd ){
System.out.println( "today" );
}
The values toBeg and toEnd, computed up front, can be used to run the query, according to the if. (To be precise, note the use of <= and <.)
这篇关于检查时间戳（毫秒）属于特定的一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！