TIMESTAMP vs. DATETIME, 该用哪一个?

尽管他们看起来一样,用起来也几乎一样. 然而还是很多地方不一样. 笔者会指出不同之处,包括一些容易出错和特殊的地方.

范围
TIMESTAMP从新纪元开始, 也就是格林威治时间'1970-01-01 00:00:01', 结束于格林威治时间'2038-01-19 03:14:07'. 今天看来,直到我们退休这都是不错的. 不过年轻人可能必须在38年后面临类似千禧年的bug, 那一天必然到来.
DATETIME是从公元'1000-01-01 00:00:00'到'9999-12-31 23:59:59'; 稍后详表.

从范围上看, 记录当前时间的日志通常可以用TIMESTAMP, 而祖父,孙女的生日就需要DATETIME.
一般笔者建议和"现在"相关的事情可以用TIMESTAMP.
例如:
增加一个当前的新记录? 可以用TIMESTAMP.
记录几分钟, 几天或者几个月后失效的事情? 只要在2037年前都可以安全地使用.

而其它的事情最好使用DATETIME. 特别是和生日,保险,交易等等相关的, 都属于这个范畴.
不过DATETIME也不支持和历史相关的记录. 例如,罗马在公元前很久衰败. 这种情况就必须自己实现了. 公元年已经不适用了.(仅对MySQL来说,每个数据库实现范围不同)


存储
TIMESTAMP有4字节. DATETIME有8个字节. 来看看为什么?
TIMESTAMP仅仅是从新纪元开始计算的秒数. 这是一个数字. 不仅仅是个数字, 而且不断在增长, 没有间断. 它实际上就等于UNIX_TIMESTAMP函数

DATETIME就复杂多了. 手册上定义:
         * DATETIME: 8个字节
             o 四个字节的整数存放 YYYY*10000+MM*100+DD
             o 四个字节的整数存放 HH*10000+MM*100+SS
所以DATETIME很明显不是连续的. 20110307095159后面就是20110307095200. 这就像没有分割符的字符串.看下面:
    root@mysql-5.1.51> SELECT NOW()+0;
    +-----------------------+
    | NOW()+0               |
    +-----------------------+
    | 20110307095238.000000 |
    +-----------------------+
这样内部的标识意味着必须能向前向后转换, 当你在DATETIME上加5秒时,MYSQL不能简单的直接在某个数上加5, 而是个更复杂的计算.

MySQL特有的DATETIME

笔者提到MySQL的DATETIME从'1000-01-01 00:00:00'开始?(Oracle允许从公元前4712-儒略日开始,SQL Server从0001年开始),这点让其很困惑. 唯一笔者能想到这样设计的理由就是:DATETIME可以用多种格式来表示, 而且能够从很多格式的字符串中自动解析出来.  '110307095100' 和 '20110307095100'都表示相同的DATETIME. 这个自动的解析是基于字串中数字的个数, 数字的个数决定着如何被解析.而第一年是1000的设计正可以让数字的个数被正确估算出来. 作为一个程序员,笔者不喜欢这种方式,而更喜欢强类型, 即解析''YYYY-MM-DD HH:MM:SS'这样的字串(甚至,直接接受一个对象,然后用自己的connector来做转换.) 甚至, 笔者更倾向于这种方式. 这样觉得安全, 不会出现传递一个错误的字串, 然后直接被接受而不报错的情况.

原文: http://code.openark.org/blog/mysql/timestamp-vs-datetime-which-should-i-be-using