Gotchas in Drupal, part 1. Date field storage formats.

I've been working with Drupal now for about 3 years now. From time to time there are things that are just strange design decisions or just plain stupid.

One such thing is Date fields, there are a couple of them with no clear documentation (on the Field management page that is, there are if you Google, but who does that?). Anyway, I needed a Date field. The ISO sounded good, pretty much assumed it was just related to the data entry as I was going to feed in a 170000 nodes with date & time field in a easy for machines to parse format.

How.Wrong.Was.I

Drupal uses, obviously, a SQL database. Meaning that dates, should, obviously be stored in a SQL a DATE, TIME, DATETIME type right ? Oh, no no no.

What does Drupal do ? Use a varchar.

No wonder searching, by date ranges, on my collection of 170000 nodes was just a tiny bit slow.