mysql有三大类数据类型, 分别为数字、日期/时间、字符串, 本文将对时间类型进行介绍。

如下，这是mysql的时间类型相应字段。

名称 大小 范围 描述
date 3 bytes 1000-01-01~9999-12-31 日期
time 3 bytes -838:59:59~838:59:59 时间/间隔
datetime 8 bytes 1000-01-01 00:00:00~9999-12-31 23:59:59 具体时间
timestamp 4 bytes 1970-01-01 00:00:01~2038-01-19 03:14:07

时间戳
year 1 bytes 1901~2155 年
  • 关于时区

    • 在所有时间中，只有timestamp是基于时间戳的，它的取值范围也是以UTC为标准的，即1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。
    mysql支持获取不同时区的timestamp的值。另外，当存入timestamp后，改变服务器的时区，再次获取值会发生变化，因为已经被转换成新的时区所对应的值。

  • time说明

    • time值的区间是-838:59:59~838:59:59，大家可能会疑问一天不就只有24个小时么。实际上，time不仅仅可以用作存储一天的时间，很多时候用来记录两个时间相隔的时间，因此设计之初将范围设置的比较大。

  • 超出区间

    • 对于不存在的时间值，mysql会存储为0，date，datetime，或者timestamp 对应为'0000-00-00' 或者 '0000-00-00 00:00:00'。


    因为0值被当做是不合法的时间值，因此像timestamp类型，是无法存储'1970-01-01 00:00:00'的，区间也不包括在内。

    这里以year来举例，如下，我们可以看到超出区间范围的值，都被存为了0值。

    mysql> CREATE TABLE test (y year);
    Query OK, 0 rows affected (0.03 sec)
    mysql> INSERT INTO test (y) VALUES(1853),(1912),(2012),(2112),(2156);
    Query OK, 5 rows affected, 2 warnings (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 2
    #超出区间范围的被显示为对应的0值
    mysql> select * from test;
    +------+
    | y    |
    +------+
    | 0000 |
    | 1912 |
    | 2012 |
    | 2112 |
    | 0000 |
    +------+
    5 rows in set (0.00 sec)
  • year参数

    • 和int类似，year接受参数，来返回其不同的显示宽度，却并不会因此改变存储大小。
    这里举例year(2)和year(4)，他们只是在显示上不同，实际存储的值却是相同的。

    mysql> CREATE TABLE t (y2 YEAR(2), y4 YEAR(4));
    Query OK, 0 rows affected, 1 warning (0.02 sec)

    mysql> INSERT INTO t (y2) VALUES(1853),(1912),(2012),(2112);
    Query OK, 4 rows affected, 1 warning (0.01 sec)
    Records: 4  Duplicates: 0  Warnings: 1
    #只显示两位，1912和2012返回值相同都是12
    mysql> SELECT * FROM t;
    +------+------+
    | y2   | y4   |
    +------+------+
    |   00 | NULL |
    |   12 | NULL |
    |   12 | NULL |
    |   12 | NULL |
    +------+------+
    4 rows in set (0.00 sec)
    #虽然显示两位，但是存储了完整的年份
    mysql> UPDATE t SET y4 = y2;
    Query OK, 4 rows affected (0.00 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    #复制到四位year
    mysql> SELECT * FROM t;    
    +------+------+
    | y2   | y4   |
    +------+------+
    |   00 | 0000 |
    |   12 | 1912 |
    |   12 | 2012 |
    |   12 | 2112 |
    +------+------+
    4 rows in set (0.00 sec)
  • 其它总结

    • mysql提供了还算丰富的时间类型供大家使用。不过我们可以直接采用unixtime时间戳进行记录的，这样做的一个好处是，不需要关心时区，也不会因为程序的时区问题而产生程序异常。多机器间的数据同步、迁移，不同语言的处理都更加灵活。

    例如，现在的时间是1343002461，那我们可以设计一个字段为int类型，存储该值即可。

    int存储时间也有一个缺点，就是不直观，想要查找分析数据时，还需要转换成对应的真实时间。

    现在很多公司都是采用unixtime时间戳来进行存储的。