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

datatypespostgresqlsql-standardtimestamp

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 like to see:

=> SELECT CAST('1964-05-02 00:00' AS TIMESTAMP) = CAST('1964-05-02 00:00:00' AS TIMESTAMP);
 ?column? 
----------
 t
(1 row)

=> SELECT CAST('1964-05' AS TIMESTAMP) = CAST('1964-05-02' AS TIMESTAMP);
 ?column? 
----------
 t
(1 row)

Currently the former behaves as above; the latter complains about “invalid input syntax for type timestamp”. To my eye, they're both cases of reduced-precision values behaving sensibly when compared to finer-precision values.

The meaning of 1964-05 in ISO 8601 includes the more-precise values 1964-05-02 and 1964-05-02 18:27 and 1964-05-23. So those should all compare equal.

Best Answer

No, the interval type supports reduced precision but none of the other date/time types do.

Postgres allows you to roll your own with create type but unfortunately wont allow contraints to be added to the type which limits it's usefulness in this scenario. The best I can come up with requires you to repeat check constraints on every field where the fuzzy type is used:

create type preciseness as enum('day', 'month', 'year');
create type fuzzytimestamptz as (ts timestamptz, p preciseness);
create table t( id serial primary key,
                fuzzy fuzzytimestamptz
                    check( (fuzzy).ts is not null 
                           or ((fuzzy).ts is null and (fuzzy).p is not null) ),
                    check((fuzzy).ts=date_trunc('year', (fuzzy).ts) or (fuzzy).p<'year'),
                    check((fuzzy).ts=date_trunc('month', (fuzzy).ts) or (fuzzy).p<'month'),
                    check((fuzzy).ts=date_trunc('day', (fuzzy).ts) or (fuzzy).p<'day') );

insert into t(fuzzy) values (row(date_trunc('year', current_timestamp), 'year'));
insert into t(fuzzy) values (row(date_trunc('month', current_timestamp), 'month'));
insert into t(fuzzy) values (row(date_trunc('day', current_timestamp), 'day'));

select * from t;

 id |              fuzzy
----+----------------------------------
  1 | ("2011-01-01 00:00:00+00",year)
  2 | ("2011-09-01 00:00:00+01",month)
  3 | ("2011-09-23 00:00:00+01",day)

--edit - an example equality operator:

create function fuzzytimestamptz_equality(fuzzytimestamptz, fuzzytimestamptz)
                returns boolean language plpgsql immutable as $$
begin
  return ($1.ts, $1.ts+coalesce('1 '||$1.p, '0')::interval)
         overlaps ($2.ts, $2.ts+coalesce('1 '||$2.p, '0')::interval);
end;$$;
--
create operator = ( procedure=fuzzytimestamptz_equality, 
                    leftarg=fuzzytimestamptz, 
                    rightarg=fuzzytimestamptz );

sample query:

select *, fuzzy=row(statement_timestamp(), null)::fuzzytimestamptz as equals_now,
          fuzzy=row(statement_timestamp()+'1 day'::interval, null)::fuzzytimestamptz as equals_tomorrow,
          fuzzy=row(date_trunc('month', statement_timestamp()), 'month')::fuzzytimestamptz as equals_fuzzymonth,
          fuzzy=row(date_trunc('month', statement_timestamp()+'1 month'::interval), 'month')::fuzzytimestamptz as equals_fuzzynextmonth
from t;
 id |               fuzzy                | equals_now | equals_tomorrow | equals_fuzzymonth | equals_fuzzynextmonth
----+------------------------------------+------------+-----------------+-------------------+-----------------------
  1 | ("2011-01-01 00:00:00+00",year)    | t          | t               | t                 | t
  2 | ("2011-09-01 00:00:00+01",month)   | t          | t               | t                 | f
  3 | ("2011-09-24 00:00:00+01",day)     | t          | f               | t                 | f
  4 | ("2011-09-24 11:45:23.810589+01",) | f          | f               | t                 | f
Related Question