What MySQL Date/Time type do you use in your applications and why?

I've always just kind of randomly chosen DATETIME or TIMESTAMP. What are the pros/cons of each and what do you use and why? (I realize some have more detail, I'm u开发者_开发问答sually looking for year/month/day/hour/minute/second resolution and I'm currently working with PHP, AS3, and Objective-C.

Thanks!

---

Timestamp can be updated/created automatically and can reflect on changing the timezone mysql session variable. Also date can store the dates of wider range (2038 vs 9999).

As for me - I prefer to use timestamp everywhere, since it is easy to display the time in user's timezone.

SET time_zone='$tz'

Where $tz is an offset in format +10:00 or string timezone representation like Asia/Vladivostok. After you perform this query - all the dates will be automatically converted to expected timezone. And from another point of view - each date you're storing in database will be stored with a glance of current timezone. (timestamp is stored in UTC, and mysql just offsets it when returns data to you, according the time_zone variable).

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html