Search code examples
phpsqldatespace-efficiency

Most efficient database date storing method?

So I am looking to store a date in my SQL database and am not sure what the most efficient way would be. I have thought of two different methods.

Firstly, people are going to be able to set a certain price for each day.

For example:

March 8, 2011: $1

March 9, 2011: $2

March 10, 2011: $5

For my database I was thinking of either storing a UNIX timestamp or storing in the month, day, and year in separate fields.

First method:

UNIX Timestamp

Pros

  • Only one field

Cons

  • Have to convert date to timestamp
  • Have to search between start and end dates
  • Prone to errors in search

Month/Day/Year

Pros

  • Easier to search

Cons

  • More fields
  • Takes up more space
  • Will be storing a LOT of data so could slow down the database.

With the above information, what would be your recommendation? (I can clarify if any of this is not clear.)

Solution

  • timestamp/datetime/date fields are more efficient (they are all stored as integers).
    So search is much faster on that.
    There is a small (very) overhead of the conversion between different date formats, but, unless you join by date fields (which is ...usually... not smart) you have no issue, it will happen only once.
    I do not see how it is error prone.