MYSQL5.7日期和时间类型总览

MYSQL5.7日期和时间类型总览

Comments 0 Comment

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-overview.html

对于DATE和DATETIME范围的描述，“SUPPORTED”一词仅仅代表可以工作，并不做其它保证。

MYSQL允许在TIME, DATETIME和TIMESTAMP上使用秒片段（fractional seconds）值，精度可以到微秒（6位有效数字）。定义一个包含秒片段的字段，使用的语法是type_name(fsp)，这里type_name是TIME,DATETIME, TIMESTAMP，fsp指定了秒片段的精度。例如：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

如果指定了fsp值，取值范围必须是0~6。如果指定的是0，那么没有秒片段。如果忽略这个设置，默认值是0。（这不同于SQL标准，SQL标准是6，这么做的原因是为了兼容之前MYSQL版本）

任何TIMESTAMP或DATETIME列，都有自动初始化和更新的属性。

  • DATE：日期类型。支持的范围是 ‘1000-01-01’ to ‘9999-12-31’。MYSQL显示DATE值的格式是’YYYY-MM-DD’，但是也允许分配字符串或数字到DATE字段列上。
  • DATETIME[(FSP)]：日期时间的组合。支持的范围是’1000-01-01 00:00:00.000000′ to ‘9999-12-31 23:59:59.999999’。MYSQL显示的DATETIM值的格式是 ‘YYYY-MM-DD HH:MM:SS[.fraction]’，但也允许分配字符串或数字到该类型上。可选的FSP值的范围是0~6，用来指定秒片段的精度。默认值是0，指定了0代表没有秒片段。自动初始化和更新到当前日期和时间的属性可以通过使用DEFAULT和ON UPDATE子句定义。as described in Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
  • TIMESTAMP[(FSP)]：时间戳类型。范围是 '1970-01-01
    00:00:01.000000'     UTC到 '2038-01-19
    03:14:07.999999'     UTC。TIMESTAMP的值是从'1970-01-01 00:00:00' UTC开始以秒来存储。TIMESTAMP不能用来描述 '1970-01-01 00:00:00'这个时间，因为它的值是0。并且0值是被TIMESTAMPE预留的“零”值——'0000-00-00
    00:00:00'    。可选的FSP值的范围是0~6，用来指定秒片段的精度。默认值是0，指定了0代表没有秒片段。服务器处理TIMESTAMP定义依赖于 explicit_defaults_for_timestamp系统变量。如果 explicit_defaults_for_timestamp开启，就不会允许使用 DEFAULT CURRENT_TIMESTAMP 或ON
    UPDATE CURRENT_TIMESTAMP    属性。它们的值必须被显示指定在列的定义中。同时TIMESTAMP列也不能被定义为NOT NULL而允许NULL值。如果 explicit_defaults_for_timestamp关闭，服务器处理TIMESTAMP的方式如下：

    除非指定了其它内容，表中的第一个TIMESTAMP列如果没有被显示的分配一个值则自动更新成最近修改的日期和时间。这使用TIMESTAMPE列在插入和更新时变的非常有用。你同样也可以通过分配NULL值来设置TIMESTAMP列为当前的日期和时间，除非它被定义为允许设置NULL值。
自动初始化和更新当前的日期和时间，可以通过使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP的子句来实现。默认情况下第一个TIMESTAMP列已经有这些属性。但是也可以通过显示设置这个属性。
  • TIME[(FSP)]：时间类型。它的范围是 '-838:59:59.000000' to '838:59:59.000000'。MYSQL显示TIME值的格式是'HH:MM:SS[.fraction]'，但是也允许分配字符串或数字到该列上。可选的FSP值的范围是0~6，用来指定秒片段的精度。默认值是0，指定了0代表没有秒片段。
  • YEAR[(4)]：4位十进制数组成的格式。MYSQL显示YEAR的格式是YYYY，但是同样允许使用字符串或数字分配值。值显示从1901到2155和0000。

SUM()和AVG()这两个聚合函数不能用于时间值。（它们把第一个非数字字符后面的一切转换成数值型。）为了绕开这个问题，要先转换数值的单元，然后执行聚合，然后再转换回时间值（temporal value）。例如：

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

 

发表回复