ISO-8601 in Postgres: How to insert only year in type date? (incomplete date-time values)

Question

The Postgres database claims it supports the ISO-8601 standard. In ISO-8601 a date format "yyyy", i.e. consisting of only the year, is fine and acceptable. But I can't find a way to add only a year to a Postgres database field of the type "date". Any idea if I'm doing something wrong or is this feature missing in Postgres?

I've seen other posts advising to set the date to "yyyy-01-01" but that is not what I want and need (since it marks a specific day of a month of a year).

Scenario

The scenario is the following. We are collecting information on people. For many we do have exact dates. But some have no dates or only years, or year and month but no day. We do have to be able to find people born before some year, or after some other year. This is easy if you have a full date. I hoped there would be some feature implemented in Postgres that would handle cases of incomplete dates.

A "date format" is something different than a "date". yyyy simply isn't a date, it's just a year and as such it can not be stored in a date column. If you really need this, you need to store this e.g. in a varchar column. Another alternative would be to store year, month, day in three different (nullable) integer columns — a_horse_with_no_name
Agree with horsey. You should really consider what you want to do with this value -- do you need to validate that it is in a range, do you need to add to or subtract from it, do you need to derive dates from it (eg. "add 200 days to this year value") that sort of thing. — David Aldridge
hmm. well, i guess i'll split the dates in year, month, and day myself and store them in different fields (as you also suggest). this way i can at least (quite) easily find dates before a certain year or something like that. thanks! — user2080255
@user2080255 Don't do that. Use the date data type as you will be able to do all sorts of date arithmetic in a much easier way than with separate fields. There is also the benefit of the automatic constraint — Clodoaldo Neto
@ClodoaldoNeto: my understanding of the requirement is, that the some rows will only contain a year, some rows will contain year/month and some rows will contain the full date. The typical "partial date" problem. This situation can not be expressed using a date column. I agree that if you always store a full date, a date column is the only way to go. Unfortunately there is no nice solution for "partial dates" — a_horse_with_no_name

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2014-02-06T11:27:21

To get the year of a date data type:

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

If you only need the year then use a smallint with a check constraint

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 will store the whole date then it makes no sense to separate into 3 columns.

Note that the semantics of the column are up to the application. If a column is of the date type but the application only considers the year then that column means the year.

Check the Date/Time Functions and Operators

One solution to the partial date problem pointed by @a_horse in the comments is to create a column indicating the precision 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 the KISS aproach 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

That select expression could be turned into a function to be easier to reproduce. Or just a 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

ISO-8601 in Postgres: How to insert only year in type date? (incomplete date-time values)

Scenario

1 Answers