Room + Date / Time

If you’ve started using Room (and you should if you haven’t), there’s a high probability that you will need to store + retrieve some kind of date/time. Room does not provide any support for that outof the box, instead it provides the extensible TypeConverter annotation, which allows you to provide mappings from arbitrary objects to types Room understands, and vice-versa.

The canonical example in the docsfor that API is in fact for date/time:

public class Converters {
    @TypeConverter
    public static Date fromTimestamp(Long value) {
        return value == null ? null : new Date(value);
    }

    @TypeConverter
    public static Long dateToTimestamp(Date date) {
        return date == null ? null : date.getTime();
    }
}

I used this exact code in my app, and while it technically works, it has two big issues. The first is that it uses the Date class, which should be avoided in nearly all instances. The main issue with Date is the fact that it does not support timezones. At all.

The second issue is that it persists the value as a simple Long, which again can’t store any timezone information.

So let’s say we use the above converters to persist a Date instance to the database, and then later retrieve it. How do you know what timezone the original value is from? The simple answer is that you can’t know. The best you can do is to try and make sure that all Date instances use a common timezone such as UTC. While this allows you to compare different retrieved values against each other (i.e. for sorting), you can never find out the original time zone.

I decided to spend an hour attempting to fix the timezone issue in my app.

SQLite + date/time

#

The first thing I investigated was SQLite’s support for date and time values, and indeed it does support them. As you’re using Room, it controls which SQL data types your class values map to. For instance String will map to TEXT, Int to INTEGER, etc. But how do we tell Room to map our object date + time values? Well the simple answer is that we don’t need to.

SQLite is a loosely typed database system and stores all values as one of: NULL, INTEGER, TEXT, REAL or BLOB. You’ll notice that there is no special date or time type like you may find in other database systems. Instead they provides the following documentation on how to store date/time values:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values

It is these date and time functions which will allow us to store high-fidelity date-time values with minimal/no accuracy loss, specifically using the TEXTtype since that support ISO 8601strings.

Thus we just need to save our values as specially formatted text which contains all of the information we need. We can then use the mentioned SQLite functions to convert our text to a date/time in SQL if needed. The only thing we need to do is make sure our code is using the correct format.

Back to the app

#

So we know that SQLite supports what we need, but we need to decide how we’re going to represent this in our app.

I’m using ThreeTen-BP in my app, which is a backport of the JDK 8 date and time library (JSR-310) but works on JDK 6+. This library supports timezones, so we’re going to use one of its classes to represent date + times in the app: OffsetDateTime. This class is an immutable representation of both a time and date within a specific offset from UTC/GMT.

So when we look at one of my entities, we now use OffsetDateTime instead of Date:

@Entity(tableName = "users")
data class User(
    @PrimaryKey val id: Long? = null,
    val username: String,
    val joined_date: OffsetDateTime? = null
)

That’s the entities updated, but now we have to update our TypeConverters so that Room understands how to persist/restore the OffsetDateTime values:

object TiviTypeConverters {
    private val formatter = DateTimeFormatter.ISO_OFFSET_DATE_TIME

    @TypeConverter
    @JvmStatic
    fun toOffsetDateTime(value: String?): OffsetDateTime? {
        return value?.let {
            return formatter.parse(value, OffsetDateTime::from)
        }
    }

    @TypeConverter
    @JvmStatic
    fun fromOffsetDateTime(date: OffsetDateTime?): String? {
        return date?.format(formatter)
    }
}

Instead of our previous mapping of Date to/from Long, we’re now mapping OffsetDateTime to/from String.

The methods are pretty simple to look at: one formats a OffsetDateTime to a String, and the other parses a String into an OffsetDateTime. The key puzzle here is making sure that we use the correct String format. Thankfully ThreeTen-BP provides a compatible one for us as DateTimeFormatter. **ISO_OFFSET_DATE_TIME** .

You might not be using this library though so lets take a look at an example formatted string: 2013-10-07T17:23:19.540-04:00. Hopefully you can see what date this represents: 7th October 2013, 17:23:19.540 UTC-4. As long as you format/parse to a string like that, SQLite will be able to understand it.

So at this point, we’re nearly done. If you run the app, with an appropriate database version increase + migration, you’ll see that everything should be working nicely.

For more information on migrations with Room, see Florina Muntenescu’s post:

Sorting the Room out

#

The one thing we haven’t yet fixed is querying on date columns in SQL. The previous Date/Long mapping had an implicit benefit in that numbers are extremely efficient to sort and query. Moving to a String somewhat breaks that though, so let’s fix it.

Say we previously had a query which return all users ordered by their join date. You would probably have had something like this:

@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY joined_date")
    fun getOldUsers(): List<User>
}

Since joined_date was a number (long, remember), SQLite would do a simple number comparison and return the results. If you run the same query with the new text implementation, you’ll probably notice that the results look the same, but are they?

Well the answer is yes, most of the time. With the text implementation, SQLite is doing a text sort rather than a number sort, which for the majority of cases will be correct. Lets look at some example data:

A simple left-to-right String sort works here, since all of the components of the string are in descending order (year, then month, then day, and so on). The issues comes with the last component of the string, the timezone offset. Lets tweak the data slightly and see what happens:

You can see that the timezone for the 3rd row has changed from UTC to UTC-2. This results in its joined time actually being 09:01:12 in UTC, thus it should actually be sorted as the 2nd row. The returned list contained the same order as before though. This is because we’re still using string ordering, which does not take the timezone into account.

SQLite date time functions

#

So how do we fix it? Remember those SQLite date/time functions? We just need to make sure we use them when interacting with any date/time columns in SQL. There are 5 functions which SQLite provides:

1. date(...) returns just the date.
2. time(...) returns just the time.
3. datetime(...) returns both the date and time.
4. julianday(...) returns the Julian Day.
5. strftime(...) returns a value formatted with your given format string. The first four can be thought of as variations of strftime with a pre-defined format.

Since we want to sort on both the date and time, we can use the datetime(...)function. If we go back to our DAO, the query now becomes:

@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY datetime(joined_date)")
    fun getOldUsers(): List<User>
}

Easy enough right? After we’ve made this change we now get the correct semantic ordering:

And that was my hour* of work complete! We now support timezoned date/times in Room.

N.B. I’ve used ThreeTenBP in this post, but this works with any time/date API which supports timezones, such as ICU Calendar (on API 24+), Joda-Time, or even the Calendar class. I’ll leave it as an exercise to the reader to work out the TypeConverter implementations for the API they wish to use.

Cover photo: Big Ben by James Cullen