What datatype should I use to store time() in MySQL for my application?

Question

I plan to capture the start and end of user initiated activities on my website using time() in php. I'm not sure if this is the best way to capture start/end times. Anyway, the data will be stored in MySQL, but again I'm not sure what datatype I should use. Based on the answers I've read on stackoverflow, the datatype used depends on the purpose of the application.

Purpose of the application

1. At it's simplest, I want to record start, stop (and duration) of an activity. Probably using time().
2. At it's most complicated I'd like to plot statistics based on when the user did a certain activity, how much time they spent doing the activity (in total), and when they were the most successful/least successful etc, etc. (all based on the start/end times) Something to keep in mind. The users will be from all over the world.

MORE INFO If an activity is repeated a new record will be made for it. Records will not be updated.

At first, I had planned on storing unix timestamps in MySQL (as an integer datatype?), but from what I understand this is a bad idea, because I will lose a lot of MySQLs ability to process the information. If I store the information as DATETIME, but then move the server, all the times will change based on the local time of the server. Something I found confusing was that TIMESTAMP in MySQL is not the same as a unix timestamp- which is what I would be getting if I used time().

I'm aware that the unix timestamp can only hold dates up to 2038 for some systems, but that isn't a concern (at the moment).

Question: Should I use time() to capture start and end times for user initiated activities? Based on the purpose of the application, what datatype should I use to store the start and stop of user initiated activities?

THANKS

Thanks for the answers everyone. TBH I'm not convinced either way yet, so I'm still doing some research. I chose the TIMESTAMPS option because I really would like to store my information using UTC (GMT). It's a pity though that I will lose out on some of MySQLs inbuilt time functions. Anyway thanks again for your answers.

Answer 1

If you're going worldwide, MySQL's TIMESTAMP is almost universally a good choice over DATETIME, since it stores the time as UTC instead of local time so DST changes won't cause you problems if analyzing in multiple time zones.

Having a non DST changing time zone as a base can be a life saver, converting between multiple time zones with different DST changeover dates can really cause problems, consider for example having a timestamp during the hour that happens twice in a change from summer- to winter time.