As I’ve been refactoring a fairly large section of Pigment, my coloring book app for adults, I came across some SQL code which I haven’t touched in a very long time and didn’t seem quite right.

Part of Pigment’s large library of coloring books and pages are the Daily books, which are a different book each month in which a new page is released each day. The day’s page is free only for the day (for non-subscribers), and then reverts to being locked once the next page is released. Users can unlock a page forever by simply starting a coloring project with it while it’s free, but there is a limited window in which it’s available.

This dynamic behavior of only displaying the currently released pages, and dynamically setting the premium flag, all happens on device. That way we only have to download the metadata for a month’s book once, and the user can still access the dailies even without a network connection. To accomplish this, we embed the release information into the books in our Room database.

The Existing Approach⌗

To support daily pages, each daily book is assigned a month, and the app will “release” the pages in order for each day that month. Here is a snippet of the data returned by Pigment’s API:

{
  "id": "dailies_september_2020",
  "title": "Dailies for September",
  "type": "dailies",
  "dailiesMonth": "2020-09-01T00:00:00.000Z",
  "pages": [
    {
      "id": "2020_09_daily_1",
      "free": false,
      ...
    },
    {
      "id": "2020_09_daily_2",
      "free": false,
      ...
    },
  ]
}

It’s well understood that, unlike some other database systems, SQLite doesn’t have a dedicated DateTime type as one of it’s five data types. That means that no matter which ORM library you use (Pigment started with SQLDelight then later switched to Room), you’ll need to decide how you’ll store dates in your database.

Becuase of that, when storing this data in my Room database, younger me chose to use a TypeConverter to store the Date object I was using in Kotlin as a long, representing the milliseconds since 1970 (standard Unix epoch time).

class Converters {

  @TypeConverter
  fun dateFromTimestamp(value: Long?): Date? = if (value == null) null else Date(value)

  @TypeConverter
  fun dateToTimestamp(date: Date?): Long? = date?.time
}

My initial thinking was that storing these date values as simple numbers was the easiest way to support things like sorting and comparison, but there are several problems with this approach.

No Time Zone Support⌗

My initial assumption was that all times could be treated as UTC times, so time zones weren’t relevant, but it turns out that was wrong. Pages get released at noon in the user’s local timezone. While the approach I took accomodates that since I store values in UTC time, it complicates things, and has led to bugs over the last couple of years, because I need to remember the timezones of each piece of data, and how to accurately convert between them.

Poor Query Support⌗

While storing dates as millis might make sense for use in the JVM, where datetime APIs use such things, they’re slightly more challenging in SQL. Based on this data structure, finding the current daily page isn’t trivial, as it requires joins and, in my initial implementation, filtering multiple results in application code.

Because of my initially limited experience in this regard, this meant that fetching daily books and pages invovled two parts. The first is a SQL query which simply got all daily books and pages.

SELECT
  books.*,
  pages.*
FROM books
JOIN pages_books ON pages_books.bookId = books.book_id
JOIN pages ON pages.page_id = pages_books.pageId
WHERE type = 'dailies'
ORDER BY
  books.dailiesMonth DESC,
  pages.sort

Once this list of all daily books and pages was fetch from the database, a somewhat complex kotlin function would filter that list to show only released pages, and also to set the current daily page to free.

private val dailyBooksFilter = { fullList: List<BookDao.BookWithPages> ->
  // Get the current "release time". New pages are released at noon in the local TZ.
  val now = calendarProvider.getCalendar()
  if (now.isBeforeNoon()) {
    now.add(Calendar.DATE, -1)
  }
  val dayOfMonth = now.get(Calendar.DAY_OF_MONTH)
  fullList
    // Filter out any books for a future month
    .filter { it.book.dailiesMonth?.after(now.time) == false }
    .map {
      val dailiesTime = Calendar.getInstance(TimeZone.getTimeZone("UTC")).apply {
        time = it.book.dailiesMonth!!
      }
      var pages = it.pages.reversed()

      // We only need to filter unreleased pages and set the free page
      //  for the current month. So we can make things "more efficient"
      //  by only filtering the current month's pages.
      if (
        dailiesTime.get(Calendar.YEAR) == now.get(Calendar.YEAR) &&
        dailiesTime.get(Calendar.MONTH) == now.get(Calendar.MONTH)
      ) {
        pages = pages
          // Remove any pages that shouldn't be released yet.
          .filter { it.sort < dayOfMonth }
          // Set today's page to free.
          .map { it.also { it.free = it.sort == dayOfMonth - 1 } }
      }
      BookDao.BookWithPages(it.book, pages)
    }
}

On review it’s clear that this is a rather complex piece of code, but is also easily unit testable, so I only felt a little bad about that. More importantly, however, this kind of filtering of datasets is exactly what SQL is designed to do! Whatever efficiency gains I thought I was getting by only filtering page lists for the current month instead of for every book could probably be even greater by pushing this filtering logic down into the query to begin with.

Enter SQLite datetime functions⌗

While SQLite may not have a native DateTime data type, it does have a set of datetime functions that make working with time pretty straightforward.

These functions understand ISO-8601 style date and time formats, allowing you to store dates and times as human readable strings that retain important information, like time zone offsets, and also retain sortability and comparability. They also understand unix epoch time, as seconds since 1970, so my dataset is usable without even needing to migrate any data.

Even better, you can use modifiers to perform calculations on the values, meaning this logic of figuring out a release date within a month based on the sort order of a page is perfectly possible!

It should be noted that another alternative to storing the data as it comes from the server and calculating the release date of a page based on it’s sort order on each query would be to simply calculate that value when inserting the data. Pigment, however, allows pages to be contained in multiple books, so a “release date” is relevant for a page when accessed from a daily book, but not otherwise, meaning that’s not an option in this case.

Filter Unreleased Pages in SQL⌗

In order to move the logic that filters out unreleased pages (based on the containing book’s dailiesMonth and the page’s sort order), the same query above can simply be altered to use SQLite’s datetime functions and modifiers to compare the generated release dates.

SELECT
  books.*,
  pages.*
FROM books
JOIN pages_books ON pages_books.bookId = books.book_id
JOIN pages ON pages.page_id = pages_books.pageId
WHERE type = 'dailies'
  AND 
    datetime(dailiesMonth / 1000, 'unixepoch', '12 hours', pages.sort || ' days')
    <=
    datetime(:releaseDateMillis / 1000, 'unixepoch')
ORDER BY
  dailiesMonth DESC,
  page_sort DESC

Since SQLite’s datetime functions support the unixepoch modifier, this query is able to run without migrating the data in the database. The function does, however, expect the value to be in seconds, not millis, so we have to divide by 1000.

From there we’re able to use modifiers to to adjust and calculate the page’s actual release date. Since the dailiesMonth field stores the start of the month, we simply add 12 hours, using 12 hours, to get to noon on the first day of the month, then use SQLite’s string concatenation (||) to add the appropriate number of days for the page, based on it’s sort order. This results in a datetime the represents noon on the day the page should be released.

Now that we have a datetime value, we can simply compare it to other datetime values, like the releaseDateMillis query parameter, just like we would compare numeric values.

Instead of passing in the releaseDateMillis parameter, we could simply use datetime('now') to fetch the current datetime. That, however, would make this code difficult to test, so I opted to pass the target release date in and have it default to now in my application code.

Current Page: Free for Today⌗

The query above returns only those daily pages that should currently be available in the app, but they’re all still only available to premium subscribers. For this feature to be useful the current daily page should be free for all users. This is also something that SQLite’s datetime comparison can help with.

While it’s easy to think of the Entity objects that the ORM returns as mapping directly to the data stored in your database, in the case of queries they actually represent the rows returned from the query. This means that we can overwrite the page.free property by simply returning different data from our query than is stored in the table.

To do this we’ll use a similar datetime comparison to the one we used to filter the data, but instead of ensuring the page’s calculated release date is before the target release date, we’ll check that the target release date lies between the page’s release date and that of the next page, one day later.

SELECT
  books.*,
  pages.page_id as page_page_id,
  pages.asset as page_asset,
  pages.hero as page_hero,
  pages.sort as page_sort,
  pages.thumb as page_thumb,
  pages.creation_date as page_creation_date,
  CASE
    WHEN datetime(:releaseDateMillis / 1000, 'unixepoch') BETWEEN
        datetime(dailiesMonth / 1000, 'unixepoch', '12 hours', sort || ' days')
      AND
        datetime(dailiesMonth / 1000, 'unixepoch', '12 hours', sort || ' days', '+1 days')
    THEN 1
    ELSE 0
  END as page_free
FROM books
JOIN pages_books ON pages_books.bookId = books.book_id
JOIN pages ON pages.page_id = pages_books.pageId
WHERE type = 'dailies'
  AND datetime(dailiesMonth / 1000, 'unixepoch', '12 hours', sort || ' days') <= datetime(:releaseDateMillis / 1000, 'unixepoch')
ORDER BY
  dailiesMonth DESC,
  page_sort DESC

Since we already have the correct datetime function to calculate a page’s release date, and pages are released once each day, simply adding the +1 days modifier make it easy to get the time of the next release, giving us both the lower and upper bounds of a page’s free window.

Conclusion⌗

With these changes in place, all of the filtering logic for Pigment’s daily pages is contained in SQL code, which is tuned and optimized for filtering large data sets, instead of spreading the query logic between the query code and the app code.

Additionally, we’re consistently handling times by using millis in our consuming API. With a simple data migration to convert the dailiesMonth to store TEXT instead of an INTEGER, we could store the values as a human readable string without losing any of the query functionality described above.

It should be noted that the code above still doesn’t handle time zones. This works because I ensure that all incoming data is properly converted in my app code. SQLite does have support for converting between UTC and localtime, using the utc and localtime modifiers, however, so that could also be migrated into the query, if needed.

So while it’s true that SQLite doesn’t have support for built-in datetime data types, it seems to me that we’re not missing much of their functionality, after all. Using these simple yet powerful functions can help you keep query logic from your app where it belongs.