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)

Answer 2

SQLite may not have a DATE storage/column type. However SQLite does have relatively flexible DATE FUNCTIONS, which work quite effectively on a number of storage/column types.

I'd suggest reading SQL As Understood By SQLite - Date And Time Functions

For example, this column definition defines a column (MYCOLUMN) that will hold the datetime (to seconds not milliseconds) of when the row was inserted.

"CREATE TABLE.......  mycolumn INTEGER DEFAULT (strftime('%s','now')), ......"

You may also want to have a read of Datatypes In SQLite Version 3 which explains the felxibity of data types. e.g. Section 3 Type Affinity asserts:

Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

e.g.

"CREATE TABLE.......  mycolumn BLOB DEFAULT (strftime('%s','now')), ......"

works fine. The following is output, using the Cursor methods getString, getLong and getDouble, for a row inserted using the above :-

For Column otherblob Type is STRING value as String is 1507757213 value as long is 1507757213 value as double is 1.507757213E9 

Personally, I'd suggest, due to milliseconds, using a type of INTEGER and storing UTC and using the cursor getLong to retrieve from the cursor.

Answer 3

UTC

Generally, the best practice for storing or exchanging date-time values is to adjust them into UTC.

Programmers and sysadmins should learn to think in UTC rather than their own parochial time zone. Consider UTC as The One True Time, with all other offsets and zones being mere variations on that theme.

Adjust your OffsetDateTime into UTC.

OffsetDateTime odtUtc = myOdt.withOffsetSameInstant( ZoneOffset.UTC ) ;

odtUtc.toString(): 2007-12-03T10:15:30.783Z

Simplest approach is to convert to Instant, which is always in UTC.

Instant instant = myOdt.toInstant() ;
String instantText = instant.toString() ;  // Generate text in standard ISO 8601 format.
…
myPreparedStatement.setString( instantText ) ;

ISO 8601

Given the limitations of SQLite, I suggest storing the values as text, in a format that when sorted alphabetically is also chronological.

You need not invent such a format. The ISO 8601 standard has already defined such formats. The standard formats are sensible, practical, easy to parse by machine, and easy to read by humans across various cultures.

Conveniently, the java.time classes use ISO 8601 formats by default when parsing or generating strings.

If your JDBC driver complies with JDBC 4.2 or later, you can exchange the java.time objects directly to the database via setObject and getObject.

myPreparedStatement.setObject( … , odtUtc ) ;

Resolution

You mentioned milliseconds. Know that java.time classes use a resolution of nanoseconds, for up to nine decimal digits of fractional second.

Calculating days

simple arithmetic like "how many days passed since some event".

Actually that is not so simple. Do you mean calendar days? If so, specify a time zone to determine the dates. Or do you mean periods of 24-hours?

// Count of generic 24-hour days
String fromDatabase = myResultSet.getString( … ) ;
Instant instant = Instant.parse( fromDatabase ) ;
Instant now = Instant.now() ; 
long days = ChronoUnit.DAYS.between( instant , now ) ;

// Count of calendar days.
ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;
ZonedDateTime zdtNow = now.atZone( z ) ;
long days = ChronoUnit.DAYS.between( zdt.toLocalDate() , zdtNow.toLocalDate() ).

Half-Open

Do not use BETWEEN predicate for date-time range searching. Generally the best practice is to use the Half-Open approach where the beginning is inclusive and the ending is exclusive. In contrast, BETWEEN is inclusive on both ends, also known as "fully closed".

Consider another database

Your database needs may be exceeding the limited purpose of SQLite. Consider moving up to a serious database engine if you need features such as extensive date-time handling or high performance.

Richard Hipp, the creator of SQLite, intended that product to be an alternative to plain text files, not be a competitor in the database arena. See What I learned about SQLite…at a PostgreSQL conference.

H2 Database

For Android, H2 Database is another possibility. For more info, see:

• Tutorial
• H2 Database vs SQLite on Android

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
  • See How to use ThreeTenABP….