That looks quite nice, and I have no objections at all in adding such handy mapping annotations - however let me point out that I don't agree with the premise of "user friendlyness" of not storing nanoseconds.

Encoding such values at high resolution (not precision) at least it ensures that on reading a stored value, the same value is returned. Let's remember that time-zone dependent values are a mess as they depend on politics and systems being updated timely and consistently to be aware of whatever weird non-technical decisions politicians made; even assuming time-zone settings are correct from a technical point of view, it's quite possible that the JVM and the DB are at different stages or quality of patching in such regard (i.e. one might not have been updated to the latest oddness yet), and since storing at lower precision implies truncations based on such inconsistent rules, the "user friendliness" is arguable. Storing at highest resolution at least prevents some classes of such oddities.

Also, most users won't care what you store. So while I like being able to control the resolution I'd keep high resolution as a default.

but it's not very nice to open up the database and see a value with 11 zeros on it representing one measly hour

I'd argue that's an endless problem: when I'm expressing a duration in years, the number of days will likely be so large it will feel meaningless.

Or, hell, even as days would make sense, using something like numeric(22,14).

I very much doubt all durations expressed as a fraction of a day can be represented as a decimal number (with a finite number of decimal digits). You're bound to hit rational but non-decimal numbers at some point and then you might lose precision. That's the kind of limitation I'd hate to expose users to, especially just for the convenience of reading "understandable" number in the DB, and especially if the problem can be introduced by something so seemingly innocuous as picking one enum value over another.

For example take a duration representing a prime number of seconds, say 32212254719. In days (divided by 86400), that's 372827.0222106481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481481....

Rounding might work out fine in this case (I don't know), but then it might not in other cases.

enum TimeZoneStorageType { NANOSECOND, SECOND, DAY }.

I suppose you meant DurationStorageType? I don't see what Timezone has to do with this.

Alternatives?

I believe either alternative is preferable to @DurationStorage, be it only because the separation between "safe" storage and unsafe storage such as "days" is clearer.

So it seems to me that we all agree the following options would be useful:

enum DurationStorageType { 
 NANOSECOND, // store as numeric(21)
 SECOND, // store as numeric(21,9)
 INTERVAL_SECOND // store as interval second
}

I think that a truncating DAY option could also make sense, similar to how @Temporal(DATE) would truncate the time part away, but maybe we should rather name it DAY_TRUNCATE if we want that and store it as bigint?

Apart from that, the idea to have a storage type enum kind of would help mapping Period to other interval types (also see #4892)