时间戳

首页 > 解决方案 > SQLite 查询相关范围内的日期

android - SQLite 查询相关范围内的日期

问题描述

我正在使用 android studio 和 SQLite。我在 SQLite 中设置了我的处方表，并为我的开始日期和结束日期使用了 TEXT 类型。在查询此数据时，我不太确定 TEXT 是否是合适的数据类型。我想显示开始日期早于或等于当前日期且结束日期大于或等于当前日期的记录。下面的代码来自我的 DatabaseHelper.java 类；

 //Create prescription table
    public static final String TABLE_PRESCRIPTION = "prescription_table";
    public static final String COL_PRESCRIPTION_ID = "PRESCRIPTIONID";
    public static final String COL_PRESCRIPTION__PATIENTID = COL_PATIENT_PATIENTID;
    public static final String COL_PRESCRIPTION__DATE = "DATE";
    public static final String COL_PRESCRIPTION__DRUGNAME = "DRUGNAME";
    public static final String COL_PRESCRIPTION__CONCENTRATION = "CONCENTRATION";
    public static final String COL_PRESCRIPTION__DOSAGE = "DOSAGE";
    public static final String COL_PRESCRIPTION__PREPARATION = "PREPARATION";
    public static final String COL_PRESCRIPTION__STARTDATE = "STARTDATE";
    public static final String COL_PRESCRIPTION__ENDDATE = "ENDDATE";
    public static final String COL_PRESCRIPTION__DOCTORID = "DOCTORID";

public void onCreate (SQLiteDatabase db) {
 String prescriptiontable = "CREATE TABLE " + TABLE_PRESCRIPTION + "(PRESCRIPTIONID INTEGER PRIMARY KEY AUTOINCREMENT, PATIENTID INTEGER, DATE TEXT, DRUGNAME TEXT, CONCENTRATION TEXT, DOSAGE TEXT, PREPARATION TEXT, STARTDATE TEXT, ENDDATE TEXT, DOCTORID INTEGER)";
        //execute SQL statements
        db.execSQL(prescriptiontable);
}

 public Cursor getPrescriptionsPerPatient(String patientid){
        SQLiteDatabase db = this.getReadableDatabase();
        //SQL select statement
        Cursor data = db.rawQuery("SELECT * FROM prescription_table WHERE PATIENTID =? AND strftime('%d/%m/%Y',date('now') BETWEEN " +
                        "STARTDATE AND ENDDATE)",
                new String[]{patientid});
        return data;
    }

错误在 getPrescriptionsPerPatient() 方法中。任何帮助，将不胜感激！谢谢

标签： androidsqlitedatesqldatatypes

解决方案

假设您以 SQLite 支持的格式之一存储日期（通常是：） YYYY-MM-DD，您可以这样做：

SELECT * 
FROM prescription_table 
WHERE 
    patientid = ? 
    AND date('now') BETWEEN startdate AND enddate

推荐阅读