MySQL8.0学习记录03 - 数据类型之日期和时间类型

日期和时间类型主要包括DATE, TIME, DATETIME, TIMESTAMP和YEAR，可以赋值0表示无效值。

注意事项

• 读取日期时间类型时，以标准格式输出；但是为日期时间类型赋值，或者与其比较时，MySQL尝试以多种格式解析给定的值；但是，日期部分必须符合格式year-month-day
• 日期与时间类型互转的规则：
  • DATE 转DATETIME 或 TIMESTAMP，时间补上’00:00:00’;DATE转TIME结果一直是’00:00:00’，无意义
  • DATETIME 和TIMESTAMP 转DATE, 会考虑微秒部分四舍五入。比如 ‘1999-12-31 23:59:59.499’ 变成’1999-12-31’，‘1999-12-31 23:59:59.500’ 变成’2000-01-01’
  • TIME 转其他类型，会使用CURRENT_DATE() 作为日期部分
  • TIME 和 DATETIME 可以转数字，TIME(N) or DATETIME(N) ，如N是0,转成int，否则转成DECIMAL

    select  CURTIME()+0,CURTIME(3)+0;
    -- 分别是143341、143341.076
    

• 当MySQL遇到一个日期或时间类型的值超出范围或对该类型无效时，它将该值转换为该类型的 "零 "值,TIME例外
• 可以存日期或时间类型‘零’值，无效日期将转成‘零’值，可以通过NO_ZERO_DATE 模式禁止；

  Data Type	“Zero” Value
  DATE	‘0000-00-00’
  TIME	‘00:00:00’
  DATETIME	‘0000-00-00 00:00:00’
  TIMESTAMP	‘0000-00-00 00:00:00’
  YEAR	0000

• SUM() 和AVG() 聚合函数不能用于日期或时间类型,但可以转换使用

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

• 支持日期和时间字面量, 比如

  select DATE'2022-01-01';
  select TIME'18:30:00';
  SELECT TIMESTAMP'2007-03-11 08:00:00';
  

时区

时区可以通过’+05:30’、‘Asia/Shanghai’,'UTC’等表示，时区转化可以通过CONVERT_TZ

SELECT
  CONVERT_TZ('2007-03-11 8:00:00','Asia/Shanghai','UTC') AS time1,
  CONVERT_TZ('2007-03-11 8:00:00','+08:00','+00:00') AS time2;
-- 结果都是2007-03-11 00:00:00

具体的注意事项

TIME

• 表示范围 ‘-838:59:59’ to ‘838:59:59’，小时部分可以超过24小时，是因为time可以表示成两个时间的差
• 时间赋值需要注意

   select time'11';      -- 00:00:11
  
   select time '1112';   -- 00:11:12
   select time '10:11:12'; -- 10:11:12
   select time '10:11'; -- 10:11:00
  

• 默认情况下，TIME越界之后，会截止最接近的边界。比如，‘-850:00:00’ 和’850:00:00’ 分别变成 ‘-838:59:59’ 和’838:59:59’.

DATE, DATETIME, and TIMESTAMP

• TIMESTAMP值从当前时区转换为UTC进行存储，并从UTC返回到当前时区进行检索；时区由系统变量 time_zone决定
• TIMESTAMP时区的转换可以通过 CAST() with the AT TIME ZONE ,将其以UTC 时区读取出来

  select CAST(TIMESTAMP'2007-03-11 08:00:00' AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut;
  

• 通过启用ALLOW_INVALID_DATES SQL模式，你可以让MySQL接受某些错误日期，如 “2009-11-31”，这个模式下，只会校验月份1-12，天数在1-31内
• DATE 和 DATETIME 允许月份或天数为0，可以存储不知确切日期的生日等。开启NO_ZERO_IN_DATE模式，可以禁止。
• 对于TIMESTAMP或DATETIME列，可以将当前的时间戳指定为默认值，自动更新值

    CREATE TABLE t1 (
      ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      dt DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    
    CREATE TABLE t1 (
      ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
      ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
    );
  

• 如果explicit_defaults_for_timestamp系统变量被禁用，可以赋值NULL来指定当前时间，前提是列不具有NULL属性

YEAR

• 对于两位数的年份来说，因为世纪未知，规则是：
  • 70-99 表示19世纪1970-1999
  • 00-69 表示20世纪2000-2069
• 非严格模式下，无效的YEAR 会转成0000
• MySQL 8.0 does not support the 2-digit YEAR(2) data type permitted in older versions of MySQL

字节大小与时间范围

可以指定时间类型的微秒的精度（最多6位），比如DATETIME(6)，默认0

类型	字节数	表示范围
YEAR	1	1901 to 2155, or 0000
DATE	3	‘1000-01-01’ 到 ‘9999-12-31’
TIME[(fsp)]	3 bytes + fractional seconds storage	‘-838:59:59.000000’ 到 ‘838:59:59.000000’
DATETIME[(fsp)]	5 bytes + fractional seconds storage	'1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’
TIMESTAMP[(fsp)]	4 bytes + fractional seconds storage	'1970-01-01 00:00:01.000000’UTC 到 '2038-01-19 03:14:07.999999’UTC

需要注意的是在MySQL 5.6.4之前，DATETIME 占8 字节

Fractional Seconds Precision Storage Required

位数	所占字节
0	0 bytes
1, 2	1 bytes
3, 4	2 bytes
5, 6	3 bytes