java Android - SQLite - SELECT BETWEEN Date1 AND Date2

Android - SQLite - SELECT BETWEEN Date1 AND Date2

提问by Phil Hunter

Mac OS-X

Mac OS-X

Android

安卓

Eclipse with ADT

带有 ADT 的 Eclipse

SQLite

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 :

我是创建 Android 应用程序的新手，我对此进行了大量研究，但一无所获。我需要查询我的 SQLite 数据库以返回 2 个日期之间的所有行。到目前为止，我从研究中了解到，Androids SQLite 数据库中没有 DateTime 列，我必须将其保存为文本列。但我认为问题在于尝试比较字符串，但我无法找到解决方法。这是我的代码：

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:

我没有收到任何错误，但我没有从我的 RawQuery 返回任何结果。这是我的代码：

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.

我从其他 StackOverflow 答案中得到了这个声明，但我找不到关于 date() 方法的任何文档。我不确定它现在是否已经过时，但我收到错误消息“未定义类型（类名）的日期（字符串）方法”，并且我尝试导入一些 JAVA 库。

Does anyone know the correct way to create a rawData() query that will get the rows between selected dates??

有谁知道创建一个 rawData() 查询的正确方法，该查询将获取所选日期之间的行？

Further info, these are my INSERT statements and the date format that goes into the database:

更多信息，这些是我的 INSERT 语句和进入数据库的日期格式：

newDB.execSQL("INSERT INTO " + tableName + " (Date, Hours) Values ('" + ph_date + "'," + hours + ");");
String date1 = "12/1/13"
String date2 = "25/1/13"

回答by Phil Hunter

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:

好的，所以我无法让字符串日期工作，所以我必须先将字符串日期转换为日历日期到 Unix 时间，然后再将它们添加到 SQLite 数据库并在显示它们时将它们转换回（Unix 时间到日历日期到字符串）。Unix Time 允许对日期列进行计算（按顺序排序、升序排序、介于两者之间），这是经过长时间反复试验后使用的最佳方法。这是我最终使用的代码：

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());
                }
            }

回答by CL.

You must store date values in a format understood by SQLite. For plain dates, this would be a YYYY-MM-DDstring, or a seconds value, or a Julian date number.

您必须以 SQLite 理解的格式存储日期值。对于普通日期，这将是一个YYYY-MM-DD字符串、一个秒值或一个儒略日期数字。

To format a date as string, use something like this:

要将日期格式化为字符串，请使用以下内容：

Date date1 = ...;
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
String dateAsString = df.format(date1);

To format a date as seconds value, divide the return value of Date.getTime()by 1000.

要将日期格式化为秒值，请将返回值Date.getTime()除以 1000。

---

---

If your date variables already are strings, you must ensure that they have the correct format.

如果您的日期变量已经是字符串，您必须确保它们具有正确的格式。

If your date strings do not have the yyyy-MM-ddformat, SQLite's date functionswill not understand them, and comparisons will not work (because the string must begin with the most significant field, the year, and all fields must have a fixed length, so that string comparisons come out correct).

如果您的日期字符串没有yyyy-MM-dd格式，SQLite 的日期函数将无法理解它们，并且比较将不起作用（因为字符串必须以最重要的字段开头，年份，并且所有字段都必须具有固定长度，因此字符串比较结果正确）。

回答by Gautam

getting top two name of for 2 days

连续两天获得前两名

Calendar cal = Calendar.getInstance();
            Date now = new Date();
            cal.setTime(now);
            String currentDate = sdf.format(cal.getTimeInMillis());
            cal.add(Calendar.DAY_OF_YEAR, -1);
            String previusdDate = sdf.format(cal.getTimeInMillis());

            Log.e("Start date", currentDate);
            Log.e("End date", previusdDate);
            SQLiteOpenHelper helper;
            SQLiteDatabase db;
            helper = new My_SQliteHelper(mContext, "MyDB", null, 1);
            db = helper.getWritableDatabase();

            Cursor cr = db.rawQuery("SELECT name,sum(minutes),date FROM Historyinfo WHERE date BETWEEN '" + previusdDate + "' AND '" + currentDate + "' GROUP BY name ORDER BY SUM(minutes) DESC LIMIT 2", null);

100% working

100% 工作

回答by Varun Verma

Not sure where you read that Date / Time should be stored as strings !!! I dis-agree.

不确定你在哪里读到日期/时间应该存储为字符串！！！我不同意。

I prefer storing Date / Times as INTEGER values. Read: http://www.sqlite.org/datatype3.html#datetime

我更喜欢将日期/时间存储为整数值。阅读：http: //www.sqlite.org/datatype3.html#datetime

When Inserting data, convert your Date / time as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. For e.g. 1st Jan 2012 00:00:00 GMT is represented as 1356998400000

插入数据时，将您的日期/时间转换为 Unix 时间，即自 1970-01-01 00:00:00 UTC 以来的秒数。例如，2012 年 1 月 1 日 00:00:00 GMT 表示为 1356998400000

The Android Date / Calendar classes have built in functions to convert dates in UNIX Times and vice versa.

Android Date / Calendar 类具有内置函数，可以在 UNIX 时间中转换日期，反之亦然。

Check : http://developer.android.com/reference/java/util/Date.html#getTime%28%29

检查：http: //developer.android.com/reference/java/util/Date.html#getTime%28%29

When selecting, you can use your normal select statements like BETWEEN or anything you prefer.

选择时，您可以使用普通的选择语句，例如 BETWEEN 或您喜欢的任何内容。

I have been using this for many years and it works like a charm :D

我已经使用它很多年了，它的作用就像一个魅力：D