What is the optimal data type for columns that retain dates, DATE or INTEGER? [closed]

Question

In my experience, old school thinking tells that, for optimal performance, date data points should be stored as an INTEGERs (e.g., as in 20160101), since they are manipulated faster, accelerate searches, etc.

I’m involved in a Postgres environment in which SELECT statement execution speed is prized, and a discussion started up as to what is more convenient:

• To have columns that retain dates set up with some variation of the DATE data type,
• or to have such kind of columns established with the INTEGER data type.

Current considerations

I performed a simple test on a table that has two columns holding date data points:

• The first column is declared with the DATE data type
• The second one is defined with the INTEGER data type
• Both columns are served with INDEXes at the physical level
• I have INSERTed 100,000 rows into such table.

Then, I was surprised to see that the column of type DATE returns slightly faster results than the one of type INTEGER when executing an SELECT statement like the following one:

  SELECT foo 
    FROM bar
   WHERE my_date = '6/15/2015'

This situation appears to tell me one of two things:

• Either the speed execution of DATE columns is on par with that of INTEGER ones (yes, I realize DATE columns are physically or internally stored as integers),
• or my testing procedure is not efficient.

Any thoughts you would like to share?

Answer 1

I'm going to always vote for storing a Date as a Date or Timestamp if you need time of day too. It adds so much value in handling dates properly. Storing a date as an integer means many things are much more complicated.

How do I add a day or week to a date stored as an integer? Depends on the format used for the integer and even in your suggested format a simple +1 or +7 doesn't work as you could end up on Feb 30th or January 36th. If you move up to a timestamp it gets even more complicated with time zones, daylight savings, etc.

How do I ensure that all integers saved match the preferred format? You could manually add a trigger to try to do validation but in an integer you can't even use a format that eliminates ambiguity as you can't use an abbreviation for the month. 20161201. Is that December 1st or January 12th?

There simply is no reason to reinvent the wheel when it comes to everything a database provides for handling date or date & time. If performance was significantly different for some reason in some situation I'd rather figure a way to work around that or fix it (you can literally rewrite Postgres to fix it if needed) than start reinventing the wheel.