INT or TIMESTAMP for the change time column in a logging table?

Question

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?

Answer 1

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.

ed

Answer 2

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

1-You have DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP features in timestamp.

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.