Date Converter in Room Android

Question

I get date in this format from the server "2021-04-25T14:05:03". I wrote date type in my model and I want to add database date type. this is my model

@Entity(tableName = "order_table")
data class Order(
    val adress: String? = null,
    val clientName: String? = null,
    val clientUnicId: String? = null,
    val mobileNumber: String? = null,
    val note: String? = null,
    val orderDate: Date? = null,
    @PrimaryKey
    val orderId: Int,
    var status: Boolean? = null,
    val userId: Int? = null
)

In the examples I looked at, I couldn't see any converter example that would convert the date type. How should I write a converter to make my date type look like this in the database "2021-04-25 14:05:03"?

Answer 1

In my opinion, you should not save date type into the database. Although, it is supported.

When you get the date data from server side. You should receive it as a String. And in the case, you want to save it into local database, you also should save it as a String.

Why? Because it is easy to convert a String to many other type of format to display. For formatting the date, you can use SimpleFormatDate.

Document; https://developer.android.com/reference/java/text/SimpleDateFormat

How to use? Example

Android SimpleDateFormat, how to use it?

Answer 2

Storing dates in formats that are recognised by SQLite (see Date and Time Functions) can be quite beneficial.

1. They are directly sortable
2. They can be manipulated within queries (see example)
3. They can take up less storage and be more efficient

YYYY-MM-DDThh:mm:ss is such a format, although storage wise storing them as an integer (Long), which is recognised format can be marginally more efficient.

The T is an accepted separator (as per ISO 8601) between the date and time, there is no need to change it to a space (it would not hurt to change it to a space).

Example

Here's an example that stores the date in YYYY-MM-DDThh:mm:ss, and automatically converts the extracted date in the Order to a Date object.

After adding 3 rows, it extracts them, it then runs an update to add 7 days to all orders and extracts the modified data.

A version of the Order Entity (columns omitted for brevity) :-

@Entity
class Order {
    @PrimaryKey
    var orderId: Long? = 0L
    var date: String? = ""
    @Ignore
    var dateAsDate: Date? = null /* @Ignore so this is not a column in the table */

    /* Constructor that converts the date to a Date */
    constructor(orderId: Long?, date: String) {
        this.orderId = orderId
        this.date = date
        this.dateAsDate = SimpleDateFormat(DATE_FORMAT_IN_DATABASE).parse(date)
    }
    companion object {
        const val DATE_FORMAT_IN_DATABASE = "yyyy-MM-dd'T'HH:mm:ss"
        const val SQLITE_STRFTIME_FORMAT = "%Y-%m-%dT%H:%M:%S"
    }
}

Dao :-

@Dao
interface Dao {
    @Insert
    fun insert(order: Order): Long
    @Query("SELECT * FROM `order`")
    fun getAllOrders(): List<Order>
    @Query("UPDATE `order` SET date = strftime('${Order.SQLITE_STRFTIME_FORMAT}',date,'+7 days')")
    fun addAWeekToAllOrderDates()
    @Query("DELETE FROM `order`")
    fun deleteAllOrders()
}

• see Link above regarding the SQLite strftime function

The @Database is nothing special so omitted for brevity

An activity putting it all together that:-

1. gets an instance of the database
2. gets the Dao from the database
3. deletes any existing rows
4. adds 3 rows
5. extracts the 3 rows and prints the extracted date as per the database and the date as per the Date's toString method.
6. updates all the rows by adding 7 days using SQLite's strftime function.
7. extracts the 3 updated rows and prints the extracted date as per the database and the date as per the Date's toString method

:-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: Dao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)!!
        dao = db.getDao()
        dao.deleteAllOrders()

        dao.insert(Order(null,"2021-01-01T12:15:00"))
        dao.insert(Order(null,"2021-02-01T12:15:00"))
        dao.insert(Order(null,"2021-03-01T12:15:00"))
        for(o: Order in dao.getAllOrders()) {
            Log.d("ORDERINFO","Extracted date is ${o.date} from Date is ${o.dateAsDate.toString()}")
        }
        dao.addAWeekToAllOrderDates()
        for(o: Order in dao.getAllOrders()) {
            Log.d("ORDERINFO","Extracted date is ${o.date} from Date is ${o.dateAsDate.toString()}")
        }
    }
}

Result (as output to the log) :-

2021-06-15 19:47:30.065 D/ORDERINFO: Extracted date is 2021-01-01T12:15:00 from Date is Fri Jan 01 12:15:00 GMT+11:00 2021
2021-06-15 19:47:30.066 D/ORDERINFO: Extracted date is 2021-02-01T12:15:00 from Date is Mon Feb 01 12:15:00 GMT+11:00 2021
2021-06-15 19:47:30.066 D/ORDERINFO: Extracted date is 2021-03-01T12:15:00 from Date is Mon Mar 01 12:15:00 GMT+11:00 2021


2021-06-15 19:47:30.073 D/ORDERINFO: Extracted date is 2021-01-08T12:15:00 from Date is Fri Jan 08 12:15:00 GMT+11:00 2021
2021-06-15 19:47:30.074 D/ORDERINFO: Extracted date is 2021-02-08T12:15:00 from Date is Mon Feb 08 12:15:00 GMT+11:00 2021
2021-06-15 19:47:30.074 D/ORDERINFO: Extracted date is 2021-03-08T12:15:00 from Date is Mon Mar 08 12:15:00 GMT+11:00 2021