Saving data to SQLite db automatically once per day

Question

I have a simple SQLite database with two columns.

Col 1 SimpleDateFormat("yyyyDDD") For example 2017001 (for jan 1st, 2017)

Col 2 int of occurrences

So, in the rare situation that no occurrences happen for an entire day, nothing ever get inserted into my database for that day. I would like to populate a zero in this case, otherwise that day gets bypassed when charting my data.

I've done some research and a solution I'm considering is to set up an RTC alarm and just populate a zero occurrence row every 24 hours. In the likely event occurrences happen, the zero will not affect my data when it is summed and sent to a chart. If there is no occurrence, I will have the zero there.

Does a more elegant solution exit? I have no experience with android alarms. Any downsides to RTC alarms? Perhaps SQLite has some functionality I could exploit? I'm open to any solution.

EDIT

I failed to mention that the zeros must be added even for days that the app is never turned on. This is an important distinction, and one I failed to mention previously. The rows can be retroactively added upon the app starting up (after a few days of being off perhaps). Thanks for the responses so far.

I find that [GCMTaskService](https://developers.google.com/android/reference/com/google/android/gms/gcm/GcmTaskService) is pretty clean. But it is (generally speaking) for network calls. You may want to check out the [JobScheduler](https://developer.android.com/reference/android/app/job/JobScheduler.html) — sam_c, Oct 03 '17 at 17:54
https://stackoverflow.com/questions/13514907/update-only-once-per-day — IntelliJ Amiya, Oct 03 '17 at 17:57
You didn't mention what kind of data you are charting, but in general adding 'Empty' data sets to a database is not a very elegant not robust solution. In order to give a more detailed suggestion it would be helpful to know what data you are charting and what kind of chart you are creating. — Barns, Oct 03 '17 at 18:49
@Barns. It's a simple line chart of the number of times the user exceeds a certain speed. If the user never exceeds the speed, no data is entered, even though the real answer is zero. — seekingStillness, Oct 03 '17 at 18:52
So you have the day and the speed for an 'Event'. So just generate a X-Y diagram using the data from you database with Speed as your 'Y' and day as your 'X'. no need for 'Empty' rows in you database. — Barns, Oct 03 '17 at 18:55

MikeT · Answer 1 · 2017-10-04T01:52:32.613

Here's a way to do it without relying on a background task or adding extra (useless/wasteful) data to the database.

Basically the data is extracted and any missing days, along with actual days, are added to a MatrixCursor (cursor that can be dynamically built).

The main method is getExpandedRows which has two supporting methods :-

setDate (sets a java Date object according to a string in yyyyDDD format enclosing the try catch)
and addMatrixRow to add a row to the MatrixCursor and to return the next date as a java Date.

getExpandedRows takes two parameters start and end dates as strings in the format yyyyDDDD, these being use to a) select the appropriate rows from the database and b) to drive the logic for adding missing rows (days).

All of these are within SQLiteOpenHelper subclass DBHelper :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mystats";
    public static final String TBNAME = "mystats";
    public static final String COL1 = "date";
    public static final String COL2 = "value";

    SQLiteDatabase mDB;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyDDD");
    long oneday = 1000 * 60 * 60 * 24;

    DBHelper(Context context) {
        super(context, DBNAME, null, 1);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crtsql = "CREATE TABLE IF NOT EXISTS " + TBNAME + "(" +
                COL1 + " TEXT UNIQUE NOT NULL, " +
                COL2 + " INTEGER " +
                ")";
        db.execSQL(crtsql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    public void addData(String date, int value) {
        ContentValues cv = new ContentValues();
        cv.put(COL1,date);
        cv.put(COL2,value);
         mDB.insert(TBNAME,null,cv);
    }

    public Cursor getAllRows(String filter) {
        return mDB.query(TBNAME,null,filter,null,null,null,null);
    }

    public Cursor getExpandedRows(String startdate, String enddate) {

        Date lastdate = setDate(startdate), finishdate = setDate(enddate), currentdate = new Date(0);
        int daysinperiod = (int)((finishdate.getTime() - lastdate.getTime()) / oneday);

        // get the base data
        String filter = COL1 + " BETWEEN " + startdate + " AND " + enddate + " ";
        Cursor base = getAllRows(filter);
        Log.d("GetExpRows","Rows Extracted from base data = " + Integer.toString(base.getCount()));

        MatrixCursor mc = new MatrixCursor(new String[]{COL1,COL2},daysinperiod);
        while (base.moveToNext()) {
            currentdate = setDate(base.getString(base.getColumnIndex(COL1)));

            while (lastdate.getTime() < currentdate.getTime()) {
                lastdate = addMatrixRow(mc, lastdate, 0L);
            }
            lastdate = addMatrixRow(mc, currentdate, base.getLong(base.getColumnIndex(COL2)));

        }
        while (currentdate.getTime() <= finishdate.getTime()) {
            currentdate = addMatrixRow(mc,currentdate, 0L);
        }
        base.close();
        return mc;
    }

    private Date addMatrixRow(MatrixCursor mc, Date date, Long value) {
        mc.addRow(new Object[]{sdf.format(date.getTime()),value});
        return new Date(date.getTime() + oneday);
    }

    private Date setDate(String date) {
        Date rv = new Date(0L);
        try {
            rv = sdf.parse(date);
        } catch (Exception e) {
        }
        return rv;
    } }

To test 4 rows were added for days:-

20017001 (value 100),
2017043 (value 50),
2017150 (value 17) and
2017364 (value 33)

A period of 2016364-2018004 was chosen fro testing.

This is the invoking activity used for testing, (note you can use a Matrix Cursor as a normal Cursor). The main line being Cursor mydata = dbhlpr.getExpandedRows("2016364","2018005");

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        DBHelper dbhlpr = new DBHelper(this);
        Cursor csr = dbhlpr.getAllRows("");
        if (csr.getCount() == 0) {
            dbhlpr.addData("2017001",100);
            dbhlpr.addData("2017035",50);
            dbhlpr.addData("2017150",17);
            dbhlpr.addData("2017364",33);
        }
        csr.close();

        Cursor mydata = dbhlpr.getExpandedRows("2016364","2018005");
        int limit = 0;
        while (mydata.moveToNext()) {
            if(limit++ > 400) {
                break;
            }
            Log.d("MYDATA","Row " + Integer.toString(mydata.getPosition()) +
                    " for Date " + mydata.getString(mydata.getColumnIndex(DBHelper.COL1)) +
                    " has Value " + Integer.toString(mydata.getInt(mydata.getColumnIndex(DBHelper.COL2)))
            );
        }
        mydata.close();
    }
}

The resultant output to the log was (....... indicating similar rows not included for brevity) :-

10-04 12:25:18.495 6727-6727/mjt.so46550513 D/GetExpRows: Rows Extracted from base data = 4
10-04 12:25:18.505 6727-6727/mjt.so46550513 D/MYDATA: Row 0 for Date 2016364 has Value 0
10-04 12:25:18.505 6727-6727/mjt.so46550513 D/MYDATA: Row 1 for Date 2016365 has Value 0
10-04 12:25:18.505 6727-6727/mjt.so46550513 D/MYDATA: Row 2 for Date 2016366 has Value 0
10-04 12:25:18.505 6727-6727/mjt.so46550513 D/MYDATA: Row 3 for Date 2017001 has Value 100
10-04 12:25:18.505 6727-6727/mjt.so46550513 D/MYDATA: Row 4 for Date 2017002 has Value 0
.......
10-04 12:25:18.506 6727-6727/mjt.so46550513 D/MYDATA: Row 36 for Date 2017034 has Value 0
10-04 12:25:18.506 6727-6727/mjt.so46550513 D/MYDATA: Row 37 for Date 2017035 has Value 50
10-04 12:25:18.506 6727-6727/mjt.so46550513 D/MYDATA: Row 38 for Date 2017036 has Value 0
10-04 12:25:18.506 6727-6727/mjt.so46550513 D/MYDATA: Row 39 for Date 2017037 has Value 0
....
10-04 12:25:18.508 6727-6727/mjt.so46550513 D/MYDATA: Row 151 for Date 2017148 has Value 0
10-04 12:25:18.508 6727-6727/mjt.so46550513 D/MYDATA: Row 152 for Date 2017149 has Value 0
10-04 12:25:18.508 6727-6727/mjt.so46550513 D/MYDATA: Row 153 for Date 2017150 has Value 17
10-04 12:25:18.508 6727-6727/mjt.so46550513 D/MYDATA: Row 154 for Date 2017151 has Value 0
10-04 12:25:18.508 6727-6727/mjt.so46550513 D/MYDATA: Row 155 for Date 2017152 has Value 0
10-04 12:25:18.508 6727-6727/mjt.so46550513 D/MYDATA: Row 156 for Date 2017153 has Value 0
.......
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 365 for Date 2017362 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 366 for Date 2017363 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 367 for Date 2017364 has Value 33
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 368 for Date 2017364 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 369 for Date 2017365 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 370 for Date 2018001 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 371 for Date 2018002 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 372 for Date 2018003 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 373 for Date 2018004 has Value 0
10-04 12:25:18.510 6727-6727/mjt.so46550513 D/MYDATA: Row 374 for Date 2018005 has Value 0

Note! you would likely include improved error/exception handling especially for string to date conversions, as this is intended as an in-principle example

Thank you so much. This will take me a little time to absorb and try. :) I'm working on it — seekingStillness, Oct 04 '17 at 14:16

Saving data to SQLite db automatically once per day

1 Answers1