Is there some way to store EITHER a specific day (date) OR a year in the same column?

Question

I have a column called "date or year of birth". The reason it's not called "date of birth" is that, in some cases, the exact day is not known -- only the year, or only the year and month.

If I try to store "1998" in a date column, I get an error; PostgreSQL doesn't let me store it.

I would have to turn it into "1998-01-01" for it to be allowed to be stored, but now it's probably lying (unless they happen to be born on 1st of January 1998).

Simply having only the year is not the same as 1st of January 1998. This way, I cannot ever be sure if birth dates on that day are "real" or just another case of "we only know the year".

If I turn this column into a text, it will store both "1998" and "1995-04-06", but this makes it a PITA to deal with whenever I need to compare it time-wise. Furthermore, it "feels wrong" to store such information in a "text" column; I only use those as a last resort when PG simply doesn't have "native knowledge" of the kind of information contained within.

What can I do about this?

Answer 1

I would model this thusly:

CREATE TABLE ... (
   birthday date NOT NULL,
   only_year boolean DEFAULT FALSE NOT NULL,
   ...
);

The second column indicates if only the year of the data is relevant or not.

Answer 2

Recently I stumbled on the same problem to solve (which, previously, I solved differently than I am going to describe here), but answers from Laurnez Albe and Lennart inspired me to this, one column, solution.

The main idea is to use timestamp column to hold the date and use time fields as precision markers. Further, to prevent some unwanted values in time fields, it is recommended to create domain for it. (Domain name could be better, thou):

CREATE DOMAIN xdate AS timestamp
    CHECK (extract(HOUR FROM VALUE) IN (0, 1))
    CHECK (extract(MINUTE FROM VALUE) IN (0, 1))
;

Meaning: use date fields normally, but allow only zero or one both in hour and minute fields (probably we could also check if seconds and millis are zeros too). So we can store values like 2022-06-16 00:00:00 or 2022-06-16 00:01:00, but not eg. 2022-06-16 11:55:00.

Now: treat ones as disabling flags. Hour disables month and second disables day. So eg. 1850-01-01 01:01:00 would mean "year 1850", next 1850-01-01 00:01:00 would mean "January of 1850" and finally 1850-01-01 00:00:00 would mean "1st January of 1850".

With this approach, if you sort the table by this column, "more precise" dates (i.e. with enabled year, month and day) would appear higher and less precise lower (if month and day happen to both 1 for given dates). Besides you can safely insert into this column CURRENT_DATE or any timestamp cast to date.

You could also create few simple function if needed:

CREATE OR REPLACE FUNCTION make_xdate(IN year int)
  RETURNS xdate
  LANGUAGE SQL
  RETURNS NULL ON NULL INPUT
AS $$ SELECT make_timestamp(year, 1, 1, 1, 1, 0)::xdate; $$;

CREATE OR REPLACE FUNCTION make_xdate(IN year int, IN month int)
  RETURNS xdate
  LANGUAGE SQL
  RETURNS NULL ON NULL INPUT
AS $$ SELECT make_timestamp(year, month, 1, 0, 1, 0)::xdate; $$;

CREATE OR REPLACE FUNCTION make_xdate(IN year int, IN month int, IN day int)
  RETURNS xdate
  LANGUAGE SQL
  RETURNS NULL ON NULL INPUT
AS $$ SELECT make_timestamp(year, month, day, 0, 0, 0)::xdate; $$;

Please also note, that the checks in the above defined domain is not perfect (it allows "illegal time sequence", eg. 01:00:00 and allows non-zeroed seconds or millis), but it's more to demonstrate the idea than to write 100% fool-proof code (it can be easily fixed, thou).

And if you have better name for the domain than ad hoc made up xdate, then let me know! :-)