Postgres and partial dates

Question

So I have a table. Every entry in that table has a date associated with it, which has to:

• support partial dates: 2nd may 1994, 16:45 GMT+4 and april 2003 are both valid
• be comparable: I want to query for every entry before or after a specific date (this date is always complete, i.e. precise down to seconds and timezone). April 2003 should come after 2nd may 1994, 16:45 GMT+4, and dates should be ordered by precision (i.e. april 2003* comes before april 3rd 2003).

How would you tackle this problem? I'm currently working with Postgres (but not tied to it) and leaning towards an approach that simply has an int column for every date attribute, shifting the actual sort/compare logic into the application.

Any interesting ideas?

Answer 1

You can use ISO 8601 date strings. The ISO date format supports partial dates of the form you describe (but note not e.g. "April 23" without a year).

Your examples would be:

• "2nd may 1994, 16:45 GMT+4": 1994-05-02T12:45:00Z
• "april 2003": 2003-04

You can sort and compare these as simple strings and get the results you wanted. I.e. 2003-04-03 is lexically after 2003-04.

You can remove any level of precision from the end and it would still work. You just can't remove components from the beginning or the middle.

Note that you need to normalize all time-zones to UTC for this to work correctly, as in the above example.

An alternate approach, if you prefer to use date/timestamp types in the database, is to store the date's precision in a secondary column. E.g. store "April 2003" as a timestamp in the date field, and use a flag in a secondary field to indicate that this date is stored with "month" precision and should not be interpreted as being "1 April 2003, 12am".

Answer 2

In the PG database, you store a date or a timestamp (with time zone, presumably), and rely on date arithmetics to sort them as needed.

At the app level, you turn garbage such as April 2003 into a sane format that the database will accept to store. If you're using php, strtotime helps a lot.