How to create a TypeConverter that converts LocalDate to format that Room can understand/save?

user_7152281 0
Viewed 1055

I am using Room. I need guidence on how to convert LocalDate from java.time to a format (may be Long, TimeStamp, sql.Date or I do not even know what else) so that I can save a date to database.

I have a book entity:

@Entity
data class Book(
    @ColumnInfo(name = "date") val date: LocalDate,  
    @ColumnInfo(name = "count") val count: Int,
    @PrimaryKey(autoGenerate = true)
    val id: Int? = null
)

I have also created Book DAO:

@Dao
interface BookDao {
    @Query("SELECT * FROM book WHERE :date >= book.date")
    fun getBooks(date: LocalDate): List<Book>
}

Now, I am not sure how to create converter that converts LocalDate to . . . (again, I do not even know to what I should convert my LocalDate. Is it Long, TimeStamp, sql.Date or anything else).

I thought I should be converting LocalDate to sql.Date so that Room can save it. So, I created my converter like this:

Converters.kt

class Converters {
    @TypeConverter
    fun convertLocalDateToSqlDate(localDate: LocalDate?): Date? {
        return localDate?.let { Date.valueOf(localDate.toString()) }
    }

    @TypeConverter
    fun convertSqlDateToLocalDate(sqlDate: Date?): LocalDate? {
        val defaultZoneId = systemDefault()
        val instant = sqlDate?.toInstant()
        return instant?.atZone(defaultZoneId)?.toLocalDate()
    }
}

But, I am getting error:

error: Cannot figure out how to save this field into database. You can consider adding a type converter for it.
    private final java.time.LocalDate date = null;
android-room java-time android
2 Answers

The issue you have is that a TypeConverter should convert from/to a set of specific types that Room supports for the insertion/extraction of Data.

Some of the more common ones are Int, Long Byte, Byte[], Float, Double, Decimal, String, BigInt, BigDecimal.

  • there may be other types but the types that can be used are limited

So the message is saying please convert your LocalDate or Date as it cannot handle them.

I'd suggest storing dates as unix dates (unless you want microsecond accuracy when you can store the time with milliseconds (this can be a little awkward when using date/time functions in sqlite and may need casts)) i.e. as a Long or Int.

In doing so you:

  • will minimise the amount of storage used to store the values,
  • will be able to take advantage of the SQLite Date and Time functions,
  • can easily extract them into very flexible formats,
  • you can extract them as is and then use class functions to format convert them,
  • you will not need TypeConverters for the above.

Here's an example showing some of the functionality, storing them as unix timestamps

First a version of your Book Entity utilising a default for the date :-

@Entity
data class Book(
    @ColumnInfo(name = "date", defaultValue = "(strftime('%s','now','localtime'))")
    val date: Long? = null,
    @ColumnInfo(name = "count")
    val count: Int,
    @PrimaryKey(autoGenerate = true)
    val id: Int? = null
)
  • see SQLITE Date and Time Functions for explanation of the above. In short the above allows you to insert a row and have the date and time set to the current date time
  • see the insertBook function for doing inserting with just the count provided.

To accompany the Book Entity the Book Dao :-

@Dao
interface BookDao {
    @Insert /* insert via a book object */
    fun insert(book: Book): Long
    @Query("INSERT INTO BOOK (count) VALUES(:count)")

    /* insert supplying just the count id and date will be generated */
    fun insertBook(count: Long): Long
    @Query("SELECT * FROM book")

    /* get all the books  as they are stored into a list of book objects*/
    fun getAllFromBook(): List<Book>

    /* get the dates as a list of dates in yyyy-mm-dd format using the SQLite date function */
    @Query("SELECT date(date, 'unixepoch', 'localtime') AS mydateAsDate FROM book")
    fun getDatesFromBook(): List<String>

    /* get the date + 7 days */
    @Query("SELECT date(date,'unixepoch','localtime','+7 days') FROM book")
    fun getDatesFromBook1WeekLater(): List<String>
}
  • see the comments (and activity and results)

The activity (pretty standard BookDatabase class so not included)

class MainActivity : AppCompatActivity() {
    lateinit var db: BookDatabase
    lateinit var dao: BookDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = BookDatabase.getInstance(this)
        dao = db.getBookDao()

        /* Insert two rows */
        dao.insert(Book(System.currentTimeMillis() / 1000,10)) /* don't want millisecs */
        dao.insertBook(20) /* use the date columns default value (similar to the above) */

        /* get and log the date objects */
        for(b: Book in dao.getAllFromBook()) {
            Log.d("BOOKINFO_BOOK", " Date = ${b.date} Count = ${b.count} ID = ${b.id}")
        }

        /* get the dates in yyy-mm-dd format */
        for(s: String in dao.getDatesFromBook()) {
            Log.d("BOOKINFO_DATE",s)
        }

        /* get the dates but with a week added on */
        for(s: String in dao.getDatesFromBook1WeekLater()) {
            Log.d("BOOKINFO_1WEEKLATER",s)
        }
    }
}

The Log after running :-

2021-04-16 14:28:50.225 D/BOOKINFO_BOOK:  Date = 1618547330 Count = 10 ID = 1
2021-04-16 14:28:50.225 D/BOOKINFO_BOOK:  Date = 1618583330 Count = 20 ID = 2
2021-04-16 14:28:50.226 D/BOOKINFO_DATE: 2021-04-16
2021-04-16 14:28:50.227 D/BOOKINFO_DATE: 2021-04-17
2021-04-16 14:28:50.228 D/BOOKINFO_1WEEKLATER: 2021-04-23
2021-04-16 14:28:50.228 D/BOOKINFO_1WEEKLATER: 2021-04-24
  • (note that the adjustment for local time (albeit it incorrectly used and thus jumping forward))
  • first 2 rows data as stored in the Book object and the database
  • next 2 rows the date converted to YYYY-MM-DD format by SQLite Date/Time function
  • last 2 rows the date a week after the stored date converted to YYYY-MM-DD

The Database as per Database Inspector :-

enter image description here

user_47445140

if you want to use converters, you can follow the documentation's way. In your example, you try to convert to SQLdate, but Android suggests to Long.

class Converters {
  @TypeConverter
  fun fromTimestamp(value: Long?): Date? {
    return value?.let { Date(it) }
  }

  @TypeConverter
  fun dateToTimestamp(date: Date?): Long? {
    return date?.time?.toLong()
  }
}

and then add the @TypeConverters annotation to the AppDatabase.

With LocalDate it should be something like this:

class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): LocalDate? {
        return value?.let { LocalDate.ofEpochDay(it) }
    }

    @TypeConverter
    fun dateToTimestamp(date: LocalDate?): Long? {
        val zoneId: ZoneId = ZoneId.systemDefault()
        return date?.atStartOfDay(zoneId)?.toEpochSecond()
    }
}

source

user_20480600
Related