mysql datetime、date、time、timestamp区别

datetime、date、time、timestamp的区别啊，这事儿说实话挺绕的。

date就一个日期，格式是yyyy-mm-dd，比如你生日'1 9 9 0-05 -2 1 '，就存这个。
范围挺大的，1 000年到9 9 9 9 年都行。
存这个要3 个字节。

time就一个时间，格式是hh:mm:ss，比如你上班时间'09 :00:00'，就存这个。
范围可以很大，能存负数到8 3 8 小时5 9 分5 9 秒，跨天的时间或者持续时间都能表示。
存这个也要3 个字节。

datetime就日期时间都存，格式是yyyy-mm-ddhh:mm:ss，比如你结婚那天'2 02 3 -1 0-01 1 8 :00:00'，就存这个。
范围是1 000-01 -01 00:00:00到9 9 9 9 -1 2 -3 1 2 3 :5 9 :5 9 存这个要8 个字节。

timestamp也是日期时间都存，格式是yyyymmddhhmmss，显示的时候会转成yyyy-mm-ddhh:mm:ss。
比如你创建文档的时间'2 02 3 1 001 1 0:3 0:00'。
范围是1 9 7 0-01 -01 00:00:01 UTC到2 03 8 -01 -1 9 03 :1 4 :07 UTC。
存这个要4 个字节。

timestamp特牛逼的是，它会自动根据服务器的时区来转时间。
你存的时候用哪个时区，取的时候还是哪个时区，不用手动管。
而且如果你设成CURRENT_TIMESTAMP或者ONUPDATECURRENT_TIMESTAMP，它还会自动记录最后修改时间。

所以啊，date就存日期，time就存时间，datetime存日期时间不考虑时区，timestamp存日期时间考虑时区还自动记录修改时间。
具体用哪个得看你需求了。

MySQL date、datetime和timestamp类型的区别

说实话，这三种类型在项目中真挺容易搞混的，我当年接手老代码时差点把timestamp用成了datetime，好在同事及时拉住了。
让我来给你掰扯掰扯这区别，尽量说点实在的。

先说date吧，这玩意儿最简单，就存年月日，比如"1 9 9 8 -07 -1 5 "。
我之前在一个活动报名系统里就用了date，用户填生日，只需要知道是不是闰年判断年龄，完全够用。
它的存储格式是纯字符串的YYYY-MM-DD，MySQL内部其实不咋处理它，所以查询性能还行。
但你要是想存时分秒，直接报错，这类型就是为"只需要日期"设计的，别强求。

再聊datetime，这类型是个"万能选手"。
比如我们公司一个物流跟踪表，既要存收货时间又要存发货时间，用datetime就完美。
它的格式是完整的"2 02 3 -05 -3 0 1 4 :3 5 :2 2 "，看起来很正规。
关键在于，datetime不关心时区，存的就是绝对时间。
有个教训：我们早期有个项目用datetime存服务器时间没加时区处理，结果跨时区部署时数据全乱套了。
所以日志系统用datetime要特别小心，如果系统运行在UTC，记得存UTC时间。

最后是timestamp，这玩意儿最特别，它是个"时区敏感型"。
比如你存一个timestamp("2 02 3 -05 -3 0 1 4 :3 5 :2 2 ")，它实际存的是自1 9 7 0年1 月1 日UTC到现在的秒数，但显示时会自动根据数据库时区转成当地时间。
我在做跨国订单表时用这个就对了——存UTC时间，不同地区查看时按本地时区显示，绝了。
但有个坑：它的最大值到2 03 8 年，超出就炸，所以新系统尽量不用它当主键。
存储上它只占4 字节，datetime要8 字节，这点在亿级数据表上真的能省不少空间。

我建议你记住个场景：存生日用date，存绝对时间（比如事件发生时间）用datetime，存需要跨时区处理的UTC时间用timestamp。
有次我写测试时忘了这区别，存了本地时间当timestamp，结果客户看数据时时区错乱，差点被投诉。
所以选类型前最好问自己：这个时间需要变（时区）吗？需要秒级精度吗？最大存多少年？想明白这几个问题，基本就选对了。

MySQL日期格式化教程 13位时间戳转日期类型的解决方案

哎，这个MySQL的时间戳转换啊...我之前搞的时候也真是...挺麻烦的。

你想想啊，1 3 位毫秒时间戳，这玩意儿在2 02 2 年特别常见，到处都是。
比如你从某个前端系统或者什么API拿数据过来，就是1 3 位的毫秒数。
你直接往MySQL里存？不行啊，MySQL那些函数，像FROM_UNIXTIME，它要的是秒级的。

所以核心操作就是先把这个1 3 位的除以1 000。
对，就是除以1 000，别搞混了，这是秒级的。
然后你用FROM_UNIXTIME把它转成日期格式。
像这样：
sql SELECT FROM_UNIXTIME(1 2 3 4 5 6 7 8 9 01 2 3 / 1 000) AS converted_datetime FROM my_data;
这样就能得到一个标准的日期时间格式，像啥YYYY-MM-DD HH:MM:SS。

但你肯定不满足于这个吧？你想自定义格式啊。
比如，你可能只需要日期，不需要时间。
或者精确到秒。
这时候你就得用DATE_FORMAT了。
跟FROM_UNIXTIME一起用。

像这样，只需要日期：
sql SELECT DATE_FORMAT(FROM_UNIXTIME(1 2 3 4 5 6 7 8 9 01 2 3 / 1 000), '%Y-%m-%d') AS converted_date FROM my_data;
或者精确到秒：
sql SELECT DATE_FORMAT(FROM_UNIXTIME(1 2 3 4 5 6 7 8 9 01 2 3 / 1 000), '%Y-%m-%d %H:%i:%s') AS formatted_datetime FROM my_data;
这个%Y啊，%m啊，%d啊，%H啊，%i啊，%s啊，你就按需组合。
很简单。

哦对了，你如果用的是MySQL 5 .6 .4 或者更新的版本，你还想保留毫秒信息。
那咋办？你可以用MOD取余，拿到毫秒部分，然后用LPAD补齐三位。
拼接一下。
像这样：
sql SELECT CONCAT(DATE_FORMAT(FROM_UNIXTIME(1 2 3 4 5 6 7 8 9 01 2 3 / 1 000), '%Y-%m-%d %H:%i:%s'), '.', LPAD(MOD(1 2 3 4 5 6 7 8 9 01 2 3 , 1 000), 3 , '0')) AS datetime_with_ms_string FROM my_data;
这样就能得到一个带毫秒的时间字符串了。

反过来，你要把日期类型转回1 3 位毫秒时间戳，咋办？用UNIX_TIMESTAMP，然后乘以1 000。
像这样：
sql SELECT event_time, UNIX_TIMESTAMP(event_time) 1 000 AS timestamp_milliseconds_approx FROM my_events;
这样就能得到一个秒级的近似毫秒时间戳。

但你要是用的MySQL版本支持微秒精度，比如定义了DATETIME(3 )，那你得更精确一点。
你需要分离秒和毫秒部分。
用FLOOR和MICROSECOND。
像这样：
sql SELECT event_time, FLOOR(UNIX_TIMESTAMP(event_time)) 1 000 + FLOOR(MICROSECOND(event_time) / 1 000) AS precise_timestamp_ms FROM my_events;
这个比较复杂一点，但能保证精度。

你还得知道，获取当前时间的1 3 位时间戳。
MySQL 8 .0.1 7 以后，可以直接用UNIX_TIMESTAMP(NOW(3 )) 1 000。
简单。
但老版本呢？你得手动拼接，分秒和毫秒。
像这样：
sql SELECT FLOOR(UNIX_TIMESTAMP(NOW())) 1 000 + FLOOR(MICROSECOND(NOW()) / 1 000) AS current_timestamp_ms_manual;
关键注意事项啊，你还得注意时区。
NOW()返回的是服务器时区的时间。
UTC_TIMESTAMP()返回UTC时间。
你最好统一存储UTC时间，应用层再按用户时区显示。
避免跨地域数据偏差。
用CONVERT_TZ()转换时区的时候，确保时区表加载了。

数据类型选择也很重要。
DATETIME范围大，1 000-01 -01 到9 9 9 9 -1 2 -3 1 ，没时区信息。
TIMESTAMP范围小，1 9 7 0-01 -01 到2 03 8 -01 -1 9 UTC，受时区影响。
你要根据场景选。

版本兼容性也别忘了。
MySQL 5 .6 .4 以前不支持微秒精度。
UNIX_TIMESTAMP()只返回秒级结果。
你要毫秒精度，得用5 .6 .4 +，还得定义字段为DATETIME(3 )或DATETIME(6 )。

常见问题解析，为啥时间戳是1 3 位的？1 0位是秒级的，1 3 位是毫秒级的，现在流行。
MySQL为啥需要除以1 000？因为MySQL函数默认处理秒级，你要是毫秒级，就得手动除以1 000。

就这样，泡了1 0年，这些细节还是容易出错。
有时候一停顿，想一下，可能就偏激了，觉得简单，结果一用就懵。
总之，记住这些具体操作，多试几次，就熟练了。