[threeten-develop] Requesting advice regarding database persistence

[Rafael de F. F. <pu...@ra...>]

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, for
      instance)
      - 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
      - Con: 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 hard-to-diagnose bugs
      - 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 :)

Has 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.
--
Rafael de F. Ferreira.
http://www.rafaelferreira.net/