postgresql - How to store dates with different levels of precision in postgres? -
i storing data on journal papers pubmed in postgres, , want store publication date of paper. however, date just year (see pubdate field), month , year, , day, month , year. what's sensible way store data in database? if use postgres date field, need specify day , month well, i'll need invent them in cases, , don't idea of losing information date's precision. for purposes think need year, don't know true possible future purposes. perhaps should have text fields day , month , year , , can convert them date field in future if need them? i store date , store precision well. for example: create type date_prec enum ('day', 'month', 'year'); create table pub ( pub_id integer primary key, pub_date date not null, pub_date_prec date_prec not null ); then can query table this: select pub_id, date_trunc(pub_date_prec::text, pub_date)::date pub; to ignore “random” day , month values in pub_date .
