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 '13 at 9:21

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. – Bill Karwin Aug 15 '13 at 22:57
  • @BillKarwin I edited my answer. thank you for your attention. – Arash Mousavi Aug 16 '13 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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