从SQLite获取根据过去小时的数据 [英] Getting data according to last hours from sqlite
问题描述
我想按小时从sqlite数据库中获取记录.以下是我的问题
I want to get records out of sqlite database according to hours. following are my questions
1)我必须在过去一小时内从sqlite提取所有数据.我尝试了以下查询,但它为我提供了当天所有时段的数据
1) I have to extract all data from sqlite for past one hour. I have tried following query but it provide me data for all the hours in present day
查询:
SELECT * FROM Table1 where Date >= datetime('now','-1 hours')
其中Table1是我的表名,而Date是DATETIME类型的列名
Where Table1 is my table name and Date is coloumn name of type DATETIME
例如:数据库中有以下记录
Eg: there are following record in database
当我在sqlite firefox浏览器工具中触发查询时,它会返回我
When I fire query in sqlite firefox browser tool it returns me
我不想要的.
从数据库中获取过去1小时数据的查询应该是什么
What should be the query to get past 1 hour data from database
2)应该查询什么才能根据每小时从数据库中获取值,比如我必须获取过去1个小时的数据,然后获取过去1-2个小时的数据,过去2-3个小时的数据,即两个小时之间有一个小时的数据?
2) What should be query to get the value out of database according to every hours, like I have to get data for past 1 hour, then data of past 1-2 hour, the data of past 2-3 hour i.e an hour data between two hours?
任何帮助将不胜感激.
推荐答案
最后,我找到了解决自己问题的方法
Finally I found the solution to my own question
以下是对我有用的代码
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = new Date();
Calendar calendar = Calendar.getInstance();
String startDate = dateFormat.format(date);
String endDate = "";
for (int i = 1; i <= 24; i++) {
System.out.println("Start Date:- " + startDate);
calendar.add(Calendar.HOUR_OF_DAY, -1);
date = calendar.getTime();
endDate = dateFormat.format(date);
System.out.println("End Date:- " + endDate);
String data = dbAdapter.getOutDoorHourlyData(startDate, endDate);
System.out.println("Hourly Average:- " + data);
startDate = endDate;
endDate = "";
}
public String getOutDoorHourlyData(String startDate, String endDate) {
double outdoorHourly = 0;
Cursor cursor = _sqliteDB.rawQuery("Select AVG("
+ COLOUMN_NAME + ") from (Select * FROM "
+ TABLE_NAME + " where " + COLOUMN_NAME + " >= '"
+ endDate + "' and " + COLOUMN_NAME + " < '" + startDate
+ "')", null);
try {
if (cursor != null) {
if (cursor.getCount() > 0) {
cursor.moveToFirst();
do {
outdoorHourly = cursor.getDouble(0);
} while (cursor.moveToNext());
}
cursor.close();
}
} catch (Exception e) {
e.printStackTrace();
}
String hourlyData = decimalFormat.format(outdoorHourly);
hourlyData = hourlyData.replace(",", ".");
return hourlyData;
}
}
我希望它将来能对某人有所帮助
I hope it will help someone in future
这篇关于从SQLite获取根据过去小时的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!