sql - Rails - storing a date when the day is optional -

i'm collecting date store in database have account fact user may not know exact date. want offer option enter month , year. best way store these values on database?

if use date object without day , store date in database defaults 1st of month, inaccurate. had idea of potentially storing day, month , year separately integers , having method on model returned date object , whether or not day accurate (ie had been inputted user , not defaulted system).

it seems little messy though, there better way this?

thanks.

there multiple solutions available. choose 1 serves use-cases best:

1. individual date-part fields

you've experimented idea. seems this:

create table t(   year  int,   month int,   day   int ) 

a month of 2017-03 represented (2017, 3, null).

note fields null-able. can make year not null if want information @ least.

with model, must use client logic construct kind of date-like object further use.

the big disadvantage of model is hard index. index f.ex. make_date(year, coalesce(month, 1), coalesce(day, 1)) using in queries rather inconvenient. also, disallow value compositions, make no sense (f.ex. given year , day, not month), should add (really long) check constraint too, f.ex.

check (case   when year null month null , day null   else case when month null day null end end) 

2. sample date , precision

create table t(   sample_date      date,   sample_precision date_precision -- enum of f.ex. 'year', 'month', 'day' ) 

a month of 2017-03 represented ('2017-03-28', 'month').

this doesn't require long check constraints, hard select dates if sample_date sample (f.ex. when whole month of 2017-03 should represented in row, sample date 2017-03-28). when use first date sample_date (from values can take, based on sample_precision) things easier. following check constraint needed integrity:

check (date_trunc(sample_precision::text, sample_date)::date = sample_date) 

(more on improving further, later.)

3. possible range

you can store possible range of dates. either possible_start , possible_end or postgresql's daterange type.

create table t(   possible_start date,   possible_end   date,   -- or   possible_range daterange ) 

a month of 2017-03 represented ('2017-03-01', '2017-03-31').

in model when possible_start = possible_end date value exact. query 2 different things now:

• which rows happening around given date(s) sure (contains)
• which rows possibly happening around given date(s) (intersects)

both of these types of queries can use indexes daterange.

the beauty of not limited month ranges. can use literally length of ranges. drawback range must contiguous.

2. + 3. ?

there variant, has of 3.'s advantages, looks 2. interval type:

create table t(   possible_start  date,   possible_length interval day ) 

a month of 2017-03 represented ('2017-03-01', '1 month').

(the day qualifier restricts minimum precision of interval day. not required timestamp or timestamptz based solutions.)

the last possible date represented (possible_start + possible_length - interval '1 day')::date. or, whole range (for daterange index): daterange(possible_start, (possible_start + possible_length)::date) (ranges implicitly exclusive on end).

http://rextester.com/awio2403