How to display date from SQLite db as X-axis in GraphView of another activity, from showing only 1-1-1970?


I build an app that does some calculations in MainActivity.java and stores the results in SQLite database. When the user presses a button, opens the GraphActivity.java with the corresponding GraphView. Maybe it is because I don't have so much experience with SQLite databases yet but I can't get the Graph's X-axis to show the date each result was stored.

So, given that the Insert method and and the Graph take place in separate activities, how can I get the stored dates to show as X-axis in the GraphActivity?

I followed the YouTube tutorial for GraphView date & SQLite but it has the storing and the Graph in the same activity. (https://www.youtube.com/watch?v=lSgK6-cKjmA&list=PLFh8wpMiEi88ojfNpavGpMB0dtP4mvEqa&index=16)

I tried to send the date variable with Intent EXTRAS but it forces you to the GraphActivity when the Save button is pressed. Then I tried getting the time in the GraphActivity, which works but shows the date in X-axis as continuous "1-1-1970" for every result. I used format to show the current date with timestamp but still it doesn't show the date each result was stored, just the current date for all.

MainActivity.java: Insert method for the Results and Date

public void insertData() {
    try {
        (...)

        // Gets the time & date the results are stored

        String timeStamp = new SimpleDateFormat("d M yy hh:mm").format(Calendar.getInstance().getTime());

        // Create a ContentValues object where column names are the keys,
        // and container attributes from the MainActivity are the values.

        ContentValues values = new ContentValues();
        values.put(DexameniEntry.COLUMN_CONTAINER_NAME, "TODO");
        values.put(DexameniEntry.COLUMN_a, X1aInput);
        values.put(DexameniEntry.COLUMN_DATE, timeStamp);
        values.put(DexameniEntry.COLUMN_b, X1bInput);
        values.put(DexameniEntry.COLUMN_c, X1cInput);
        values.put(DexameniEntry.COLUMN_d, dX1StringDouble);
        values.put(DexameniEntry.COLUMN_e, eX1StringDouble);
        values.put(DexameniEntry.COLUMN_percent, percentX1fromDoubletoString);

        (...)
        } catch (NumberFormatException e) {

        } catch (NullPointerException e) {
        }
}

GraphActivity.java : Setting the GraphView

public class GraphActivity extends AppCompatActivity { 
    (...)
    SimpleDateFormat sdf = new SimpleDateFormat("EEE d M yyyy h mm", Locale.US);

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

    GraphView bigGraphX1 = findViewById(R.id.bigGraph);
    mDbHelper = new ResultsDbHelper(this);
    sqLiteDatabase = mDbHelper.getWritableDatabase();

    bigGraphX1.getGridLabelRenderer().setLabelFormatter(new DefaultLabelFormatter() {

        @Override
        public String formatLabel(double value, boolean isValueX) {
            if (isValueX) {
                return
                        sdf.format(new Date((long)value));
            }else {
                return super.formatLabel(value, isValueX);
            }
        }
    });

    (...)

EDIT The DbHelper.class (named ResultsDbHelper)

public class ResultsDbHelper extends SQLiteOpenHelper {

   public static final String LOG_TAG = ResultsDbHelper.class.getSimpleName();

//    Name of the database in a String type constant (to be referenced later)
private static final String DATABASE_NAME = "containerResults.db";
//    Database version in a Integer type constant for updating the database
private static final int DATABASE_VERSION = 1;

//    Constructor: Constructs a new instance of ResultsDbHelper.
//     * @param is the context of the app

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

//    Modify the onCreate method to have the table created on startup of the app
@Override
public void onCreate(SQLiteDatabase db) {

// Define the schema of the table: which columns will be created by the onCreate method.
//        The whole schema creation is put in a String variable (CREATE_RESULTS_TABLE) for easy reference

    String CREATE_RESULTS_TABLE = "CREATE TABLE " + DexameniEntry.TABLE_NAME + " ("
            + DexameniEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + DexameniEntry.COLUMN_CONTAINER_NAME + " TEXT NOT NULL, "
            + DexameniEntry.COLUMN_DATE + " TEXT NOT NULL, "
            + DexameniEntry.COLUMN_a + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_b + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_c + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_d + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_e + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_percent + " REAL NOT NULL);";

//        Execute the SQL Statement (Create the table)
    db.execSQL(CREATE_RESULTS_TABLE);
}

/**
 * This is called when the database needs to be upgraded.
 */
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    final String LOG_TAG = ("Upgrading the database from version " + oldVersion + " to " + newVersion);
    // on upgrade drop older tables
    db.execSQL("DROP TABLE IF EXISTS " + DexameniEntry.TABLE_NAME);

    //        Recreate tables
    onCreate(db);
    }
}

I expect the X-axis to display the date each result was stored, so that all the values to show as LineSeries in the GraphView. But I can't seem to get the dates from the database to the Graph so the date is always the same for all, and the LineSeries is not coherent.

EDIT: Before posting the question I had began also looking for a way to put in the GraphActivity a database reference of the DATE column, inside the FormatLabel method. For example

@Override
    public String formatLabel(double value, boolean isValueX) {
        if (isValueX) {
            return
             sdf.format(new Date(**put here the DATE column variable so that the X-axis is populated the column's data**);
        }else {
            return super.formatLabel(value, isValueX);
        }

I couldn't do this either because I don't know how to write this code...

EDIT: Picture of the issue

I don't know what else to do to solve this, can you please help me?

EDIT 2: GraphActivity after changes (It seems like the graph still isn't populated by the db data.Maybe problem with Saving the data in the db?idk..)

- - Source

Answers

answered 1 week ago MikeT #1

You are trying to do too much in the MainActivity and you are perhaps having issues with the date as it appears from your comments that they/it is 0.

Basically you probably want to pass the criteria for generating the graph (e.g. a date range for the graph) and then extract the data from the database via methods coded in the Databasehelper (ResultsHelper).

Instead of getting the current time when adding data and storing it in the database in D M Y format. You could simply matters (e.g. extracting data according to a date range) by storing the date in a sortable format and for manipulating/formatting dates in a format that is SQLite friendly primarily YYYY-MM-DD HH:MM:SS (although you could simply store the timestamp).

As such I'd suggest some relatively simple changes.

  1. Change the date column definition to be :-

    + DexameniEntry.COLUMN_DATE + " TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, " //<<<<<<<<<< LET SQLITE GET THE DATE TIME

    • This allows SQLite to store the current date and time if you simply don't provide a value, which is done by commenting out/deleting the line :- //values.put(DexameniEntry.COLUMN_DATE, timeStamp); //<<<<<<<<<< Use default
  2. Move the insertData method into ResultHeper (it can then be used elsewhere)

  3. Add a method to the ResultHelper to extract the data into a Cursor, including the dynamic generation of columns.

    • dynamic columns are additional columns generated on the fly telling SQLite to do things.
    • e.g. say you wanted to add 2 columns a and b, you can say SELECT *, a + b AS my_addition FROM the_table; and the result will include all columns AND the column my_addition.
  4. Pass the basic/minimum selection criteria for extracting the data for the graph from the MainActivity to the GraphActivity and then extract the actual data in the graphActivity.

Note Changed to populate Graph with date labels.

Working Example

The following code, based up your code and the above points, is a working example (it doesn't populate the graph but gets the data).

DexameniEntry.java

public class DexameniEntry {

    public static final String[] WEEKDAYS_LONG = new String[]{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"};
    public static final String[] WEEKDAYS_SHORT = new String[]{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"};

    public static final String _ID = BaseColumns._ID;
    public static final String TABLE_NAME = "container_results";
    public static final String COLUMN_CONTAINER_NAME = "container_name";
    public static final String COLUMN_DATE = "container_date";
    public static final String COLUMN_a = "container_value_a";
    public static final String COLUMN_b = "container_value_b";
    public static final String COLUMN_c = "container_value_c";
    public static final String COLUMN_d = "container_value_d";
    public static final String COLUMN_e = "container_value_e";
    public static final String COLUMN_percent = "container_percent";


    public static final String EXTRACT_COLUMN_DATELABEL = "date_label";
    public static final String EXTRACT_COLUMN_GRAPHDATELABEL = "graph_date_label";

    public static final String INTENTKEY_GRAPHVALUE = "ik_graph_value";
}
  • This is made up primarily with guess work (column and table names). However note the following two constants (the WEEKDAYS_?? constants could be used)
    • EXTRACT_COLUMN_DATELABEL (dynamically generated column name) and
    • EXTRACT_COLUMN_GRAPHDATELABEL (dynamically generated unix timestamp)
    • INTENTKEY_GRAPHVALUE (used to pass intent value)

ResultHelper

public class ResultsDbHelper extends SQLiteOpenHelper {

    public static final String LOG_TAG = ResultsDbHelper.class.getSimpleName();

    // Date format that suits SQLite i.e. YYYY-MM-DD HH:MM:SS
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); // Not used but could be useful

    //    Name of the database in a String type constant (to be referenced later)
    private static final String DATABASE_NAME = "containerResults.db";
    //    Database version in a Integer type constant for updating the database
    private static final int DATABASE_VERSION = 1;

    SQLiteDatabase mDB;

//    Constructor: Constructs a new instance of ResultsDbHelper.
//     * @param is the context of the app

    public ResultsDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mDB = this.getWritableDatabase(); // Sets mDB (also forces open of DB)
    }

    //    Modify the onCreate method to have the table created on startup of the app
    @Override
    public void onCreate(SQLiteDatabase db) {

// Define the schema of the table: which columns will be created by the onCreate method.
//        The whole schema creation is put in a String variable (CREATE_RESULTS_TABLE) for easy reference

        String CREATE_RESULTS_TABLE = "CREATE TABLE " + DexameniEntry.TABLE_NAME + " ("
                + DexameniEntry._ID + " INTEGER PRIMARY KEY, " //<<<<<<<<<< AUTOINCREMENT NOT REQD AS IS INEFFICIENT
                + DexameniEntry.COLUMN_CONTAINER_NAME + " TEXT NOT NULL, "
                + DexameniEntry.COLUMN_DATE + " TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, " //<<<<<<<<<< LET SQLITE GET THE DATE TIME
                + DexameniEntry.COLUMN_a + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_b + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_c + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_d + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_e + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_percent + " REAL NOT NULL);";

//        Execute the SQL Statement (Create the table)
        db.execSQL(CREATE_RESULTS_TABLE);
    }

    /**
     * This is called when the database needs to be upgraded.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        final String LOG_TAG = ("Upgrading the database from version " + oldVersion + " to " + newVersion);
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + DexameniEntry.TABLE_NAME);

        //        Recreate tables
        onCreate(db);
    }

    public void insertData(String name,
                           Double X1aInput,
                           Double X1bInput,
                           Double X1cInput,
                           Double X1dInput,
                           Double X1eInput,
                           Double percent) {

        String timeStamp = "";
        try {
            // Gets the time & date the results are stored
            //timeStamp = new SimpleDateFormat("d M yy hh:mm").format(Calendar.getInstance().getTime());
            timeStamp = sdf.format(Calendar.getInstance().getTime());

        } catch (NumberFormatException e) {

        } catch (NullPointerException e) {
        }

        // Create a ContentValues object where column names are the keys,
        // and container attributes from the MainActivity are the values.

        ContentValues values = new ContentValues();
        values.put(DexameniEntry.COLUMN_CONTAINER_NAME, name);
        values.put(DexameniEntry.COLUMN_a, X1aInput);
        //values.put(DexameniEntry.COLUMN_DATE, timeStamp); //<<<<<<<<<< Use default
        values.put(DexameniEntry.COLUMN_b, X1bInput);
        values.put(DexameniEntry.COLUMN_c, X1cInput);
        values.put(DexameniEntry.COLUMN_d, X1dInput);
        values.put(DexameniEntry.COLUMN_e, X1eInput);
        values.put(DexameniEntry.COLUMN_percent, percent);
        mDB.insert(DexameniEntry.TABLE_NAME,null,values);
    }

    public Cursor getAllResults() {
        String formatted_date = "strftime('%d %m %Y'," +
                DexameniEntry.COLUMN_DATE + ") AS " + DexameniEntry.EXTRACT_COLUMN_DATELABEL;
        String unixtype_date = "julianday(" + DexameniEntry.COLUMN_DATE + ") * 86400 * 1000 AS " +DexameniEntry.EXTRACT_COLUMN_GRAPHDATELABEL;
        String[] columns = new String[]{"*",formatted_date,unixtype_date};
        return mDB.query(DexameniEntry.TABLE_NAME,columns,null,null,null,null,DexameniEntry.COLUMN_DATE + " ASC");
    }
}
  • The date column has been changed to have a special DEFAULT value of CURRENT_TIMESTAMP
  • The insertData method has been moved here from MainActivity and now requires parameters passed to it (see it being used in MainActivity)
  • The getAllResults has been added (see GrapActivty for where getAllreults is used)
    • note that the resultant Cursor has all columns from the table PLUS two extra (dynamically generated) columns that will have the date in DD MM YYYY format (the date column will have the date in YYYY-DD-MM HH:MM:SS format (example output later)) and the other as a unix timestamp. (julianday(the_column) * 86400 * 1000 AS the_column_name )

MainActivity.java

MainActivity loads some data (only the first time it is run) and has a button that when clicked will start GraphActivity.

public class MainActivity extends AppCompatActivity {

    Button mShowGraph;
    Context mContext;
    ResultsDbHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mContext = this;
        mShowGraph = this.findViewById(R.id.show_graph);
        setupShowGraphButton();

        //Instantiate the DatabaseHelper
        mDBHlpr = new ResultsDbHelper(this);

        // Add some data if none exists
        if (DatabaseUtils.queryNumEntries(mDBHlpr.getWritableDatabase(),DexameniEntry.TABLE_NAME) < 1) {
            mDBHlpr.insertData("C1", 10.12, 11.12, 12.12, 13.12, 14.12, 25D);
            mDBHlpr.insertData("C2", 20.12, 21.12, 22.12, 23.12, 24.12, 35D);
            mDBHlpr.insertData("C3", 31.12, 32.12, 33.12, 34.12, 35.12, 25D);
        }
    }

    // Setup the Buttons on click listener to start the GraphActivity
    private void setupShowGraphButton() {
        mShowGraph.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent i  = new Intent(mContext,GraphActivity.class);
                i.putExtra(DexameniEntry.INTENTKEY_GRAPHVALUE,100);
                startActivity(i);
            }
        });
    }
}

GraphActivity

Although this display the Graph it doesn't load any data, rather the dates are extracted from the Database and written to the log.

public class GraphActivity extends AppCompatActivity {

    Context mContext;
    Button mDone;
    GraphView mBigGraphX1;
    ResultsDbHelper mDBHlpr;
    Cursor mResults;
    LineGraphSeries<DataPoint> mLGS;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_graph);
        mContext = this;
        mDone = this.findViewById(R.id.done);
        setupDoneButton();
        mDBHlpr = new ResultsDbHelper(this);
        mResults = mDBHlpr.getAllResults();
        mBigGraphX1 = this.findViewById(R.id.graphview);
        demoDatesFromDB();
        doTheGraphLineStuff();
    }

    private void setupDoneButton() {
        mDone.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                finish();
            }
        });
    }

    @Override
    protected void onDestroy() {
        mResults.close(); //<<<<<<<<<< Should always close cursor when done
        super.onDestroy();
    }

    private void demoDatesFromDB() {
        StringBuilder sb = new StringBuilder("Data From Table ").append(DexameniEntry.TABLE_NAME).append(" as per :-");
        while (mResults.moveToNext()) {
            String date_asperdb = mResults.getString(mResults.getColumnIndex(DexameniEntry.COLUMN_DATE));
            String date_formatted = mResults.getString(mResults.getColumnIndex(DexameniEntry.EXTRACT_COLUMN_DATELABEL));
            long unix_date = mResults.getLong(mResults.getColumnIndex(DexameniEntry.EXTRACT_COLUMN_GRAPHDATELABEL));
            sb.append("\n\tResults for row ");
            sb.append(String.valueOf(mResults.getPosition() + 1));
            sb.append(" Date as per DB is ").append(date_asperdb);
            sb.append(" Formatted Date is ").append(date_formatted);
            sb.append(" Unix Date is ").append(unix_date);
        }
        Log.d("DEMODATEINFO",sb.toString());
    }

    private void doTheGraphLineStuff() {

        long one_day = 1000 * 60 * 60 * 24;
        mResults = mDBHlpr.getAllResults();
        int rowcount = mResults.getCount();


        long[] extracted_dates = new long[rowcount];

        DataPoint[] dataPoints = new DataPoint[rowcount];
        while (mResults.moveToNext()) {

            /**
             * NOTE  as all 3 rows are added within milliseconds this adds on a day
             */
            extracted_dates[mResults.getPosition()] = mResults.getLong(
                    mResults.getColumnIndex(
                            DexameniEntry.EXTRACT_COLUMN_GRAPHDATELABEL
                    ))
                    + (one_day * ((long)mResults.getPosition() + 1L) //<<<< Frig the data show it is one day more than the previous

            );
            dataPoints[mResults.getPosition()] = new DataPoint(
                    extracted_dates[mResults.getPosition()],
                    mResults.getDouble(mResults.getColumnIndex(DexameniEntry.COLUMN_a))
            );

        }
        mLGS = new LineGraphSeries<>(dataPoints);
        mBigGraphX1.addSeries(mLGS);
        mBigGraphX1.getGridLabelRenderer().setLabelFormatter(new DateAsXAxisLabelFormatter(mContext));
        mBigGraphX1.getGridLabelRenderer().setNumHorizontalLabels(rowcount);
        mBigGraphX1.getViewport().setMinX(extracted_dates[0]);
        mBigGraphX1.getViewport().setMaxX(extracted_dates[rowcount - 1]);
        mBigGraphX1.getViewport().setXAxisBoundsManual(true);
        mBigGraphX1.getGridLabelRenderer().setHumanRounding(false);

    }

Result

The following is output to the Log :-

2019-01-12 09:01:07.194 3073-3073/so54140390.so54140390graph D/DEMODATEINFO: Data From Table container_results as per :-
        Results for row 1 Date as per DB is 2019-01-11 21:02:06 Formatted Date is 11 01 2019
        Results for row 2 Date as per DB is 2019-01-11 21:02:06 Formatted Date is 11 01 2019
        Results for row 3 Date as per DB is 2019-01-11 21:02:06 Formatted Date is 11 01 2019

So it's just a matter of perhaps changing or adding the formats to suit. - The bible for this is SQL As Understood By SQLite- Date And Time Functions

The Graph from the above :-

enter image description here

comments powered by Disqus