Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
In other words you want the equality operator to give true when the timestamps 'might' be equal (allowing for precision)? –  Jack Douglas Sep 24 '11 at 10:25
    
Not “might be”; the values are deliberately reduced-precision. 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. –  bignose Sep 24 '11 at 10:41
    
My point is that you are stretching the definition of equality so that A=C and B=C no longer implies A=B. As long as that is what you want... see my updated answer –  Jack Douglas Sep 24 '11 at 10:49
    
Also, do you want A=B to imply B=A as I've assumed in my edit? –  Jack Douglas Sep 24 '11 at 10:54
    
Good questions, thank you for making me think about my requirements. I'm exploring further at stackoverflow.com/questions/7539122/… –  bignose Sep 24 '11 at 12:50

2 Answers 2

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.

share|improve this answer
3  
+1 but create type does not require support functions in a low-level language unless you are creating a new base type. A compound type of a datetime and a precision enum for example. create domain can then be wrapped around the type to add check constraints. I personally would want the DB to handle the complexities of timezones, leap years etc if they are required so would tend to prefer wrapping the supplied types. –  Jack Douglas Sep 23 '11 at 5:48
    
@Jack Douglas: Good point about support functions. We didn't have to deal with time of day. We did have to deal with kinds of imprecision that ISO 8601 didn't anticipate, like "196?-??-01". (The imprecision didn't always go simply from right to left. Just about any digit could be obscured by, say, a coffee stain.) So I don't think it would work to present the compound type on output, but it wouldn't be too hard to take the compound type as input, and output an easily understood string with question marks in it. –  Mike Sherrill 'Cat Recall' Sep 23 '11 at 11:47
    
reply to self: create domain can't use a composite type as a base. –  Jack Douglas Sep 23 '11 at 20:14
1  
@bignose: Values with similar precision compare correctly for inequality. Values with different precisions might not compare well, but it's not clear what ('1964-??-??' < '1964-05-??') should evaluate to. They don't support interval arithmetic, either, but that's not well-defined at reduced precision anyway. –  Mike Sherrill 'Cat Recall' Sep 23 '11 at 23:03
1  
@bignose: No, I understood you, and I understood they don't act as datetime values. I was pointing out what they did well, what they don't do well, and what no implementation can do unarguably well. If you write the functions required by create type, you can make expressions of the form (DATE '1964-??-??' < 'DATE 1964-05-??') always evaluate to TRUE. What you can't do is convince everyone that it makes sense to do that. And that's probably why PostgreSQL, which has native support for about 3 dozen data types, doesn't natively support this kind of data type. –  Mike Sherrill 'Cat Recall' Sep 24 '11 at 23:09

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
share|improve this answer
    
Could those constraints be applied by creating a DOMAIN, to avoid repeating them on each field? –  bignose Sep 23 '11 at 22:43
    
No, unfortunately not, see my second comment on Catcall's answer. I believe you can create a custom constructor but that would not prevent modifications later on –  Jack Douglas Sep 24 '11 at 5:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.