Migration of a Date String to an Int in Android Room with Kotlin

Question

I need to migrate my database using Room 2.3 in Kotlin 1.4.32 on Android 9.0+. In my initial database my Date is a String (i.e. "2021-06-03T22:54:15.406-07:00[America/Los_Angeles]") and I want to Migrate it to and Int (i.e. toEpochSecond) during migration. It appears that I should be able to use a Cursor from SupportSQLiteDatabase.query to iterate through my data and do the conversion however when I do my Query my Cursor has a -1 count.

This is my migration:

val Migration_7_8 = object : Migration(7, 8) {
    override fun migrate(database: SupportSQLiteDatabase)
    {
        database.execSQL("ALTER TABLE logEntryTable ADD COLUMN dateTime INTEGER NOT NULL DEFAULT 0" )

        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryId TO id")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryAction TO 'action'")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryTime TO time")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLatitude TO latitude")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLongitude TO longitude")

        val cursor = database.query(SimpleSQLiteQuery("SELECT * FROM logEntryTable"))
        val idColumnIndex = cursor.getColumnIndex("id")
        val timeColumnIndex = cursor.getColumnIndex("time")

        while(!cursor.isAfterLast)
        {
            val timeStr = cursor.getString(3)
            val id = cursor.getInt(idColumnIndex)

            val time: ZonedDateTime = ZonedDateTime.parse(timeStr)

            val contentValues = ContentValues()
            contentValues.put("time", time.toEpochSecond())
            database.update("logEntryTable", SQLiteDatabase.CONFLICT_NONE, contentValues, "id=:id", arrayOf(id))

            cursor.moveToNext()
        }
    }
}

This is my Database before Migration:

If that is the case that the cursor initially points to no position (-1), then you can add `cursor.moveToNext()` before the while loop — Zain, Jun 04 '21 at 18:16
Yes, Thank you. I was experimenting and stumbled onto that. But WHY is it not already pointing to the first row. I see you commented on this 20 mins ago. I didn't see it till now. I'm a bit new to stackoverflow and forgot to look at the comment section and only just noticed the red number. — David, Jun 04 '21 at 18:41
@Zain - Please put your answer in the Answer section so that I can Credit your answer... — David, Jun 04 '21 at 18:47
how does this work? I want to do pretty much the opposite, change an epochMillis Int to an ISO-8601 String, and I don't understand how I would have to change your code to get the result I want. — AndroidKotlinNoob, Aug 16 '22 at 15:17

David · Answer 1 · 2021-06-04T18:44:32.750

So it turned out that (as also noted by Zain in the comments) the only problem was that I had to call cursor.moveToNext() apparently to initialize the cursor (i.e. to move to the FIRST row) BEFORE using it in my while-loop:

val Migration_7_8 = object : Migration(7, 8) {
    override fun migrate(database: SupportSQLiteDatabase)
    {
        database.execSQL("ALTER TABLE logEntryTable ADD COLUMN dateTime INTEGER NOT NULL DEFAULT 0" )

        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryId TO id")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryAction TO 'action'")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryTime TO time")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLatitude TO latitude")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLongitude TO longitude")

        val cursor = database.query(SimpleSQLiteQuery("SELECT * FROM logEntryTable"))
        val idColumnIndex = cursor.getColumnIndex("id")
        val timeColumnIndex = cursor.getColumnIndex("time")



        cursor.moveToNext()  // <======================  HERE



        while(!cursor.isAfterLast)
        {
            val timeStr = cursor.getString(3)
            val id = cursor.getInt(idColumnIndex)

            val time: ZonedDateTime = ZonedDateTime.parse(timeStr)

            val contentValues = ContentValues()
            contentValues.put("time", time.toEpochSecond())
            database.update("logEntryTable", SQLiteDatabase.CONFLICT_NONE, contentValues, "id=:id", arrayOf(id))

            cursor.moveToNext()
        }
    }
}

score 1 · Accepted Answer · answered Jun 04 '21 at 19:04

1

So, as cursor initially points to no position (-1), you need to move it to some valid position before using it

To move it to the first position you can call moveToNext() before the while loop

answered Jun 04 '21 at 19:04

Zain

37,492
7
60
84

Migration of a Date String to an Int in Android Room with Kotlin

2 Answers2