MySQL DATETIME vs TIMESTAMP

TIMESTAMP : Very importan it stores the number of milliseconds that have been elapsed Since
1st January 1970 GMT.
1)When you are inserting a DATE value into the MYSQL TIMESTAMP
TIMESTAMP values are converted from the current time zone to UTC for storage,(i.e it will store the number of milliseconds elapsed from
1st January 1970) and converted back from UTC to the current time zone for retrieval.
(This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.
2) Timestamp has some specific qualities (ie.)
The first timestamp field in the database is automatically intialized and update with the current time
on every update of the row.
For More details See Please see : http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
3) Time stamp value is stored in 4 bytes
4) The range of values that this TIMESTAMP will store is very limited.(1970 to 2037)

DATETIME : 1) 8 bytes storage
2) Just stores what you have stored and retrieves the same thing which you have stored.
3) It has nothing to deal with the TIMEZONE and Conversion.

About these ads

  1. Prasanna

    So from data consistency perspective and considering migration issues, using datetime is better?

  2. agw

    I assume you meant seconds since 1970. Storing milliseconds in a 4 byte field would give a limit of less than 50 days




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



Follow

Get every new post delivered to your Inbox.

%d bloggers like this: