Android的 - SQLite的 - 选择日期1和date2之间 [英] Android - SQLite - SELECT BETWEEN Date1 AND Date2
问题描述
的Mac OS-X
安卓
使用adt
SQLite的
我是新来的创建Android应用和香港专业教育学院研究这种严重，但我一事无成。我需要查询我的SQLite数据库返回两个日期之间的所有行。我从我的调研中了解到，到目前为止是，有在机器人SQLite数据库没有日期时间列，我必须把它保存为文本列。但我认为问题在于尝试比较字符串，但我不能想出办法解决它。这是我的code：
DB = currentContext.openOrCreateDatabase（数据库，0，NULL）;
DB.execSQL（CREATE TABLE IF NOT EXISTS+ tableName值+（ID INTEGER PRIMARY KEY AUTOINCREMENT，日期VARCHAR（40），时间INT（3））;）;
我没有收到任何错误，但我没有回从我RawQuery任何结果。这是我的code：
光标C = newDB.rawQuery（选择编号，日期，从时间+ tableName值+里之间date'+日期1 +00:00:00和 + DATE2 +99:99:99，NULL）;
如果（C！= NULL）{
Log.d（TAG，DATE1：+日期1）;
Log.d（TAG，DATE2：+日期2）;
如果（c.moveToFirst（））{
做 {
INT的id = c.getInt（c.getColumnIndex（ID））;
字符串DATE1 = c.getString（c.getColumnIndex（日期））;
INT小时1 = c.getInt（c.getColumnIndex（时时刻刻））;
results.add（+编号+日期：+日期1 +时间：+小时1）;
}
而（c.moveToNext（））;
}
}
我也曾尝试下面的语句，但他们没有取得成果之一：
光标C = newDB.rawQuery（选择编号，日期，从时间+ tableName值+，其中日之间的+日期（日期1）+和+日期（DATE2） +，NULL）;
我是从其他StackOverflow的答案，这种说法，但我找不到上的日期（）方法中的任何文件。林不知道它是否已经过时了，但现在我得到的错误说：日期（String）方法是不确定的类型（类名），我曾尝试引进一些Java库。
有谁知道创建RAWDATA（）查询，将获得选择日期??
之间的行中的正确方法另外信息，这些都是我的INSERT语句和日期格式，进入数据库：
newDB.execSQL（插入+ tableName值+（日期，时间）VALUES（'+ ph_date +'，+小时+）;）;
字符串DATE1 =13年12月1日
字符串DATE2 =25/1/13
好了，我不能让字符串日期的工作，所以我不得不将字符串转换日期，以日历日期为Unix时间将其添加到SQLite数据库前，它们转换回（UNIX时间以日历日期为String）显示他们的时候。 UNIX时间允许计算（ORDER BY，排序上升等之间）上的日期列完成，这是长时间的反复试验后使用的最佳方法。这里是code我最终使用：
光标C = newDB.rawQuery（选择编号，日期，从时间+ tableName值+里之间date'+ startDateQueryDate +'和'+ endDateQueryDate +' ORDER BY日期ASC，NULL）;
如果（C！= NULL）{
如果（c.moveToFirst（））{
做 {
INT tempId = c.getInt（c.getColumnIndex（ID））;
长tempUnixTime = c.getLong（c.getColumnIndex（日期））;
//转换tempUnixTime到日期
java.util.Date startDateDate =新java.util.Date（tempUnixTime）;
//创建SimpleDateFormat的格式
SimpleDateFormat的formatter1;
formatter1 =新的SimpleDateFormat（DD / MM / YYYY，Locale.UK）;
//转换日期为的SimpleDateFormat并转换为字符串
字符串tempStringStartDate = formatter1.format（startDateDate）;
INT tempHours = c.getInt（c.getColumnIndex（时时刻刻））;
results.add（+ tempId +日期：+ tempStringStartDate +时间：+ tempHours）;
}而（c.moveToNext（））;
}
}
Mac OS-X
Android
Eclipse with ADT
SQLite
I'm new to creating Android Apps and Ive researched this heavily but I'm getting nowhere. I need to query my SQLite database to return all the rows between 2 dates. What I have learnt from my research so far is that there is no DateTime column in Androids SQLite database and I have to save it as a text column. But I think the problem lies with trying to compare Strings but I can't figure out a way around it. Here is my code :
DB = currentContext.openOrCreateDatabase(DBName, 0, null);
DB.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, Date VARCHAR(40), Hours INT(3));");
I am not getting any errors but I am not returning any results from my RawQuery. Here is my code:
Cursor c = newDB.rawQuery("select ID, Date, Hours from " + tableName + " where Date BETWEEN '" + date1 + " 00:00:00' AND '" + date2 + " 99:99:99'", null);
if (c != null ) {
Log.d(TAG, "date1: "+date1);
Log.d(TAG, "date2: "+date2);
if (c.moveToFirst()) {
do {
int id = c.getInt(c.getColumnIndex("ID"));
String date1 = c.getString(c.getColumnIndex("Date"));
int hours1 = c.getInt(c.getColumnIndex("Hours"));
results.add(+ id + " Date: " + date1 + " Hours: " + hours1);
}
while (c.moveToNext());
}
}
I have also tried the following statement but they do not yield results either:
Cursor c = newDB.rawQuery("select ID, Date, Hours from " + tableName + " where Date BETWEEN " + Date(date1) + " AND " + Date(date2) + "", null);
I got this statement from other StackOverflow answers but I cannot find any documentation on the date() method. Im not sure whether it is obsolete now but I get error saying "Date(String) method is undefined for the type (classname)" and I have tried importing some JAVA Libraries.
Does anyone know the correct way to create a rawData() query that will get the rows between selected dates??
Further info, these are my INSERT statements and the date format that goes into the database:
newDB.execSQL("INSERT INTO " + tableName + " (Date, Hours) Values ('" + ph_date + "'," + hours + ");");
String date1 = "12/1/13"
String date2 = "25/1/13"
Ok, so I could not get string dates to work, so I had to convert String Dates to Calendar Dates to Unix Time before adding them to the SQLite database and convert them back (Unix Time to Calendar Dates to String) when displaying them. Unix Time allows calculations (order by, sort ascending, between etc) done on the date columns and it is the best method to use after long hours of trial and error. Here is the code I ended up using:
Cursor c = newDB.rawQuery("select ID, Date, Hours from " + tableName + " where Date BETWEEN '" + startDateQueryDate + "' AND '" + endDateQueryDate + "' ORDER BY Date ASC", null);
if (c != null ) {
if (c.moveToFirst()) {
do {
int tempId = c.getInt(c.getColumnIndex("ID"));
long tempUnixTime = c.getLong(c.getColumnIndex("Date"));
//convert tempUnixTime to Date
java.util.Date startDateDate = new java.util.Date(tempUnixTime);
//create SimpleDateFormat formatter
SimpleDateFormat formatter1;
formatter1 = new SimpleDateFormat("dd/MM/yyyy", Locale.UK);
//convert Date to SimpleDateFormat and convert to String
String tempStringStartDate = formatter1.format(startDateDate);
int tempHours = c.getInt(c.getColumnIndex("Hours"));
results.add(+ tempId + " Date: " + tempStringStartDate + " Hours: " + tempHours);
}while (c.moveToNext());
}
}
这篇关于Android的 - SQLite的 - 选择日期1和date2之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
