Storing dates in date fields

From time to time, ports/security/vuxml/vuln.xml gets a date field containing an invalid date. FreshPorts stores these entries in a table that looks like this:

freshports.org=# \d vuxml
                                Table "public.vuxml"
     Column     |     Type     |                     Modifiers
----------------+--------------+----------------------------------------------------
 id             | integer      | not null default nextval('vuxml_id_seq'::regclass)
 vid            | text         | not null
 topic          | text         | not null
 description    | text         | not null
 date_discovery | text         |
 date_entry     | text         |
 date_modified  | text         |
 status         | character(1) | not null
Indexes:
    "vuxml_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "vuxml_affected" CONSTRAINT "$1" 
           FOREIGN KEY (vuxml_id) REFERENCES vuxml(id)
           ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "commit_log_ports_vuxml" CONSTRAINT "$1"
            FOREIGN KEY (vuxml_id) REFERENCES vuxml(id) 
            ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "vuxml_references" CONSTRAINT "$1" 
            FOREIGN KEY (vuxml_id) REFERENCES vuxml(id)
            ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=#

Of note is the three fields stored as TEXT values:

1. date_discovery
2. date_entry
3. date_modifed

I do not know why this table was designed with text values. They should be date fields. But because they are text, errors such as this can slip in:

<discovery>2011-03-FIXME</discovery>

FreshPorts happily stores these values, but will later encounter a problem:

This message was generated by the FreshPorts Daemon.

The database is freshports.org
at http://www.freshports.org/

Could not execute SQL statement
--
  SELECT count(DISTINCT CLPV.port_id) AS ports,
         count(DISTINCT V.id)              AS vulns
    FROM commit_log_ports_vuxml CLPV, vuxml V
   WHERE CLPV.vuxml_id = V.id
     AND greatest(V.date_modified, V.date_entry, V.date_discovery)::date >= (current_date - interval '14 days')::date
--
... maybe invalid?

--
hugs+kisses
FreshPorts Daemon

The above SQL was created quite some time after the table was designed an in use.

I recently converted the vuxml table in my development copy of FreshPorts:

freshports.org=# begin;
BEGIN
freshports.org=# alter table vuxml alter column date_discovery type date using date_discovery::date;
ALTER TABLE
freshports.org=# alter table vuxml alter column date_entry type date using date_entry::date;
ALTER TABLE
freshports.org=# alter table vuxml alter column date_modified type date using date_modified::date;
ALTER TABLE
freshports.org=# commit;
COMMIT

I’ll monitor how things go.