We're having an argument over what to use for storing change date in a new log table for our site.

One side says, use INT for the timestamp, storing the UNIX timestamp. We can convert the datetime query in PHP to a UNIX integer and query based on that (WHERE log_date BETWEEN [convert 12/25/2012 to timestamp] AND [convert 12/31/2012 to timestamp]).

The other side says, use TIMESTAMP for this; we can then use the native tools to query. (WHERE log_date BETWEEN "2012-12-25" AND "2012-12-31").

Since this is a logging table, we're not concerned about the 1970-2038 range of TIMESTAMP.

Which makes more sense?

  • the 1970-2038 range could bite you if you were using INTs too
    – redguy
    Jul 17, 2013 at 9:21

2 Answers 2


I have done it both ways. I prefer using TIMESTAMP because it makes adhoc queries easier. If you only use a GUI to query the logs, then use whatever that programmer wants.

This one boils down to a style question because there isn't much difference in the functionality or performance.



It depend on your use case , queries and MySQL version. Basically I suggest timestamp instead of int because of this reasons:


2-timestamp are more flexible in time zone functionality.(actually you should consider MySQL's TIMESTAMP data type does not store timezone,but DATETIME does, As Bill Karwin said in comments)

3- benchmarks show timestamp are more better in performance in most use cases. for myisam and innodb

4-You can use datetime function for date comparision, addition, substraction, range lookup etc, without the need to use FROM_UNIXTIME() function - it will make it easier to write queries that can use indexes.

  • +1, but fwiw MySQL's TIMESTAMP data type does not store timezone. DATETIME does. Aug 15, 2013 at 22:57
  • @BillKarwin I edited my answer. thank you for your attention. Aug 16, 2013 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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