Recommended way to store java.time.OffsetDateTime in SQLite Database

Question

I'm looking for the best way to store a java.time.OffsetDateTime in a SQLite database (on Android, if that helps).

The question arises since SQLite does not have a native data type for date+time.

The criteria are that I should be able to produce sensible results from:

1. ordering on the "date+time" column.
2. Not lose small time differences (milliseconds).
3. Be able to use the equivalent of BETWEEN, or range, in WHERE
4. Not lose time zone information (devices may be used globally and roam)
5. Hopefully retain some efficiency.

At the moment I'm storing the timestamp as an ISO formatted string. Not sure that is ideal either for efficiency or for comparisons.

Perhaps a conversion to UTC and then a long (Java) is an option, but I cannot find a function in OffsetDateTime that return time since epoch (as, for example, Instant.ofEpochMilli).

I should probably mention that the data is stored and used on an Android device. The application code uses the timestamp and performs simple arithmetic like "how many days passed since some event". So the data is being converted between the storage type and OffsetDateTime.

Answer 1

Store datetime as ISO 8601 string (e.g. 2017-06-11T17:21:05.370-02:00). SQLite has date and time functions, which operates on this kind of strings (time zone aware).

Class java.time.OffsetDateTime is available from API 26, so I would recommend for you to use ThreeTenABP library.

Below example:

private val formatter = DateTimeFormatter.ISO_OFFSET_DATE_TIME

val datetime = OffsetDateTime.now()

//###### to ISO 8601 string (with time zone) - store this in DB
val datetimeAsText = datetime.format(formatter) 

//###### from ISO 8601 string (with time zone) to OffsetDateTime
val datetime2 = formatter.parse(datetimeAsText, OffsetDateTime::from)

To make SQL queries time zone aware you need to use one of the date and time functions built in SQLite, for e.g. if you want to sort by date and time you would use datetime() function:

SELECT * FROM cars ORDER BY datetime(registration_date)