MySQL 日期类型如下表所示。

类型 占用空间 范围 格式 用途
DATE 3 字节 1000-01-01 ~ 9999-12-31 YYYY-MM-DD 日期值
TIME 3 字节 -838:59:59 ~ 838:59:59 HH:MM:SS 时间值或持续时间
YEAR 1 字节 1901 ~ 2155 YYYY 年份值
DATETIME 8 字节 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 字节 1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC

结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07		 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
带时区功能
日期类型 占用空间（byte <5.6） 占用空间（byte >=5.6）
DATE 3 字节 3
TIME 3 字节 3 + 微秒存储空间
YEAR 1 字节 1
DATETIME 8 字节 5 + 微秒存储空间
TIMESTAMP 4 字节 4 + 微秒存储空间
微秒位数 所需存储空间
0 0
1，2 1 字节
3，4 2 字节
5，6 3 字节

TIMESTAMP & DATETIME

TIMESTAMP 带时区功能。

  1. mysql> create table test_time(a timestamp, b datetime);
  2. Query OK, 0 rows affected (0.12 sec)
  4. mysql> insert into test_time values (now(), now());
  5. Query OK, 1 row affected (0.03 sec)
  7. mysql> select * from test_time;
  8. +---------------------+---------------------+
  9. | a                   | b                   |
  10. +---------------------+---------------------+
  11. | 2015-11-28 10:00:39 | 2015-11-28 10:00:39 |
  12. +---------------------+---------------------+
  13. 1 row in set (0.00 sec)
  15. mysql> select @@time_zone; 
  16. +-------------+
  17. | @@time_zone |
  18. +-------------+
  19. | SYSTEM      |
  20. +-------------+
  21. 1 row in set (0.00 sec)
  23. mysql> set time_zone='+00:00';
  24. Query OK, 0 rows affected (0.00 sec)
  26. mysql> select @@time_zone;
  27. +-------------+
  28. | @@time_zone |
  29. +-------------+
  30. | +00:00      |
  31. +-------------+
  32. 1 row in set (0.00 sec)
  34. mysql> select * from test_time;
  35. +---------------------+---------------------+
  36. | a                   | b                   |
  37. +---------------------+---------------------+
  38. | 2015-11-28 2:00:39 | 2015-11-28 10:00:39  |  -- 时区的差别体现出来了
  39. +---------------------+---------------------+
  40. 1 row in set (0.00 sec)

微秒

从 MySQL5.6.X 开始，支持微秒，最大显示6位。

  1. mysql> select now(6);
  2. +----------------------------+
  3. | now(6)                     |
  4. +----------------------------+
  5. | 2015-11-30 21:15:36.415358 |  -- 6位 微秒显示
  6. +----------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> select now(7);
  10. ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.  -- 不支持，最大到6
  12. mysql> create table test_time_fac (t datetime(6));
  13. Query OK, 0 rows affected (0.11 sec)
  15. mysql> insert into test_time_fac values(now(6)); 
  16. Query OK, 1 row affected (0.02 sec)
  18. mysql> select * from test_time_fac;
  19. +----------------------------+
  20. | t                          |
  21. +----------------------------+
  22. | 2015-11-30 21:19:27.900393 |  -- 由于是用了6位微秒位数，根据表格显示，实际存储的空间是 5 + 3 = 8 byte
  23. +----------------------------+
  24. 1 row in set (0.00 sec)

字段更新时间

  1. mysql> create  table test_field_update(
  2.     -> a int(10),
  3.     -> b timestamp not null default current_timestamp on update current_timestamp
  4.     -> );
  6. mysql> insert into test_field_update values(1, now(6));
  7. Query OK, 1 row affected (0.03 sec)
  9. mysql> select * from test_field_update;
  10. +------+---------------------+
  11. | a    | b                   |
  12. +------+---------------------+
  13. |    1 | 2015-11-30 21:55:18 |  -- 上面使用了now(6)，但是这里没有微秒，是因为定义的时候就是timestamp
  14. +------+---------------------+  -- 如果写成timestamp(6),就可以显示微秒
  15. 1 row in set (0.00 sec)
  17. mysql> update test_field_update set a=100 where a=1;  -- 只更新a字段
  18. Query OK, 1 row affected (0.03 sec)
  19. Rows matched: 1  Changed: 1  Warnings: 0
  21. mysql> select * from test_field_update;    
  22. +------+---------------------+
  23. | a    | b                   |
  24. +------+---------------------+
  25. |  100 | 2015-11-30 22:01:03 |  -- 发现b字段跟着改变了
  26. +------+---------------------+
  27. 1 row in set (0.00 sec)
  29. --
  30. -- 测试timestamp(6)
  31. --
  32. mysql> create table test_time_disp(
  33.     -> a int(10),
  34.     -> b timestamp(6) not null default current_timestamp(6) on update current_timestamp(6)  -- 定义了(6)
  35.     -> );
  37. mysql> insert into test_time_disp values(1, now(6));      
  38. Query OK, 1 row affected (0.02 sec)
  40. mysql> select * from test_time_disp;
  41. +------+----------------------------+
  42. | a    | b                          |
  43. +------+----------------------------+
  44. |    1 | 2015-11-30 22:03:23.545406 |  -- 插入了now(6), 这里就显示了6位微秒
  45. +------+----------------------------+
  46. 1 row in set (0.00 sec)

MySQL 时间函数

常用的 MySQL 时间函数如下表所示。

函数名 函数说明 备注
NOW 返回 SQL 执行时的时间 如果不考虑其他因素，可以理解为写完 SQL，敲下回车瞬间的时间
CURRENT_TIMESTAMP 与 NOW() 函数同义
SYSDATE 返回函数执行时的时间 MySQL 处理你的函数时的时间，统一 SQL 语句中，大于 NOW
DATA_ADD(date, interval expr uint) 增加时间
DATA_SUB(date, interval expr uint) 减少时间 可用 ADD，然后 unit 给负数
DATE FORMAT 格式化时间

所有时间函数 - 官方文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

NOW & SYSDATE的区别

  1. --
  2. -- NOW和SYSDATE的区别
  3. --
  4. mysql> select now(6),sysdate(6),sleep(5),now(6),sysdate(6);
  5. +----------------------------+----------------------------+----------+----------------------------+----------------------------+
  6. | now(6)                     | sysdate(6)                 | sleep(5) | now(6)                     | sysdate(6)                 |
  7. +----------------------------+----------------------------+----------+----------------------------+----------------------------+
  8. | 2015-11-30 21:40:58.572383 | 2015-11-30 21:40:58.572542 |        0 | 2015-11-30 21:40:58.572383 | 2015-11-30 21:41:03.572720 |
  9. +----------------------------+----------------------------+----------+----------------------------+----------------------------+
  10. 1 row in set (5.00 sec)
  12. -- 两个now(6)都相等，因为是SQL执行时的时间(可以简单立理解为按回车的时间)
  13. -- 两个sysdate(6)差了5秒，刚好是sleep(5)的时间
  15. --
  16. -- date_add
  17. --
  18. mysql> select date_add(now(), interval 5 day);   -- 增加5天
  19. +---------------------------------+
  20. | date_add(now(), interval 5 day) |
  21. +---------------------------------+
  22. | 2015-12-05 21:42:39             |
  23. +---------------------------------+
  24. 1 row in set (0.00 sec)
  26. mysql> select date_add(now(), interval -5 month);  -- 减少 5个月
  27. +------------------------------------+
  28. | date_add(now(), interval -5 month) |
  29. +------------------------------------+
  30. | 2015-06-30 21:43:49                |
  31. +------------------------------------+
  32. 1 row in set (0.00 sec)
  34. mysql> select date_sub(now(), interval 5 month);  -- 与add + 负数一致
  35. +-----------------------------------+
  36. | date_sub(now(), interval 5 month) |
  37. +-----------------------------------+
  38. | 2015-06-30 21:44:21               |
  39. +-----------------------------------+
  40. 1 row in set (0.00 sec)
  42. --
  43. -- date_format
  44. --
  45. mysql> SELECT DATE_FORMAT((select now(6)), '%H:%i:%s');
  46. +------------------------------------------+
  47. | DATE_FORMAT((select now(6)), '%H:%i:%s') |
  48. +------------------------------------------+
  49. | 21:48:30                                 |
  50. +------------------------------------------+
  51. 1 row in set (0.00 sec)

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/wgaftm 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。