Store date with optional month / day
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?
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.
While the accepted answer is a good one, there is another alternative.
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-01Note 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.