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
- or to have such kind of columns established with the
I performed a simple test on a table that has two columns holding date data points:
- The first column is declared with the
- The second one is defined with the
- 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
DATEcolumns is on par with that of
INTEGERones (yes, I realize
DATEcolumns are physically or internally stored as integers),
- or my testing procedure is not efficient.
Any thoughts you would like to share?