Postgresql – How to encode a known date but unknown year

best practicesdatedatetimepostgresql

I am using a Postgres database, and have a table with a date-time field. For many records, we know the year but not the date. Sometimes we know the date but not the year. And other times we know the time but not the date.

How can I store partial date information while using the date-time data type?

Best Answer

The only thing that I can think of is to split the date into day/month and year - having one or the other as NULL as necessary. This is one of the only times I consider a split year/month/day structure, with separate fields, to be OK.

Related Solutions

SQL – How to Get Max Date for Each Year from List of Dates

It is possible that I am missing something, but you should be able to get the result easily using the max() aggregate and a GROUP BY:

select
  max(date) maxdate,
  year
from yourtable
group by year;

See SQL Fiddle with Demo

PostgreSQL – Define Column for Year Data Type

Either use a simple integer and represent BC as negative:

CREATE TABLE information (
  id serial PRIMARY KEY,
  year integer NOT NULL
);

or use a date that's constrained to the 1st of Jan and use 2014-01-01 etc in input/output:

CREATE TABLE information (
    id serial PRIMARY KEY,
    year date NOT NULL,
    CONSTRAINT year_must_be_1st_jan CHECK ( date_trunc('year', year) = year )
);

Which is more appropriate depends mostly on how your app is using the information. I think it's OK to represent a year as a simple integer - just please don't do this for year integer, month integer. That's awful to work with, and is where a constrained date is more appropriate.

I tend to use constrained dates so that when the requirements inevitably change I can adapt more easily.

Best Answer

Related Solutions

SQL – How to Get Max Date for Each Year from List of Dates

PostgreSQL – Define Column for Year Data Type

Related Question