Converting Strings in SQLite database to Date/Time or Doubles in Android for achartengine graph

Question

My problem is converting two strings: KEY_DATE and KEY_TIME into Date/Time formats for a Chart. I don't know whether converting them into doubles could be a solution either.

I have a SQLite database which has a table with four type STRING columns.

public static final String KEY_ROWID = "_id";
public static final String KEY_WORKOUT = "workout_type";
public static final String KEY_DATE = "date";
public static final String KEY_TIME = "workout_time";

This data has been stored into the database with the following SQL.

db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_WORKOUT
                + " TEXT NOT NULL, " + KEY_DATE + " TEXT NOT NULL, "
                + KEY_TIME + " TEXT NOT NULL);"

There is a stopwatch in my workout app. When the user submits there time:

The current system date is converted to a String and stored in the dB as KEY_DATE (Format dd-mmm-yy)
The stopwatch LONG values of hours, mins and sec are converted to String and concatenated as KEY_TIME and stored in the dB (Format: hh:mm:ss)

I would like to display this data in a chart using achartengine, which is up and running in Eclipse.

KEY_DATE will be on the x axis and KEY_TIME will be on the y axis.

The following methods retrieve KEY_DATE and KEY_TIME from the database.

public String getWorkoutDate(){
    String[] columns = new String[] { KEY_ROWID, KEY_WORKOUT, KEY_DATE,
            KEY_TIME };
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null,
            null, null);
    String result = "";

    int iDate = c.getColumnIndex(KEY_DATE);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        result = result + c.getString(iDate);
    }

    return result;
}


public String getWorkoutTime(){
    String[] columns = new String[] { KEY_ROWID, KEY_WORKOUT, KEY_DATE,
            KEY_TIME };
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null,
            null, null);
    String result = "";

    int iTime = c.getColumnIndex(KEY_TIME);


    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        result = result + c.getString(iTime);
    }

    return result;
}

I have created the following methods to convert Strings back into Date and Time formats. I am unsure of how to use them or if they are appropriate at all.

public Date stringToDate(String stringDate) throws java.text.ParseException {

    SimpleDateFormat formatter;
    Date date;
    formatter = new SimpleDateFormat("dd-MMM-yy");
    date = (Date) formatter.parse(stringDate);
    System.out.println("stringToDate: Today is " + date);

    return date;
}

public Date stringToTime(String stringTime) throws ParseException{

    SimpleDateFormat formatter;
    Date time;

    formatter = new SimpleDateFormat("hh:mm:ss");
    time =  (Date) formatter.parse(stringTime);
    System.out.println("Time: " + formatter.format(time));

    return time;

}

I need help in coding the conversion of these Strings to date time. I don't know what collection type to store them in.

In the achartengine samples, the x and y values are stored in ArrayList

The KEY_TIME value will never exceed 60 minutes on the y axis. So it could be converted into a Double eg. 35minutes 30 seconds will be 35.3.

KEY_DATE will be on the x axis as either dd-mmm or dd-mmm-yy. I don't know what type of ArrayList I need to use for storing this data.

Once I have the data stored as the right type, I think I know how to code the data in achartengine using DBHelper.

I'm a novice programmer and need some help with the code. This is the last problem in my code before my very first app is ready for Google Play market! Any help would be greatly appreciated.

"35minutes 30 seconds will be 35.3." - That would actually be 35.5 minutes, not 35.3. If you are going to store the time as a double like this be mindful of the fact that hours do not have 100 minutes, and thus it is not a straight conversion from minutes to decimal of an hour. What is the output of the methods that you've posted? Are they causing an error or just unexpected result? — FoamyGuy, Jun 18 '12 at 22:38
@Tim The get methods return Strings correctly. I don't know to pass this data into the Chart. Can I use a SQL convert command to convert the text to date/time. Good point about the Double issue. — tiptopjat, Jun 18 '12 at 22:54

score 2 · Answer 1 · answered Jun 18 '12 at 23:10

You should take a look at the javadoc provided, and maybe at the support section for discussion groups and forums on their official page: http://www.achartengine.org.

The javadoc should provide you with information on the objects and methods available, such as the datatypes that you need to provide in the arrays for the TimeChart, or your chart of choice.

The discussion groups might aid you get faster answers, since the library has restricted use and there might not be much knowledge of it here.

Also a nice library for time/date related calculation is http://joda-time.sourceforge.net/

This might also give you a starting point: http://stackoverflow.com/questions/8869854/how-to-implement-timechart-in-achartengine-with-android — Morfic, Jun 18 '12 at 23:19

Converting Strings in SQLite database to Date/Time or Doubles in Android for achartengine graph

1 Answers1