Approximate date column

One of my customers would like to have a custom date column, where he could store the year onl开发者_JAVA百科y, a combination of month and year (without the day), or a classic date with day, month and year.

It should be possible to use this field for sorting the data. A "month-year" date should be considered as "01-month-year" for the sort, and a "year" date should be treated as "01-01-year" for the sort.

I could imagine two solutions to that:

• Store the date in the standard "day-month-year" format, and keep in a separate column how the date was entered ("year", "month-year", "day-month-year"), so the approximate date can be displayed exactly how it was entered.

• Use some sort of custom date column in the postgresql database.

Has anyone experience with that?

---

You could use date-time functions to extract date components. I don't think it has any sense to create additional columns. Also, some databases allow to create indexes by functions.