Storing, retrieving and sorting records by time and date

Question

I think I may be asking too much in a single post, so let me know if I should break this up into separate questions for date and for time, but utilising date and time in a database has me completely stumped. I've looked into the various methods (strings, juliandates, unix, etc) and all it's done is confuse me further. To provide some context, the user adds records by filling in a form. The form has 2 buttons, one opens a fragment with a datepicker, and one opens a fragment with a timepicker. The chosen date and time is shown as the button text. Another button saves the form details into the db.

Next, I have a listview activity that shows all records at the moment, but I eventually want to have a button to a datepicker fragment, so that only records with a specific date are shown. Those records are then listed in order of time. (I think I can figure out this paragraph myself once the next paragraph is resolved.)

Clicking on one of the records in the listview opens an activity that has the same appearance as the original form (where the record was created) and it is populated with all the info from that record. The user then has the choice to delete the record, or modify the fields in the record and save (update) the record. This form should also contain a date and time button so these can be changed as well, so the pickers should be set to the previously saved dates/times in this activity.

What I need help with, is the best method of storing the date and time to then retrieve and edit it later in the editing form, including formatting it into a specific method to display as the button text (DD/MM/yyyy for the date and hh:mm:am/pm for time). As mentioned before this has confused the hell out of me so I really would appreciate some hand-holding with this. Apologies for the huge amount of coding below but I've tried to keep it relevant by cutting out all the extra form fields.

FuncAdd.java (form to save the data):

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

    now = Calendar.getInstance();
    add_button_date = (Button) findViewById(R.id.add_button_date);
    add_button_time = (Button) findViewById(R.id.add_button_time);

    add_button_date.setText(String.valueOf(now.get(Calendar.DAY_OF_MONTH))+"/"+
            String.valueOf(now.get(Calendar.MONTH)+1)+"/"+
            String.valueOf(now.get(Calendar.YEAR)));
    strDate = add_button_date.getText().toString();

    add_button_time.setText(String.valueOf(now.get(Calendar.HOUR_OF_DAY))+":"+
            String.valueOf(now.get(Calendar.MINUTE)));
}

public void addDateTime(View v) {
    int id = v.getId();
    switch (id) {
    case R.id.add_button_date :
        showDialogDate();
        break;
    case R.id.add_button_time :
        showDialogTime();
        break;
    default:
        break;
}}

public interface DateDialogFragmentListener {
    public void updateChangedDate(int year, int month, int day);
}

public interface TimeDialogFragmentListener {
    public void updateChangedTime(int hourOfDay, int minute);
}

public void showDialogDate() {
    FragmentTransaction ftDate = getFragmentManager().beginTransaction();
    fragDate = DateDialogFragment.newInstance(this, new DateDialogFragmentListener() {
        public void updateChangedDate(int year, int month, int day) {
            add_button_date.setText(String.valueOf(day)+"/"+String.valueOf(month+1)+"/"+String.valueOf(year));
            now.set(Calendar.YEAR, year);
            now.set(Calendar.MONTH, month);
            now.set(Calendar.DAY_OF_MONTH, day);
        }}, now);
    fragDate.show(ftDate, "DateDialogFragment");
}

public void showDialogTime() {
    FragmentTransaction ftTime = getFragmentManager().beginTransaction();
    fragTime = TimeDialogFragment.newInstance(this, new TimeDialogFragmentListener() {
        public void updateChangedTime(int hourOfDay, int minute) {
            add_button_time.setText(String.valueOf(hourOfDay)+":"+String.valueOf(minute));
            now.set(Calendar.HOUR_OF_DAY, hourOfDay);
            now.set(Calendar.MINUTE, minute);
        }}, now);
    fragTime.show(ftTime, "TimeDialogFragment");
}

public void onClick(View arg0) {

    strDate = add_button_date.getText().toString();
    strTime = add_button_time.getText().toString();

    String Time = strTime;
    String Date = strDate;
AddDBHelper entry = new AddDBHelper(FuncAdd.this);
    entry.open();
    entry.createEntry(Time, Date, Category, CategoryPos, BGL, Carbs, Insulin, InsulinPos, Dose, CorInsulin, CorInsulinPos, CorDose);
    entry.close();              

    goHome (this);
}

AddDBHelper.java (My DB Helper class):

private static class DbHelper extends SQLiteOpenHelper {

    public DbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DB_TABLE + " (" +
            KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            KEY_TIME + " TEXT NOT NULL, " +
            KEY_DATE + " TEXT NOT NULL, " +
            KEY_CATEG + " TEXT NOT NULL);"
);}

public long createEntry(String Time, String Date, String Category) {
    ContentValues cv = new ContentValues();
    cv.put(KEY_TIME, Time);
    cv.put(KEY_DATE, Date);
    cv.put(KEY_CATEG, Category);
    return glucoDB.insert(DB_TABLE, null, cv);
}

public Cursor getEntries() {        
    return glucoDB.query(DB_TABLE, new String[] {
            KEY_ROWID,
            KEY_TIME,
            KEY_DATE,
            KEY_CATEG},
            null, null, null, null, null);
}

public void updateDetails(DBDetails dbdetails) {

    ContentValues cv = new ContentValues();
    cv.put(AddDBHelper.KEY_TIME, UpdateDBDetails.newTime);
    cv.put(AddDBHelper.KEY_DATE, UpdateDBDetails.newDate);
    cv.put(AddDBHelper.KEY_CATEG, dbdetails.getuCateg());

    int count = glucoDB.update(DB_TABLE, cv, KEY_ROWID + "=?", new String[] {String.valueOf(dbdetails.getuRow())});
    Log.v("AddDbHelper", "Row " + dbdetails.getuRow() + " updated? " + count);
}

*TimeDialogFragment class: *

public class TimeDialogFragment extends DialogFragment {

static Context mContext;
static int mHour;
static int mMinute;
static TimeDialogFragmentListener mListener;

public static TimeDialogFragment newInstance(Context context,
        TimeDialogFragmentListener listener, Calendar now) {
    TimeDialogFragment dialog = new TimeDialogFragment();
    mContext = context;
    mListener = listener;

    mHour = now.get(Calendar.HOUR_OF_DAY);
    mMinute = now.get(Calendar.MINUTE);

    Bundle args = new Bundle();
    args.putString("title", "Set Time");
    dialog.setArguments(args);
    return dialog;
}

public Dialog onCreateDialog(Bundle savedInstanceState) {
    return new TimePickerDialog(mContext, mTimeSetListener, mHour, mMinute, false);
}

private TimePickerDialog.OnTimeSetListener mTimeSetListener =
        new TimePickerDialog.OnTimeSetListener() {

    public void onTimeSet(TimePicker view, int hourOfDay, int minute) {
        mHour = hourOfDay;
        mMinute = minute;
        mListener.updateChangedTime(hourOfDay, minute);
    }
}; }

DateDialogFragment class:

public class DateDialogFragment extends DialogFragment {

static Context mContext;
static int mYear;
static int mMonth;
static int mDay;
static DateDialogFragmentListener mListener;

public static DateDialogFragment newInstance(Context context,
        DateDialogFragmentListener listener, Calendar now) {
    DateDialogFragment dialog = new DateDialogFragment();
    mContext = context;
    mListener = listener;

    mYear = now.get(Calendar.YEAR);
    mMonth = now.get(Calendar.MONTH);
    mDay = now.get(Calendar.DAY_OF_MONTH);

    Bundle args = new Bundle();
    args.putString("title", "Set Date");
    dialog.setArguments(args);
    return dialog;          
}

public Dialog onCreateDialog(Bundle savedInstanceState) {
    return new DatePickerDialog(mContext, mDateSetListener,
            mYear, mMonth, mDay);
}

private DatePickerDialog.OnDateSetListener mDateSetListener = 
        new DatePickerDialog.OnDateSetListener() {

    public void onDateSet(DatePicker view, int year, int monthOfYear,
            int dayOfMonth) {
        mYear = year;
        mMonth = monthOfYear;
        mDay = dayOfMonth;
        mListener.updateChangedDate(year, monthOfYear, dayOfMonth);     
            }
    };}

DBDetails.java (class to get/set values):

public class DBDetails {

private String uRow;
private String uTime;
private String uDate;
private String uCateg;

public String getuRow() {
    return uRow;
}

public void setuRow(String uRow) {
    this.uRow = uRow;
}

public String getuTime() {
    return uTime;
}

public void setuTime(String uTime) {
    this.uTime = uTime;
}

public String getuDate() {
    return uDate;
}

public void setuDate(String uDate) {
    this.uDate = uDate;
}

public String getuCateg() {
    return uCateg;
}

public void setuCateg(String uCateg) {
    this.uCateg = uCateg;
} }

UpdateDBDetails.java (Class where user can edit the records):

public class UpdateDBDetails extends DashMenuActivity {

TimeDialogFragment fraguTime;
DateDialogFragment fraguDate;
Button update_button_time, update_button_date;
Calendar now;

ImageButton update_button_save, update_button_delete;   
String strTime, strDate;

private String bundleduRowId;
private String bundleduTime;
private String bundleduDate;
private String bundleduCateg;
public static String getRowId;
public static String newTime, newDate, newCat;

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.updatedbdetails);

    now = Calendar.getInstance();
    update_button_date = (Button) findViewById(R.id.update_button_date);
    update_button_time = (Button) findViewById(R.id.update_button_time);

    update_button_date.setText(bundleduDate);
    update_button_time.setText(bundleduTime);

Bundle takeBundledData = getIntent().getExtras();

    bundleduRowId = takeBundledData.getString("clickeduRowId");
    bundleduTime = takeBundledData.getString("clickeduTime");
    bundleduDate = takeBundledData.getString("clickeduDate");
    bundleduCateg = takeBundledData.getString("clickeduCateg");
}
public void clickUpdateDelete(View v) {
    newTime = strTime;
    newDate = strDate;
    newCateg = newCat;

AddDBHelper modifyEntry = new AddDBHelper(this);
    modifyEntry.open();

    DBDetails dbdetails = new DBDetails();

    dbdetails.setuRow(bundleduRowId);

    if (v.getId() == R.id.update_button_delete) {
        modifyEntry.deleteDetails(dbdetails);
    } else if (v.getId() == R.id.update_button_save) {
        dbdetails.setuDate(newDate);
        dbdetails.setuTime(newTime);
        dbdetails.setuCateg(newCateg);
modifyEntry.updateDetails(dbdetails);
    }   
    modifyEntry.close();
    finish();
}

Amendments to the FuncAdd.java class

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView (R.layout.dash_add);

    add_button_date = (Button) findViewById(R.id.add_button_date);
    add_button_time = (Button) findViewById(R.id.add_button_time);

    now = Calendar.getInstance().getTime();

    SimpleDateFormat dFormatter = new SimpleDateFormat("dd MMM yyyy");
    strDate = dFormatter.format(now);
    add_button_date.setText(strDate);

    SimpleDateFormat tFormatter = new SimpleDateFormat("KK:mm a");
    strTime = tFormatter.format(now);
    add_button_time.setText(strTime);
}

public interface DateDialogFragmentListener {
    public void updateChangedDate(int year, int month, int day);
}

public void showDialogDate() {
    FragmentTransaction ftDate = getFragmentManager().beginTransaction();
    fragDate = DateDialogFragment.newInstance(this, new DateDialogFragmentListener() {
        public void updateChangedDate(int year, int month, int day) {
            strDate = (String.valueOf(day)+"/"+String.valueOf(month+1)+"/"+String.valueOf(year));
            SimpleDateFormat dFormatter = new SimpleDateFormat("dd MMM yyyy");
            newstrDate = dFormatter.parse(strDate);
            add_button_date.setText(newstrDate);
        }}, now);
    fragDate.show(ftDate, "DateDialogFragment");
}

Amendments to UpdateDBDetails.java

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.updatedbdetails);

Bundle takeBundledData = getIntent().getExtras();

    bundleduRowId = takeBundledData.getString("clickeduRowId");
    bundleduTime = takeBundledData.getString("clickeduTime");
    bundleduDate = takeBundledData.getString("clickeduDate");

SimpleDateFormat dFormatter = new SimpleDateFormat("dd MMM yyyy");
    SimpleDateFormat tFormatter = new SimpleDateFormat("KK:mm a");

    now = Calendar.getInstance();

// no need to catch an exception, as I'm using pickers and the format can never be input incorrectly
    try {
        dDate = dFormatter.parse(bundleduDate);
        String dateArray[] = dDate.toString().split(" ");

        if (dateArray[1].equalsIgnoreCase("Jan")) {
            uMonth = 0;
        } else if (dateArray[1].equalsIgnoreCase("Feb")) {
            uMonth = 1;
        } else if (dateArray[1].equalsIgnoreCase("Mar")) {
            uMonth = 2;
        } else if (dateArray[1].equalsIgnoreCase("Apr")) {
            uMonth = 3;
        } else if (dateArray[1].equalsIgnoreCase("May")) {
            uMonth = 4;
        } else if (dateArray[1].equalsIgnoreCase("Jun")) {
            uMonth = 5;
        } else if (dateArray[1].equalsIgnoreCase("Jul")) {
            uMonth = 6;
        } else if (dateArray[1].equalsIgnoreCase("Aug")) {
            uMonth = 7;
        } else if (dateArray[1].equalsIgnoreCase("Sep")) {
            uMonth = 8;
        } else if (dateArray[1].equalsIgnoreCase("Oct")) {
            uMonth = 9;
        } else if (dateArray[1].equalsIgnoreCase("Nov")) {
            uMonth = 10;
        } else if (dateArray[1].equalsIgnoreCase("Dec")) {
            uMonth = 11;
        } 

        int uYear = Integer.parseInt(dateArray[5]);
        int uDay = Integer.parseInt(dateArray[2]);
        now.set(Calendar.YEAR, uYear);
        now.set(Calendar.MONTH, uMonth);
        now.set(Calendar.DAY_OF_MONTH, uDay);
        CurrentD = now.getTime();
        strDate = dFormatter.format(CurrentD);
        update_button_date.setText(strDate);
    } catch (ParseException e) {
        e.printStackTrace();
    }

    try {
        tDate = tFormatter.parse(bundleduTime);
        String timeArrayFull[] = tDate.toString().split(" ");
        String timeArray[] = timeArrayFull[3].split(":");
        int uHour = Integer.parseInt(timeArray[0]);
        int uMinute = Integer.parseInt(timeArray[1]);
        now.set(Calendar.HOUR_OF_DAY, uHour);
        now.set(Calendar.MINUTE, uMinute);
        CurrentT = now.getTime();
        strTime = tFormatter.format(CurrentT);
        update_button_time.setText(strTime);
    } catch (ParseException e) {
        e.printStackTrace();
    }
\\ The rest of the code does not differ from above, for launching a dialogfragment and saving the results to the DB.

[See-1](http://stackoverflow.com/questions/9912331/sorting-by-date-an-array-of-bean-having-date-member) AND [See BlackBelt's Links](http://stackoverflow.com/questions/9912574/sorting-list-having-itembean-by-date) — Chintan Raghwani, Aug 04 '12 at 05:38

Andy · Answer 1 · 2012-08-04T07:00:18.147

1

Well I don't really understand why it has you stumped. But I guess if you are new to relational databases, its an understandable problem beginners have. The best method for storing your dates and times is in a format that is easily understood by most databases, even if you don't plan on ever exporting it. Check out the SQLite website. To make this short, just store them as strings in the database in a YYYY-MM-DD (for date) and HH:MM:SS (for time) format. Then you can use SQLite's sweet built in functions that you can see on the link I provided :) Hope it helps.

Edit:

I also did a little extra digging because I noticed my app actually stores dates and times as INTEGER values, check out the different data types in SQLite, but more importantly for you, section 1.2 on Date and Time data types. So holding them as TEXT or INTEGER is fine, just be consistent.

edited Aug 04 '12 at 07:00

answered Aug 04 '12 at 06:46

Andy

10,553
21
75
125

Thanks. I was familiar with storing and retrieving as I've done it with the rest of the form elements. I had anticipated that I would store as a string, then split it in order to set the dates/times to the new pickers. I've now changed the FuncAdd.java (see amendment of original post) to use a date object instead of a calendar, but I have no idea how to modify the datepicker dialog and the showDialogDate() methods. Apologies but I'm quite new to coding, hence the request for some hand-holding. – Ronnie Aug 04 '12 at 10:30
Ahh, yea. You are on the right track. Thats what I do. Just splitting them once you get them from the database works just fine. Not too much work. And remember to subtract 1 from the month before inputting it into the `DatePicker`. Thats one annoyance I learned from setting it all up. When you want to compare the Strings from your database to a date you have as a String, just make sure you input the String inside the SQLite date and time functions to make sure it all works. Hopefully I helped :) Good luck on learning to code. Looks like your'e doing just fine. – Andy Aug 04 '12 at 17:56
I achieved everything I set out to do above. For retrieving and modifying the date, I took the string from the database, split it into an array at every 'space', then used the appropriate parts for 'day' and 'year'. The 'month' is used in an `if else` block, where each month was allocated a value from 0 to 11. I parsed all 3 parts into integers and set them in the Calendar. For time, I split the string into spaces, then split the time string from that array, at every ':', then it was easy to grab the hh and mm integers. Amended my post to assist others who see this. – Ronnie Aug 05 '12 at 01:26
Glad to hear. Accept the best answer please! – Andy Aug 05 '12 at 02:31

score 0 · Accepted Answer · answered Aug 04 '12 at 12:36

Using the formatter that Chintan suggested, and a bit more reading I've managed to figure it out. Andy, your advice was valuable but I was after more assistance with how to set/get the data with the dialogfragment to allow saving, retrieving and updating the DB. In the end, I only needed to amend my main form activity.

public class FuncAdd extends DashMenuActivity implements OnClickListener {

TimeDialogFragment fragTime;
DateDialogFragment fragDate;
Button add_button_time, add_button_date;
Calendar now;
Date nowDT, newD, newT;

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView (R.layout.dash_add);

    add_button_date = (Button) findViewById(R.id.add_button_date);
    add_button_time = (Button) findViewById(R.id.add_button_time);

    now = Calendar.getInstance();
    nowDT = now.getTime();

    SimpleDateFormat dFormatter = new SimpleDateFormat("dd MMM yyyy");
    strDate = dFormatter.format(nowDT);
    add_button_date.setText(strDate);

    SimpleDateFormat tFormatter = new SimpleDateFormat("KK:mm a");
    strTime = tFormatter.format(nowDT);
    add_button_time.setText(strTime);
}

public void addDateTime(View v) {
    int id = v.getId();
    switch (id) {
    case R.id.add_button_date :
        showDialogDate();
        break;
    case R.id.add_button_time :
        showDialogTime();
        break;
    default:
        break;
}}

public interface DateDialogFragmentListener {
    public void updateChangedDate(int year, int month, int day);
}

public interface TimeDialogFragmentListener {
    public void updateChangedTime(int hourOfDay, int minute);
}

public void showDialogDate() {
    FragmentTransaction ftDate = getFragmentManager().beginTransaction();
    fragDate = DateDialogFragment.newInstance(this, new DateDialogFragmentListener() {
        public void updateChangedDate(int year, int month, int day) {               
            now.set(Calendar.YEAR, year);
            now.set(Calendar.MONTH, month);
            now.set(Calendar.DAY_OF_MONTH, day);
            newD = now.getTime();
            SimpleDateFormat newDFormatter = new SimpleDateFormat("dd MMM yyyy");
            strDate = newDFormatter.format(newD);
            add_button_date.setText(strDate);
        }}, now);
    fragDate.show(ftDate, "DateDialogFragment");
}

public void showDialogTime() {
    FragmentTransaction ftTime = getFragmentManager().beginTransaction();
    fragTime = TimeDialogFragment.newInstance(this, new TimeDialogFragmentListener() {
        public void updateChangedTime(int hourOfDay, int minute) {
            now.set(Calendar.HOUR_OF_DAY, hourOfDay);
            now.set(Calendar.MINUTE, minute);
            newT = now.getTime();
            SimpleDateFormat tFormatter = new SimpleDateFormat("KK:mm a");
            strTime = tFormatter.format(newT);
            add_button_time.setText(strTime);
        }}, now);
    fragTime.show(ftTime, "TimeDialogFragment");
}
}

Storing, retrieving and sorting records by time and date

2 Answers2