> On Sep 28, 2020, at 3:14 PM, Joe Abbate <jma@freedomcircle.com> wrote:
>
> Hello,
>
> I'm considering creating a TYPE for what may be called a "possibly imprecise date" (pidate). The most obvious use is
forrecording dates such as births or deaths of historical individuals, where we may know that someone died precisely on
agiven year-month-day, but the birth may only be known down to year-month or just the year (or perhaps we know
preciselythe baptism date [Adam Smith], but not the actual birth, so we want to record the former but qualified so it
canbe annotated on display). Another use is for publications, like magazines that are issued on a monthly basis or
journalsthat are issued on a quarterly or seasonal basis.
>
> We currently have two instances of this kind, using a standard DATE column plus a CHAR(1) column that encodes (on a
limitedbasis for now) the YMD, YM or Y level of precision, and a simple SQL function to return a textual representation
ofthe pidate. It would be nice to generalize this before going further.
>
> The first option I explored was creating a composite type with the two attributes, but that doesn't allow
specificationof DEFAULTs, NOT NULL or CHECK expressions on the precision code attribute. It seems I'd have to create a
DOMAINfirst, then use DATE and that domain to create a composite TYPE, to finally use the latter in actual tables.
Thatlayering looks cumbersome.
>
> Another option, which I havent't tried, is to subvert PG by creating an empty table, since that creates a "record
type",but even if possible that would be a hack.
>
> Finally there's the base TYPE. This entails writing some seven functions "in C or another low-level language" (does
PGsupport *any* other such language?), plus installing a library with those functions in a production environment.
Doable,yes, but not very friendly either.
>
> Am I overlooking something or is the practice of creating abstractions in object-relational databases mostly
unchanged?
>
> Regards,
>
> Joe
>
>
just record all three fields (day, month, year) with nulls and do the to-date as needed.