How can I save hour/minutes into database SQLiteOpenHelper?

Question

How can I save hour/minutes in database SQLiteOpenHelper? With clock and minute format? For example: 12:40 or 00:00.

Answer 1

Just save it as String and format it as you will every time you use the time.

Sqlite doesn't have a DATETIME type.

Answer 2

You can save such data in numerous ways, as text/string, as an integer 0-1440, as two integers (one for the hours and one for the minutes), as long, as a decimal.

The choice may depend upon subsequent usage if it's just to display then text/string may be suitable. If you want to perform calculations then text/string may result in complicated process (see getElapsedTimeForAllTimesAsInt in the example below).

A factor to consider is how/if SQLite datetime functions would be of benefit and if so then it could be wise to save in one of the formats that it recognises (HH:MM (24 hour clock) is such a format). A must read is SQL As Understood By SQLite - Date And Time Functions.

Below is a working example of how (and also perhaps how not to) you can save times :-

The Database Helper - SOD49707387DBHelper.java

• This includes a method for inserting data (no validation of the data though)
  • namely insertTime
• Also included are 3 methods for extracting data :-
  • getAllTimesAsCursor retrieves all rows as a Cursor.
  • getAllTimesAsStringArray retrieves all rows as a String[] (note that it utilises getAllTimesAsCursor)
  • getElapsedTimeOfAllTimesAsInt which returns the number of minutes between the earliest and latest time. This is intended to demonstrate how having to convert/cater for non-sqlite date formats can get a little complex (note it's got flaws and is not intended to be used)
  • The class/file name is lengthy for my convenience, you would likely use a different name.

:-

public class SOD49707387DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydatabase";
    public static final int DBVERSION = 1;
    public static final String TBNAME_TIME = "mytimesave";
    public static final String TIME_COL_TIME = "_time";

    SQLiteDatabase mDB;
    public SOD49707387DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getReadableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crtsql = "CREATE TABLE IF NOT EXISTS " + TBNAME_TIME + "(" +
                TIME_COL_TIME + " TEXT" +
                ")";
        db.execSQL(crtsql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    // Not assumes that time is valid
    public boolean insertTime(String time) {
        ContentValues cv = new ContentValues();
        cv.put(TIME_COL_TIME,time);
        return  (mDB.insert(TBNAME_TIME,null,cv) > 0);
    }

    public Cursor getAllTimesAsCursor() {
         return mDB.query(TBNAME_TIME,
                null,
                null,
                null,
                null,
                null,
                null);
    }

    public String[] getAllTimesAsStringArray() {
        Cursor csr =getAllTimesAsCursor();
        String[] rv = new String[csr.getCount()];
        while (csr.moveToNext()) {
            rv[csr.getPosition()] = csr.getString(csr.getColumnIndex(TIME_COL_TIME));
        }
        csr.close();
        return rv;
    }

    /*
     NOTE!! equates to query
         SELECT
             max((substr(_time,1,2) * 60) + substr(_time,4,2))
             -
             min((substr(_time,1,2) * 60) + substr(_time,42))
         AS elapsedminutes
         FROM mytimesave;
      NOTE!!! inherently unreliable as assumes all rows are valid
     */
    public int getElapsedTimeOfAllTimesAsInt() {
        int rv = 0;
        String result_column =
                "" +
                        "max((substr(" +
                        TIME_COL_TIME +
                        ",1,2) * 60) + substr(" +
                        TIME_COL_TIME + ",4,2))" +
                        " - " +
                        " min((substr(" +
                        TIME_COL_TIME +
                        ",1,2) * 60) + substr(" +
                        TIME_COL_TIME +
                        ",4,2))" +
                        " AS " + TIME_COL_TIME //??? hijack _time column
                ;
        Cursor csr = mDB.query(
                TBNAME_TIME,
                new String[]{result_column},
                null,
                null,
                null,
                null,
                null);
        if (csr.moveToFirst()) {
            rv = csr.getInt(csr.getColumnIndex(TIME_COL_TIME));
        }
        csr.close();
        return rv;
    }
}

Invoking Activity - MainActivity.java

public class MainActivity extends AppCompatActivity {


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

        SO49707387(); // call method for this answer
    }

    private void SO49707387() {
        // Instantiate DBHelper instance
        SOD49707387DBHelper dbhlpr = new SOD49707387DBHelper(this);
        // Insert some data (2 rows each time App is run)
        dbhlpr.insertTime("12:00");
        dbhlpr.insertTime("08:00 AM");
        // Get database information (*1)
        CommonSQLiteUtilities.logDatabaseInfo(dbhlpr.getWritableDatabase());
        // Get all times as a Cursor
        Cursor csr = dbhlpr.getAllTimesAsCursor();
        // traverse the Cursor writing data to the log
        while (csr.moveToNext()) {
            Log.d("TIME_VIA_CURSOR",
                    csr.getString(
                            csr.getColumnIndex(
                                    SOD49707387DBHelper.TIME_COL_TIME)
                    )
            );
        }
        csr.close(); //<<< Should always close cursor when done with them

        // get all times as a String array
        String[] times = dbhlpr.getAllTimesAsStringArray();
        // traverse the array writing data to the log
        for (String s: times) {
            Log.d("TIME_VIA_ARRAY",s);
        }
        // Calculate and print the elapsed time
        // (largest time - smallest time)
        // i.e. 12:00 - 08:00 = 240 minutes = 4 hours
        Log.d("ELAPSED",
                "The elapsed time in minutes is " +
                        String.valueOf(dbhlpr.getElapsedTimeOfAllTimesAsInt())
        );
    }
}

• Note the source for the CommonSQLiteUtilities class can be found here.

Testing results (from Log) :-

04-08 03:33:00.055 1945-1945/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/soanswers.soanswers/databases/mydatabase
    Database Version = 1
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
    Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = mytimesave Created Using = CREATE TABLE mytimesave(_time TEXT)
    Table = mytimesave ColumnName = _time ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
04-08 03:33:00.059 1945-1945/? D/TIME_VIA_CURSOR: 12:00
    08:00 AM
04-08 03:33:00.059 1945-1945/? D/TIME_VIA_ARRAY: 12:00
    08:00 AM
04-08 03:33:00.059 1945-1945/? D/ELAPSED: The elapsed time in minutes is 240