mysql datetime和timestamp区别

datetime:

保存格式为YYYYMMDDHHMMSS的整数，与时区无关，存入什么值就是什么值，不会根据当前时区进行转换。mysql5.6.4中可以存储小数片段，最多小数点后6位，在mysql5.5中没有小数片段，精确到秒。存储范围从1000-01-01 00:00:00到9999-12-31 23:59:59。长度8个字节，datetime(n)，n不是存储长度，而是小数点位数，即使小数位数是0，存储也是6位小数，仅显示0位而已，n=3毫秒，n=6微妙，默认精确到秒。

 

timestamp:

存入的是自1970-01-01格林尼治标准时间以来的秒数，与时区有关，查询时转为相应的时区时间。存储范围时1970-01-01 00:00:01到2038-01-19 03:14:07。在数据库用整型的int型存储，int范围时2的32次方减1。长度4个字节，因为存储长度的原因，决定了支持范围比datetime小，默认精确到秒。从5.5就开始有小数片段，最大6位，超过报错，旧版本中代表不同含义，TIMESTAMP(14)代表YYYYMMDDHHMMSS，最常见尺寸6、8、12、14，定义为0或比14大均会被强制定义为列长14，1-13范围的奇数值均被强制为下一个更大的偶数。

 

date:

1000-01-01到9999-12-31，只有日期部分，没有时间部分。

 

year:

1901-2155，字节数为1。

给year赋值的3种方法：
1.是直接插入4位字符串或者4位数字，
2.是插入2位字符串，这种情况下如果插入‘00’~‘69’，则相当于插入2000~2069；如果插入‘70’~‘99’，则相当于插入1970~1999。第2种情况下插入的如果是‘0’，则与插入‘00’效果相同，都是表示2000年。
3.是插入2位数字，它与第2种(插入两位字符串)不同之处仅在于：如果插入的是一位数字0，则表示的是0000，而不是2000年。所以在给YEAR类型赋值时，一定要分清0和‘0’，虽然两者相差个引号，但实际效果确实相差了2000年。

time:

-838:59:59到838:59:59，字节数为3。

time类型表示为“时：分：秒”，尽管小时范围一般是0~23，但是为了表示某些特殊时间间隔，MySQL将TIME的小时范围扩发了，而且支持负值。
对time类型赋值，标准格式是‘HH：MM：SS’，但不一定非要是这种格式。
如果插入的是‘D HH：MM：SS’格式，则类似插入了‘(D*24+HH)：MM：SS’。比如插入‘2 23:50:50’，相当于插入了‘71:50:50’。
如果插入的是‘HH：MM’或‘SS’格式，则效果是其他未被表示位的值赋为零值。比如插入‘30’，相当于插入了‘00:00:30’；如果插入‘11:25’，相当于插入了‘11:25:00’。
也可以插入‘D HH’和‘D HH：MM’，按上面的例子可以推理出来。
在MySQl中，对于'HHMMSS'格式，系统能够自动转化为标准格式。

 

性能比较：

对于MyISAM引擎，不建立索引的情况下，效率从高到低unix_timestamp(timestamp)>datetime(直接和时间比较)>timestamp(直接和时间比较)>unix_timestamp(datetime)。

对于MyISAM引擎，建立索引的情况下，效率从高到低unix_timestamp(timestamp)>datetime(直接和时间比较)>timestamp(直接和时间比较)>unix_timestamp(datetime)。

对于InnoDB引擎，不建立索引的情况下，效率从高到低unix_timestamp(timestamp)>datetime(直接和时间比较)>timestamp(直接和时间比较)>unix_timestamp(datetime)。

对于InnoDB引擎，建立索引的情况下，效率从高到低datetime(直接和时间比较)>timestamp(直接和时间比较)>unix_timestamp(timestamp)>unix_timestamp(datetime)。

总结，对于MyISAM引擎，采用unix_timestamp(timestamp）比较，对于InnoDB引擎，采用datetime直接时间比较。

 

unix_timestamp

返回时间戳，可以是时间类型，可以是字符串，字符串需提供时间表达式。

from_unixtime

把时间戳转为时间格式。可以加上时间格式。