ISO-8601 in Postgres: how to insert only a year into a type date? (incomplete date and time values)

Question

The Postgres database claims to support the ISO-8601 standard . In ISO-8601, the date format is "yyyy", i.e. Consisting of only a year, is beautiful and acceptable. But I cannot find a way to add only a year to the Postgres database field of type "date". Any idea if I am doing something wrong or is this feature missing in Postgres?

I have seen other posts suggesting setting the date to “yyyy-01-01,” but that’s not what I want and need (as it marks a specific day of the month of the year).

Scenario

The scenario is as follows. We collect information about people. For many, we have exact dates. But some have no dates, or only years, or a year and a month, but no day. We should be able to find people born before the year or some other year. This is easy if you have a full date. I was hoping Postgres would implement a function that would handle cases of incomplete dates.

Answer 1

To get the year for the date data type:

 select extract(year from '2014-01-01'::date) as the_year; the_year ---------- 2014 

If you only need a year, use the small push with a control limit

 create table t ( the_year smallint check( the_year between 0 and extract(year from current_date) ) ); insert into t (the_year) values (2030); ERROR: new row for relation "t" violates check constraint "t_the_year_check" DETAIL: Failing row contains (2030). insert into t (the_year) values (2014); INSERT 0 1 

But if you save the whole date, then it makes no sense to divide into 3 columns.

Note that column semantics are application dependent. If the column is of type date , but the application takes into account only the year, then this column means year.

Mark Functions and Date and Time Operators

---

One solution to the partial date problem noted by @a_horse in the comments is to create a column indicating the accuracy of that date

 create table t ( the_date date, the_date_precision varchar(5) ); insert into t (the_date, the_date_precision) values (current_date, 'year'), (current_date, 'month'), (current_date, 'day') ; select case the_date_precision when 'year' then to_char(the_date, 'YYYY') when 'month' then to_char(the_date, 'YYYY-MM') else to_char(the_date, 'YYYY-MM-DD') end as the_date from t ; the_date ------------ 2014 2014-02 2014-02-06 

The above is KISS approach, but I think the next implementation is more elegant.

 create table t ( the_date date, the_date_precision smallint ); insert into t (the_date, the_date_precision) values (current_date, 1), (current_date, 2), (current_date, 3) ; select array_to_string( ( string_to_array(to_char(the_date, 'YYYY-MM-DD'), '-') )[1:the_date_precision] , '-' ) as the_date from t ; the_date ------------ 2014 2014-02 2014-02-06 

This expression of choice can be turned into a function that is easier to reproduce. Or just view

 create view view_t as select *, array_to_string( ( string_to_array(to_char(the_date, 'YYYY-MM-DD'), '-') )[1:the_date_precision] , '-' ) as the_date_output from t ; select * from view_t; the_date | the_date_precision | the_date_output ------------+--------------------+----------------- 2014-02-06 | 1 | 2014 2014-02-06 | 2 | 2014-02 2014-02-06 | 3 | 2014-02-06