Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
4  
You do know the differences are documented in the manual? dev.mysql.com/doc/refman/5.5/en/datetime.html –  a_horse_with_no_name May 14 '13 at 10:57

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
What do you mean with "datetime is a calendar date."? –  ypercube May 14 '13 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.)" –  ypercube May 14 '13 at 12:10
    
I updated the answer, It's kinda of you said about the timezones. –  altmannmarcelo May 14 '13 at 12:29
    
@all Thanks :) .. –  Hitesh Chouhan May 14 '13 at 13:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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