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.


1 Answer 1


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


datetime - uses 8 bytes for each field

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


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


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.


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.


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.

  • What do you mean with "datetime is a calendar date."? May 14, 2013 at 12:09
  • 2
    And another major difference is that: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)" May 14, 2013 at 12:10
  • I updated the answer, It's kinda of you said about the timezones. May 14, 2013 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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