Store date with optional month / day

1
votes

I want to store date in my Postgres database.
The only problem is that this date can have optional day or even month.

Example:
User provides time period when he was employed - not necessary full date (day + month + year), but only start year and end year.
However there are users, who worked only from may to october in the same year so month have to be provided too.

How to handle this kind of optional date parts?

2
ruby-on-railsrubydatabasepostgresqldatabase-design
Should user that worked from year 2000 be distinguished from the user that worked from January 1st 2000? – Branko Dimitrijevic
It's a matter of frontend representation, I'd like to hide month and day fields if user didn't provided them. – Kamil Lelonek
seems like you actually need to store them as year, day, month. Otherwise you will not be providing a valid Date. Then you can build a Date if all attributes are given. U/I will be easier this way as well since you can provide 3 drop down lists. – engineersmnky
Or just a plain string input with a pattern validator for the different use cases and a model method which returns the corresponding date object if the string can be parsed (e.g. with Date.parse. – svoop
fyi these are called "fuzzy dates" or "partial dates" – Neil McGuigan

2 Answers

3
votes

Use a proper date type anyway. Do not store text or multiple columns. That would be more expensive and less reliable.

Use the function to_date(), which is fit to deal with your requirements out of the box. For instance, if you call it with a pattern 'YYYYMMDD' and the actual string is missing characters for day, or month and day, it defaults to the first month / day of the year / month:

db=# SELECT to_date('2001', 'YYYYMMDD');
  to_date
------------
 2001-01-01

db=# SELECT to_date('200103', 'YYYYMMDD');
  to_date
------------
 2001-03-01

You could store a precision flag indicating year / month / day in addition if you need that.

2
votes

While the accepted answer is a good one, there is another alternative.

ISO 8601

The ISO 8601 standard defines sensible formats for textual representations of various kinds of date-time values.

A year is represented in the obvious manner, a four-digit number: 2014

A year-month is represented with a required hyphen: 2014-01
Note that in other ISO 8601 formats, the hyphen is optional. But not for year month, to avoid ambiguity.

A full date is similar: 2014-08-21 or without optional hyphens: 20140821. I recommend keeping the hyphens.

So you could store the values as text. The length of text would tell you whether it is year-only, year-month, or date.