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?

Answers


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.

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-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.


Need Your Help

What does out.absolute.dir meaning in linux?

java linux ubuntu ant

nI see in build files of different project in Linux frequently use of "out.absolute.dir" and I'm really curious about what does it exactly mean.So I write a short build file which I mentioned in

Set decimal value to certain value based on underlying number with t-sql case when

sql sql-server-2008 decimal case rounding

How to set the decimal values to a condition based value for underlying number using case when in T-Sql? Example to clarify: