Single data type for imprecise date values, as allowed by ISO 8601

How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?

ISO 8601 allows date values with reduced precision. ‘1964’, ‘1964-05’, ‘1964-05-02’ are all valid representations of a value, in increasing precision. The Python ‘datetime’ types also allow values with reduced precision in this way.

PostgreSQL native time types doesn't allow reduced precision

In the native date type, every element of a date must be present or the value is rejected. Setting the elements below the desired precision level to ‘00’ also fails.

=> SELECT CAST('1964-05-02' AS DATE);
    date    
------------
 1964-05-02
(1 row)

=> SELECT CAST('1964-05' AS DATE);
ERROR:  invalid input syntax for type date: "1964-05"
LINE 1: SELECT CAST('1964-05' AS DATE);
                    ^
=> SELECT CAST('1964' AS DATE);
ERROR:  invalid input syntax for type date: "1964"
LINE 1: SELECT CAST('1964' AS DATE);
                    ^
=> SELECT CAST('1964-00-00' AS DATE);
ERROR:  date/time field value out of range: "1964-00-00"
LINE 1: SELECT CAST('1964-00-00' AS DATE);
                    ^
HINT:  Perhaps you need a different "datestyle" setting.

Expected behaviour for a reduced-precision date and/or time type

Is there a simple, standard way to support entry of ISO 8601 date values with reduced precision into a PostgreSQL date and/or time type?

Creating a type for this is possible, but I don't know how. Of course, I need the values to be range checked and deal with timezones and compare sensibly with other time values all the other useful things the built-in types do.

What I expect is that, just as the value ‘1964-05-02’ refers to the entire interval between 00:00:00 on that day until 00:00:00 the next day, a reduced-precision value would simply represent a larger interval: ‘1962-05’ refers to the entire interval between 00:00:00 at the beginning of May 1962 until 00:00:00 on the first day of June of 1962.

An example of what I'd

I have used CHAR and VARCHAR in the past, replacing the missing pieces with question marks or dashes. Question marks mean "not known", and dashes meant "not applicable". This proved to be intuitive enough for the users (secretaries and paralegals in complex litigation), flexible enough for the lawyers, and it sorted sensibly.

"1964------"
"1964-??-??"
"1964-05---"
"1964-05-??"
"1964-05-02"
"1964-06---"
"1964-06-??"

Wrap your declaration and CHECK constraints in CREATE DOMAIN or CREATE TYPE to make maintenance easier. CREATE DOMAIN doesn't require additional coding. CREATE TYPE requires support functions written in a low-level language.

StackExchange Database Administrators Q#6042, answer score: 6