mysql datetime timestamp比较

最新推荐文章于 2026-01-05 16:48:34 发布

原创最新推荐文章于 2026-01-05 16:48:34 发布 · 831 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #timezone #database #types #date

本文详细解析了MySQL中TIMESTAMP与DATETIME两种日期时间类型的特性及使用场景。TIMESTAMP会根据当前时区转换为UTC存储，并在检索时转回原时区；而DATETIME则直接存储和检索所给定的时间值，不受时区影响。TIMESTAMP适用于记录自动更新的时间戳，存储占用较少，但范围有限；DATETIME则提供更大的灵活性和更宽的日期范围。

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.