Search code examples
sqliteandroid-studiokotlinandroid-sqlite

how to save and retrieve a date with kotlin in android studio using sqlite?

I need help please? I am working on date manipulation with sqlite using kotlin, I would like to know how to do if a user clicks on a button, I offer him a calendar or he chooses a date only (without time) and I get this date to save in sqlite in a TEXT field, then retrieve this field from sqlite and display it in a textview for example? THANK YOU

myTable

Solution

  • You can store a date in many formats. The most efficient, space/storage wise is to store it as an integer type value (typically a long). However, if just storing the date then you can improve the efficiency by dropping the time and just storing an integer that represents the date.

    Here's an example, based upon part of your code, of storing a date obtained via a LocalDate object:-

    First the class that extends the SQLiteOpenHelper class, often termed as the Database Helper and hence it being called DBHelper :-

    const val DATABASE_NAME = "the_database.db"
const val DATABASE_VERSION = 1
const val TABLE_RESERVATION = "reservation"
const val COLUMN_RESERVATION_ID = "id"
const val COLUMN_RESERVATION_DATE = "date_reservation"
private const val CREATE_TABLE_RESERVATION = "CREATE TABLE IF NOT EXISTS ${TABLE_RESERVATION} (${COLUMN_RESERVATION_ID} INTEGER PRIMARY KEY, ${COLUMN_RESERVATION_DATE} INTEGER);"
/* etc */
class DBHelper(context: Context): SQLiteOpenHelper(context, DATABASE_NAME,null, DATABASE_VERSION) {
    override fun onCreate(p0: SQLiteDatabase) {
        p0.execSQL(CREATE_TABLE_RESERVATION)
    }

    override fun onUpgrade(p0: SQLiteDatabase?, p1: Int, p2: Int) {
        TODO("Not yet implemented")
    }

    fun insertReservation(reservationId: Long?, reservationDate: LocalDate): Long {
        val cv = ContentValues()
        if (reservationId!= null) {
            cv.put(COLUMN_RESERVATION_ID,reservationId)
        }
        cv.put(COLUMN_RESERVATION_DATE,reservationDate.toEpochDay())
        return this.writableDatabase.insert(TABLE_RESERVATION,null,cv)
    }
    fun insertReservation(reservationDate: LocalDate): Long = insertReservation(null,reservationDate)

    fun updateReservationDate(reservationId: Long,reservationDate: LocalDate): Int {
        val cv = ContentValues()
        cv.put(COLUMN_RESERVATION_DATE,reservationDate.toEpochDay())
        return this.writableDatabase.update(TABLE_RESERVATION,cv,"${COLUMN_RESERVATION_ID}=?", arrayOf(reservationId.toString()))
    }

    companion object {
        @Volatile
        var instance: DBHelper?=null
        fun getInstance(context: Context): DBHelper {
            if (instance==null) {
                instance=DBHelper(context)
            }
            return instance as DBHelper
        }
    }
}
    • this includes a function for inserting (actually two one with a shorter signature for generating the id rather than passing the id)
    • and it includes a function that can update an existing row according to the id
    • the getInstance function allows a single instance of DBHelper to be used.

    To demonstrate the above then the following is some Activity code that inserts a number of rows:-

    class MainActivity : AppCompatActivity() {
    lateinit var dbHelper: DBHelper
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        dbHelper=DBHelper.getInstance(this)

        val date1 = LocalDate.parse("2023-01-01")
        val date2 = LocalDate.parse("2023-03-01")
        val date3 = LocalDate.parse("2023-04-04")
        dbHelper.insertReservation(date1)
        dbHelper.insertReservation(100,date2)
        dbHelper.insertReservation(date3)
        dbHelper.insertReservation(111, LocalDate.parse("2023-05-05"))
        dbHelper.updateReservationDate(2, LocalDate.parse("2020-03-01"))

    }
}

    After running the above then using App Inspection the resultant data is:-

    enter image description here

    • note how the rows with id's 1 and 101 have been generated with AUTOINCREMENT. AUTOINCREMENT is inefficient as per "The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed."

    So you may question the validity of the numbers so using App Inspection to run the Query:- SELECT date(date_reservation * 24 * 60 * 60,'unixepoch') AS formattedDate,* FROM reservation then the result confirms the expected dates:-

    enter image description here

    When it comes to retrieving the date, then there are numerous options. You can use SQLite's date and time functions (see https://www.sqlite.org/lang_datefunc.html) or you can retrieve the stored value (which can be stored many ways).

    Following on from the above, as an example. Consider this function (added to the DBHelper class) and also the const (included here immediately before the function):-

    const val DATEONLY_TO_YYYYMMDD = 24 * 60 * 60
private val dateInYYYYMMDDFormat = "date(${COLUMN_RESERVATION_DATE} * ${DATEONLY_TO_YYYYMMDD},'unixepoch')"
fun getReservations(reservationId: Long?= null,startAndEndDateAsList: List<LocalDate>?=null): Cursor {
    val whereClause = StringBuilder()
    val whereArgs = arrayListOf<String>()
    if (reservationId != null) {
        whereClause.append("${COLUMN_RESERVATION_ID}=?")
        whereArgs.add(reservationId.toString())
    }
    if (startAndEndDateAsList !=null && startAndEndDateAsList.size ==2){
        if (whereClause.isNotEmpty()) whereClause.append(" AND ")
        whereClause.append("${COLUMN_RESERVATION_DATE} BETWEEN ? AND ?")
        var firstIdx=0
        var secondIdx = 1
        if (startAndEndDateAsList[0] > startAndEndDateAsList[1]) {
            firstIdx=1
            secondIdx=0
        }
        whereArgs.add(startAndEndDateAsList[firstIdx].toEpochDay().toString())
        whereArgs.add(startAndEndDateAsList[secondIdx].toEpochDay().toString())
    }
    return this.writableDatabase.query(TABLE_RESERVATION,
        arrayOf("*","${dateInYYYYMMDDFormat} AS formattedDate"),
        whereClause.toString(),
        whereArgs.toList().toTypedArray(),
        null,null,null
    )
}

    First the Cursor object is the output from queries using the SQLiteDatabase. A Cursor will have 0-nnnnnn rows. You can navigate the cursor using the move??? functions (e.g. moveToFirst, moveToNext ....). You use the get??? functions to retrieve the data from a column.

    In the above the output columns are ALL columns (*) (actually more correctly all unhidden columns) and also a derived column named(aliased) formattedDate.

    • this used to demonstrate, the stored value and a value formated using the SQLite date function (see above).

    The function is quite flexible allowing ALL rows to be extracted, rows (a single row) according to value of the id column and/or according to a date range.

    • noting that the date range provided is accordingly adjusted to suit the BETWEEN clause (i.e. the first value must be the lowest value (if both are the same then the order does not matter)).

    In conjunction with the new function in the DBHelper class is the following additional code in the Activity (after the data has been inserted):-

    DatabaseUtils.dumpCursor(dbHelper.getReservations(null, listOf(LocalDate.parse("1900-01-01"),LocalDate.parse("2024-01-01"))))

    • dumpCursor is a utility that outputs the contents of the Cursor to the Log. The parameters, in this case, passed to the getReservations function specify:-

    • that any/all id/s will be extracted (i.e. null indicates that the WHERE clause does not test test the id column)

    • that the BETWEEN clause will be included.

    The output (after uninstalling the App and rerunning) being:-

    2023-03-09 12:34:30.531 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@c64b406
2023-03-09 12:34:30.532 I/System.out: 0 {
2023-03-09 12:34:30.532 I/System.out:    id=1
2023-03-09 12:34:30.532 I/System.out:    date_reservation=19358
2023-03-09 12:34:30.532 I/System.out:    formattedDate=2023-01-01
2023-03-09 12:34:30.532 I/System.out: }
2023-03-09 12:34:30.532 I/System.out: 1 {
2023-03-09 12:34:30.532 I/System.out:    id=100
2023-03-09 12:34:30.532 I/System.out:    date_reservation=19417
2023-03-09 12:34:30.532 I/System.out:    formattedDate=2023-03-01
2023-03-09 12:34:30.532 I/System.out: }
2023-03-09 12:34:30.532 I/System.out: 2 {
2023-03-09 12:34:30.532 I/System.out:    id=101
2023-03-09 12:34:30.532 I/System.out:    date_reservation=19451
2023-03-09 12:34:30.532 I/System.out:    formattedDate=2023-04-04
2023-03-09 12:34:30.532 I/System.out: }
2023-03-09 12:34:30.533 I/System.out: 3 {
2023-03-09 12:34:30.533 I/System.out:    id=111
2023-03-09 12:34:30.533 I/System.out:    date_reservation=19482
2023-03-09 12:34:30.533 I/System.out:    formattedDate=2023-05-05
2023-03-09 12:34:30.533 I/System.out: }
2023-03-09 12:34:30.533 I/System.out: <<<<<
    • as can be seen the stored value and the derived formatted date are as expected.
      • one thing is important to note/remember/respect is the previously linked date and time functions documentation e.g. the modifier unixepoch has a role to play in the above, if not used when dealing with with above data then the results will not be as desired. However, the date and time processing capabilities of the functions are quite powerful/useful.

    Here's some example of other permutations of the parameters (that all return the expected results):-

        DatabaseUtils.dumpCursor(dbHelper.getReservations())
    DatabaseUtils.dumpCursor(dbHelper.getReservations( startAndEndDateAsList =  listOf( LocalDate.parse("2024-01-01"),LocalDate.parse("1900-01-01"))))
    DatabaseUtils.dumpCursor(dbHelper.getReservations(reservationId = 100))