How to convert full date/time to timestamp?

Should you use DATETIME or TIMESTAMP format in MySQL if you do many ORDER BYs on this field?

• I have a table with over a 1.1 million rows. I frequently select the first 25 order by timestamp desc. I have an index on this field and it performs pretty reasonably, however, I'm looking for optimum performance... would it help to move to datetime format instead of timestamp?

• Answer:

  This was an interesting question; my theory was that DATETIME would be faster as it's searching a smaller space. It turns out that it might be. http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/ This part is especially interesting. mysql> load data infile ‘/export/home/ntavares/test_datetime.sql’ into table test_datetime; Query OK, 10000000 rows affected (41.52 sec) Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0 mysql> load data infile ‘/export/home/ntavares/test_datetime.sql’ into table test_timestamp; Query OK, 10000000 rows affected, 44 warnings (48.32 sec) Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 44 This is not the same thing as querying your data but my guess is that querying TIMESTAMPs would be somewhat smaller than DATETIMEs. Ideally, you should  do your own profiling with your own data with your own queries. However, you should make the decision not only based on benchmarks but also your application; if you are updating things a lot and need to find the difference between to timestamps, then maybe TIMESTAMP is the right choice, even with the performance penalty. If you are storing the date for an event, use DATETIME. The reasoning behind is that TIMESTAMPS are UNIX timestamps that start from the UNIX epoch and define how long has passed aka. they define a specific point in time. DATETIME is what it is; it's the current date and time with all the daylight savings and timezones applied.