MySQL中日期值格式总结

MySQL中日期和时间类型有如下几种

• DATE
  A date. The supported range is ‘1000-01-01’ to ‘9999-12-31’. MySQL displays DATE values
  in ‘YYYY-MM-DD’ format, but permits assignment of values to DATE columns using either strings or
  numbers.
• TIME[(fsp)]
  A time. The range is ‘-838:59:59.000000’ to ‘838:59:59.000000’. MySQL displays TIME
  values in ‘hh:mm:ss[.fraction]’ format, but permits assignment of values to TIME columns
  using either strings or numbers.
  An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision.
  A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
• DATETIME[(fsp)]
  A date and time combination. The supported range is ‘1000-01-01 00:00:00.000000’ to
  ‘9999-12-31 23:59:59.999999’. MySQL displays DATETIME values in ‘YYYY-MM-DD
  hh:mm:ss[.fraction]’ format, but permits assignment of values to DATETIME columns using
  either strings or numbers.
  An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision.
  A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
• TIMESTAMP[(fsp)]
  A timestamp. The range is ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19
  03:14:07.999999’ UTC. TIMESTAMP values are stored as the number of seconds since the
  epoch (‘1970-01-01 00:00:00’ UTC). A TIMESTAMP cannot represent the value ‘1970-01-01
  00:00:00’ because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for
  representing ‘0000-00-00 00:00:00’, the “zero” TIMESTAMP value.
  An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision.
  A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
• YEAR[(4)]
  A year in 4-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of
  values to YEAR columns using either strings or numbers. Values display as 1901 to 2155, or 0000.

官方说明有点复杂，简单来说就是

• 表示年月日，通常用DATE。
• 表示时分秒，通常用TIME。
• 表示年月日时分秒，通常用DATETIME。
• 表示时间戳，通常用TIMESTAMP。
• 表示年份，通常用YEAR。

细心地同学可能注意到了，给这些日期和时间数据类型填值时既可以用字符串也可以用数字。那么到底可以用什么样的格式呢？

笔者总结了下，以DATETIME为例进行介绍：

• YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许“不严格”语法，即任何标点符都可以用做日期部分或时间部分之间的间隔符。例如，“2020-02-18 00:41:30”、“2020.02.18 00+41+30”、“2020/02/18 004130”、“2020#02$18 00%41^30”是等价的。如果月、日、时、分、秒的值小于10，可以不用两位数表示，“2020-02-01 03:08:09”和“2020-2-1 3:8:9”相同。

root@database-one 00:51:  [gftest]> create table testdate(c1 datetime);
Query OK, 0 rows affected (0.36 sec)

root@database-one 00:51:  [gftest]> insert into testdate values('2020-02-18 00:41:30');
Query OK, 1 row affected (0.00 sec)

root@database-one 00:52:  [gftest]> insert into testdate values('2020.02.18 00+41+30');
Query OK, 1 row affected (0.01 sec)

root@database-one 00:52:  [gftest]> insert into testdate values('2020/02/18 00*41*30');
Query OK, 1 row affected (0.00 sec)

root@database-one 00:52:  [gftest]> insert into testdate values('2020#02$18 00%41^30');
Query OK, 1 row affected (0.00 sec)

root@database-one 00:53:  [gftest]> insert into testdate values('2020-02-01 03:08:09');
Query OK, 1 row affected (0.00 sec)

root@database-one 00:53:  [gftest]> insert into testdate values('2020-2-1 3:8:9');
Query OK, 1 row affected (0.00 sec)

root@database-one 00:53:  [gftest]> select * from testdate;
+---------------------+
| c1                  |
+---------------------+
| 2020-02-18 00:41:30 |
| 2020-02-18 00:41:30 |
| 2020-02-18 00:41:30 |
| 2020-02-18 00:41:30 |
| 2020-02-01 03:08:09 |
| 2020-02-01 03:08:09 |
+---------------------+
6 rows in set (0.01 sec)

• YYYYMMDDHHMMSS或YYMMDDHHMMSS格式没有间隔符的字符串。如果给出的字符串对于日期类型是有意义的，则合法。例如，“20200218004130”和“200218004130”被解释为“2020-02-18 00:41:30”，但“20200218004192”因为秒的部分不合法，被变为“0000-00-00 00:00:00”

root@database-one 01:04:  [gftest]> truncate table testdate;
Query OK, 0 rows affected (0.79 sec)

root@database-one 01:04:  [gftest]> insert into testdate values('20200218004130');
Query OK, 1 row affected (0.00 sec)

root@database-one 01:04:  [gftest]> insert into testdate values('200218004130');
Query OK, 1 row affected (0.01 sec)

root@database-one 01:04:  [gftest]> insert into testdate values('20200218004192');
Query OK, 1 row affected, 1 warning (0.00 sec)

root@database-one 01:05:  [gftest]> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
root@database-one 01:05:  [gftest]> select * from testdate;
+---------------------+
| c1                  |
+---------------------+
| 2020-02-18 00:41:30 |
| 2020-02-18 00:41:30 |
| 0000-00-00 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

• YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字。如果给出的数字对于日期类型是有意义的，则合法。例如，20200218004130和200218004130被解释为“2020-02-18 00:41:30”。数字值应为6、8、12或者14位长。如果一个数值是8位或14位长，则假定为YYYYMMDD或者YYYYMMDDHHMMSS格式，前4位表示年。如果数字是6位或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位表示年。

root@database-one 01:12:  [gftest]> truncate table testdate;
Query OK, 0 rows affected (0.58 sec)

root@database-one 01:12:  [gftest]> insert into testdate values(20200218004130);
Query OK, 1 row affected (0.01 sec)

root@database-one 01:13:  [gftest]> insert into testdate values(200218004130);
Query OK, 1 row affected (0.01 sec)

root@database-one 01:13:  [gftest]> insert into testdate values(20200218);
Query OK, 1 row affected (0.00 sec)

root@database-one 01:14:  [gftest]> insert into testdate values(200218);
Query OK, 1 row affected (0.00 sec)

root@database-one 01:15:  [gftest]> insert into testdate values(004130);
Query OK, 1 row affected, 1 warning (0.03 sec)

root@database-one 01:16:  [gftest]> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
root@database-one 01:16:  [gftest]> select * from testdate;
+---------------------+
| c1                  |
+---------------------+
| 2020-02-18 00:41:30 |
| 2020-02-18 00:41:30 |
| 2020-02-18 00:00:00 |
| 2020-02-18 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
5 rows in set (0.01 sec)

• 函数返回的结果，例如now()、current_date等。

root@database-one 01:16:  [gftest]> truncate table testdate;
Query OK, 0 rows affected (0.38 sec)

root@database-one 01:18:  [gftest]> insert into testdate values(now());
Query OK, 1 row affected (0.00 sec)

root@database-one 01:18:  [gftest]> insert into testdate values(current_date);
Query OK, 1 row affected (0.01 sec)

root@database-one 01:18:  [gftest]> select * from testdate;
+---------------------+
| c1                  |
+---------------------+
| 2020-02-18 01:18:29 |
| 2020-02-18 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)