对于Android，我如何从SQLite中的日期和日期范围中获取数据，在SQLite中，Android变量可以传递给查询？

Question

SimpleDateFormat dateFormat=新的SimpleDateFormat（“yyyy-mm-dd hh：mm：ss”）；

Answer 1

您可以在数据库帮助器中使用如下所示的方法：-

public Cursor getDatesInRange(String fromDate, String toDate) {
    String whereclause = COL_DATE + " >= datetime(?) and " + COL_DATE + " <= datetime(?)";
    String[] whereargs = new String[]{fromDate,toDate};
    return this.getWritableDatabase().query(
            TB_DATE,
            null,
            whereclause,
            whereargs,
            null,
            null,
            COL_DATE + " DESC"
    );
}

• COL_DATE保存相应列的名称。
• TB_DATE保存相应表的名称。
• fromdate和toDate需要格式为yyyy-MM-dd、hh：mm：ss或子格式，注意hh：mm：ss（如果没有给定）等于00:00:00。
  • 例如yyyy-MM-dd在某些情况下就足够了。

  Databasehelper：-

  public static final String DBNAME = "SO50282282";
  public static final int DBVERSION = 1;
  public static final String TB_DATE = "tbl_date";
  public static final String COL_DATE = "date_column";
  
  public SO50282282DBHelper(Context context) {
      super(context, DBNAME, null, DBVERSION);
      this.getWritableDatabase();
  }
  
  @Override
  public void onCreate(SQLiteDatabase db) {
      String crtsql = "CREATE TABLE IF NOT EXISTS " +
              TB_DATE +
              "(" +
              COL_DATE + " TEXT " +
              ")";
      db.execSQL(crtsql);
  
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  
  }
  
  public long addDate(String date) {
      ContentValues cv = new ContentValues();
      cv.put(COL_DATE,date);
      return this.getWritableDatabase().insert(TB_DATE,null,cv);
  }
  
  public Cursor getDatesInRange(String fromdate, String toDate) {
      String[] columns = new String[]{"*,rowid AS id"};
      String whereclause = COL_DATE + " >= datetime(?) and " + COL_DATE + " <= datetime(?)";
      String[] whereargs = new String[]{fromdate,toDate};
      return this.getWritableDatabase().query(
              TB_DATE,
              columns,
              whereclause,
              whereargs,
              null,
              null,
              COL_DATE + " DESC"
      );
  }
  

  null

  在一项活动中：-

      SO50282282DBHelper dbhlpr = new SO50282282DBHelper(this);
      // Only insert some testing rows if there are none
      if (
              DatabaseUtils.queryNumEntries(
              dbhlpr.getWritableDatabase(),
              SO50282282DBHelper.TB_DATE
              ) < 1) {
          dbhlpr.addDate("2018-01-01 08:30:56");
          dbhlpr.addDate("2018-02-01 09:31:56");
          dbhlpr.addDate("2018-03-01 10:32:56");
          dbhlpr.addDate("2018-04-01 11:35:56");
          dbhlpr.addDate("2018-05-01 12:36:56");
          dbhlpr.addDate("2018-02-02 12:37:56");
          dbhlpr.addDate("2018-03-02 14:38:56");
      }
      // gets rows id=3 id=4 id=7
      //      (id 1, 2 and 6 before 2018-03-01)(id 5 after 2018-04-30)
      Cursor csr = dbhlpr.getDatesInRange("2018-03-01","2018-04-30");
      SO50282282LogDates(csr);
      // gets rows id = 1
      csr = dbhlpr.getDatesInRange("2018-01-01 00:00:00","2018-01-01 23:59:59");
      SO50282282LogDates(csr);
      //gets rows id = 2, 3, 4, 6 and 7
      csr = dbhlpr.getDatesInRange("2018-01-01 08:30:57","2018-05-01 12:36:55");
      SO50282282LogDates(csr);
      csr.close();
  

  日志中的结果：-

  对于其中的第一个（使用dbhlpr.getdatesinrange（“2018-03-01”,“2018-04-30”）):-

  05-11 03:56:44.777 2203-2203/? D/EXTRACTED_DATE: Date is 2018-04-01 11:35:56 from row #  4
      Date is 2018-03-02 14:38:56 from row #  7
      Date is 2018-03-01 10:32:56 from row #  3
  

      Date is 2018-01-01 08:30:56 from row #  1
  

  05-11 03:56:44.781 2203-2203/? D/EXTRACTED_DATE: Date is 2018-04-01 11:35:56 from row #  4
      Date is 2018-03-02 14:38:56 from row #  7
      Date is 2018-03-01 10:32:56 from row #  3
      Date is 2018-02-02 12:37:56 from row #  6
      Date is 2018-02-01 09:31:56 from row #  2