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?

closed as off-topic by Philᵀᴹ, MDCCL, mustaccio, Andriy M, James Anderson Nov 11 '16 at 10:52

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, MDCCL, mustaccio, Andriy M, James Anderson
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    As Dates are internally stored as Integers performance should be the same, but when you use DATE you get all the date logic/functions for free. I never liked those yyyymmdd integers anyway. – dnoeth Nov 10 '16 at 18:46
  • The optimal type to store a date is going to be DATE of course, that's why it's there. The only way this answer could be anything but DATE would be designed for specific situation which you've not given us any information on. – Evan Carroll Nov 10 '16 at 20:00
  • 2
    Always store dates as dates. End of. – Philᵀᴹ Nov 10 '16 at 20:29
  • 1
    WHAT IS THE OPTIMAL DATATYPE TO STORE BOOLEANS AS?! – Evan Carroll Nov 10 '16 at 20:31

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.