Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
the 1970-2038 range could bite you if you were using INTs too –  redguy Jul 17 '13 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.

ed

share|improve this answer

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.

share|improve this answer
    
+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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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