Are there guidelines established for persisting threeten
classes to databases? For the purposes of this question we can separate
date and time classes in three categories:
- Timestamps: only the Instant class, representing a point in a timeline
Unanchored: recurring time specifications such as MonthDay and LocalTime
Achored: represent some extent of time in a human timeline. They are
tuples of date and time fields with varied degrees of resolution. Eg:
LocalDate, LocalDateTime and YearMonth.
I'm supposing a database with support for storing the usual
primitives and timestamps. The latter are represented as
java.util.Dates in the API.
For Instants seems a no brainer to map from and to j.u.Date s.
So far I haven't needed to map Unanchored classes, so I haven't put much thought.
For Anchored classes, I can see several approaches:
- Store them as Strings
- Pro: Simple bidirectional conversion
- Con: No way to do comparative queries (before/after/between)
- Con: No reasonable way to query for parts of an instance (i.e. all local dates with year 1970)
- Store each temporal field as a separate db attribute/column
- Pro: Easy to query for parts of an instance
- Con: Hard to write comparative queries, each query needs to enforce lexicographical tuple ordering.
- Con: Polluted db schema
- Neutral: Possible to write comparative queries among different
resolution anchored classes (get all LocalDates between two YearMonths,
- Neutral: Relatively simple bidirectional conversion
- Transform the Anchored class to a j.u.Date
- Pro: Easy to convert using 310 methods
- Pro: Easy to write comparative queries
- Pro: Possible to write comparative queries among different resolution anchored classes
Exposure to conversion bugs, not getting back what is put in (we
encontered this with a naive j.u.Date->YearMonth conversion)
- Con: requires understanding the flawed j.u.Date APIs:
interaction with TZ, DST, etc. The lack of which risks incurring
- Neutral: Relatively easy to write queries for parts of an instance (as comparative queries)
- Map the temporal fields to portions of a Long
- Pro: Once conversions are done, easy to write comparative queries
- Pro: Easy to write queries for parts of an instance
- Pro: Easy to write comparatice queries among different resolution anchored classes
- Con: Non-trivial conversion code
- Con: Hard to interpret stored data without running conversion code.
Now with the questions :)
anyone else explored this space? Am I missing something in this
characterization? What option are you guys usually leaning towards?
Thanks and sorry for the explosion of bullet points.