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?

  • 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, 2016 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. Nov 10, 2016 at 20:00
  • 2
    Always store dates as dates. End of.
    – Philᵀᴹ
    Nov 10, 2016 at 20:29
  • 1

1 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.

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