Sqlite query using dates as text in android?

Question

I have an app tha has in a sqlite database some measures.

From an activity(Email Activity) the user selects which data will be sent to doctor's email(the data of last 1/7/30 days).

enter image description here

This is the code of this activity:

import com.oikonomopo.blood.pressure.tracker.dao.BpDAO;

...

public class EmailActivity extends Activity implements OnItemSelectedListener {

    Spinner spinner;
    String[] measures = { "today", "7 days", "30 days" };

    private Calendar theStart;
    private Calendar theEnd;
    SimpleDateFormat dateFormat;
    String start;
    String end;

    //this refers to another class for the manipulate of my table
    private BpDAO dao = null;

    private Button sendEmailBtn;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.email);

        // ////////////////////////////////////
        theEnd = Calendar.getInstance();
        theStart = (Calendar) theEnd.clone();
        dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        // //////////////////////////////////

        ArrayAdapter<String> adapter = new ArrayAdapter<String>(
                EmailActivity.this, android.R.layout.simple_spinner_item,
                measures);

        sendEmailBtn = (Button) findViewById(R.id.button1);
        spinner = (Spinner) findViewById(R.id.spinner1);
        spinner.setAdapter(adapter);
        spinner.setOnItemSelectedListener(this);

        // BpDao - refers to my table - see later
        dao = new BpDAO(this);

        // add a click listener to the button
        sendEmailBtn.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {
/////////////////////////////////////////////////////////////////////////////////////////
                Log.d("BPT", "before"); // it works untill here
                Cursor bpLastDaysList = dao.lastXdays_bp(start, end);
                Log.d("BPT", "after"); //doesn't reach the code here!!
/////////////////////////////////////////////////////////////////////////////////////////
            }
        });
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        dao.close();
    }

    public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2,
            long arg3) {
        int position = spinner.getSelectedItemPosition();
        switch (position) {

                // last day
        case 0:
            theStart.add(Calendar.DAY_OF_MONTH, -1);

            // date boundaries in TEXT format
            start = dateFormat.format(theStart.getTime());
            end = dateFormat.format(theEnd.getTime());
            //
            break;

        // last 7 days
        case 1:
            theStart.add(Calendar.DAY_OF_MONTH, -7);

            // date boundaries in TEXT format
            start = dateFormat.format(theStart.getTime());
            end = dateFormat.format(theEnd.getTime());
            //
            break;

        // last 30 days
        case 2:
            theStart.add(Calendar.DAY_OF_MONTH, -30);

            // date boundaries in TEXT format
            start = dateFormat.format(theStart.getTime());
            end = dateFormat.format(theEnd.getTime());
            //
            break;
        }
    }

}

But it gives me this logcat error:

    FATAL EXCEPTION: main
android.database.sqlite.SQLiteException: near "09": syntax error: , while compiling: SELECT DISTINCT systolic, diastolic, datetime, _id, notes, pulses FROM bp_import WHERE datetime BETWEEN 2012-10-12 09:44 AND 2012-10-12 09:44 ORDER BY datetime DESC
at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
...
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1405)
at com.oikonomopo.blood.pressure.tracker.dao.BpDAO.lastXdays_bp(BpDAO.java:76)
at com.oikonomopo.blood.pressure.tracker.EmailActivity$1.onClick(EmailActivity.java:70)

android.database.sqlite.SQLiteException: near "09": syntax error: , while compiling: SELECT DISTINCT systolic, diastolic, datetime, _id, notes, pulses FROM bp_import WHERE datetime BETWEEN 2012-10-12 09:44 AND 2012-10-12 09:44 ORDER BY datetime DESC

The code for the function of the BpDAO class is :

    public class BpDAO {
        private DatabaseHelper_bp dbHelper;
        private SQLiteDatabase database;
        /**
         * Movie table related constants.
         */
        public final static String bp_TABLE = "bp_import";
        public final static String bp_ID = "_id";
        public final static String bp_DT = "datetime";
        public final static String bp_SYS = "systolic";
        public final static String bp_DIA = "diastolic";
        public final static String bp_PUL = "pulses";
        public final static String bp_NOT = "notes";

        public BpDAO(Context context) {
            dbHelper = new DatabaseHelper_bp(context);
            database = dbHelper.getWritableDatabase();
        }

        /**
         * \ Creates a new blood pressure measure
         * 
         * @param datetime
         * @param systolic
         * @param diastolic
         * @param pulses
         * @param notes
         * @return
         */
        public long importBP(String datetime, String systolic, String diastolic,
                String pulses, String notes) {
            ContentValues values = new ContentValues();
            values.put(bp_DT, datetime);
            values.put(bp_SYS, systolic);
            values.put(bp_DIA, diastolic);
            values.put(bp_PUL, pulses);
            values.put(bp_NOT, notes);
            return database.insert(bp_TABLE, null, values);
        }

    // this is working perfect
        /**
         * Fetch all bps sorted DESCending
         * @return
         */
        public Cursor fetchAll_bp() {
            Cursor mCursor = database.query(true, bp_TABLE, new String[] { bp_SYS,
                    bp_DIA, bp_DT, bp_ID, bp_NOT, bp_PUL }, null, null, null, null,
                    bp_DT + " DESC", null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        }
    // here is the problem!
        /**
         * Fetch all bps sorted DESCending by date(yyyy-mm-dd hh:mm) of last
         * X[1-7-30] days
         * @return
         */
        public Cursor lastXdays_bp(String start, String end) {
            Cursor mCursor = database.query(true, bp_TABLE, new String[] { bp_SYS,
                    bp_DIA, bp_DT, bp_ID, bp_NOT, bp_PUL }, "datetime BETWEEN "
                    + start + " AND " + end, null, null, null, bp_DT + " DESC",
                    null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        }
//
        public void close() {
            database.close();
        }
    }

And the code of the DatabaseHelper_bp(extends SQLiteOpenHelper) is :

    public class DatabaseHelper_bp extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "bpDB";
    private static final int DATABASE_VERSION = 1;

    // Database creation sql statement
    private static final String DATABASE_CREATE = "create table bp_import ( _id integer primary key, datetime text not null, systolic text not null, diastolic text not null, pulses text not null, notes text not null);";

    public DatabaseHelper_bp(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Method is called during creation of the database
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    // Method is called during an upgrade of the database,
    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion,
            int newVersion) {
        Log.w(DatabaseHelper_bp.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        database.execSQL("DROP TABLE IF EXISTS bp_import");
        onCreate(database);
    }
}

I save the datetime as text in sqlite. (2012-10-12 09:43)

The BETWEEN params should be in quotes. I advice you to use Prepared Statments. — st0le
– st0le, Commented Oct 12, 2012 at 10:37

Sardor Dushamov · Accepted Answer · 2012-10-12 10:39:40Z

2

Cursor mCursor = database.query(true, bp_TABLE, new String[] { bp_SYS,
                bp_DIA, bp_DT, bp_ID, bp_NOT, bp_PUL }, "datetime BETWEEN ?
              AND ?" , new String[]{start,end}, null, null, bp_DT + " DESC",
                null);

answered Oct 12, 2012 at 10:39

Sardor Dushamov

1,6674 gold badges17 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Germann Arlington · Accepted Answer · 2012-10-12 10:41:55Z

1

The date strings must be surrounded by quotes in your SQL - the error is in your BpDAO/lastXdays_bp(String start, String end) method.

And Sardor Dushamov's method is better still

edited Oct 12, 2012 at 10:41

answered Oct 12, 2012 at 10:36

Germann Arlington

3,3532 gold badges19 silver badges21 bronze badges

Collectives™ on Stack Overflow

Sqlite query using dates as text in android?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related