datetime vs timestamp in mysql 5.5

Question

What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this?

I have a situation where I am trying to migrate from db2 to mysql. In DB2 a field is referred to as timestamp but MySQL's timestamp does not support values greater than 2038-01-19 03:14:07, so I have used datetime. Before implementing datetime I want to do an impact analysis.

Answer 1

Like @a_horse_with_no_name told on his comment, the differences are documented in here, but here is some information:

Size:

datetime - uses 8 bytes for each field

timestamp - uses 4 bytes for each field (half of the size)

Range:

datetime - 1000-01-01 00:00:00 to 9999-12-31 23:59:59

timestamp - 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC

Timezone:

as @ypercube mention, timestamp converts your data to utc and store it, and when you retrieve it, it converts from utc to your timezone connection.

Concept:

datetime - Is a calendar date(same point in time can be different depends on timezone).

timestamp - Is a point in time, does not matter the timezone your are.

Suggestion:

The 2 main differences are range and size, then think, do you really need dates bigger then 2038-01-19 03:14:07 at the moment(at the moment, no in the future!)? If no, go with timestamp for now, when you reach a point where you need a date range outside timestamp range, convert it to datetime.