MySQL中时间日期类型的使用

朱雀 2020-10-17 18:21 1109阅读 0赞

### 一、MySQL中 日期和时间类型 ###

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

| 类型      | 大小(字节) | 范围                                                         | 格式                | 用途                     |
| --------- | ---------- | ------------------------------------------------------------ | ------------------- | ------------------------ |
| 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/2038 结束时间是第 **2147483647** 秒，北京时间 **2038-1-19 11:14:07**，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS     | 混合日期和时间值，时间戳 |

------

--------------------

### 二、日期和时间类型的使用 ###

开始使用的是 Mysql 5.5.47

![20190222123009386.png][]

创建表时出错了，遇到两个问题。。

**问题一：**

\[Err\] 1067 - Invalid default value for 'create\_time'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG5sdW9sZWk_size_16_color_FFFFFF_t_70][]

查阅资料，原来是版本问题

在5.5到5.6.4版本里，对于DEFAULT CURRENT\_TIMESTAMP 子句，只能TIMESTAMP类型列上指定。

而从5.6.5开始以后的版本，对于DEFAULT CURRENT\_TIMESTAMP子句可以指定到TIMESTAMP或者DATETIME类型列上。

**问题二：**

想给一个类型为date字段设置一个default value（CURRENT\_DATE，CURRENT\_DATE()，NOW()）但试了很久都不行。最后试了下 DEFAULT NULL 成功了，再试下 DEFAULT 1也成功了。最后网上查了一下资料，原因是date类型的default value 一定要是一个常量，不能是一个函数或者是表达式。所以不能给MySQL的date类型的列设置默认值。

--------------------

### 三、TIMESTAMP和DATETIME比较 ###

**相同点：**

两者都可用来表示YYYY-MM-DD HH:MM :SS 类型的日期。

**不同点：**

他们的的存储方式，大小(字节)，表示的范围不同。

TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。

DATETIME，不做任何改变，基本上是原样输入和输出。

总结：TIMESTAMP和DATETIME 都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期， 除了存储方式和存储范围以及大小不一样，没有太大区别。但对于跨时区的业务，TIMESTAMP更为合适。

--------------------

### 四、时间与时间戳之间转换 ###

有些应用生成的时间戳是比这个多出三位，是毫秒表示，如果要转换，需要先将最后三位去掉（标准的10位数字，如果是13位的话可以以除以1000的方式），否则返回NULL

#将时间转换为时间戳unix_timestamp
    SELECT UNIX_TIMESTAMP('2019-02-22 13:25:07'); #1550813107
    
    #将时间戳转换为时间from_unixtime
    SELECT FROM_UNIXTIME(1550813107); #2019-02-22 13:25:07
    
    #NOW
    SELECT UNIX_TIMESTAMP(NOW()); #1550813420
    SELECT FROM_UNIXTIME(1550813420); #2019-02-22 13:30:20

--------------------

### 五、按时间日期查询 ###

然后我将 Mysql 改为 Mysql 5.7.17，升一下版本

#MySQL 按时间日期查询
    DROP TABLE IF EXISTS tb_test;
    CREATE TABLE IF NOT EXISTS tb_test(
      id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
      name VARCHAR(100) DEFAULT NULL COMMENT '名称',
      create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT '时间测试表';
    
    #插入数据
    INSERT INTO tb_test (name) VALUES
    ('luo'),
    ('lei'),
    ('luolei'),
    ('xing'),
    ('dxx');
    
    #修改数据
    UPDATE tb_test SET name='luo1', update_time='2021-01-23' WHERE id=100;
    UPDATE tb_test SET name='luo2', update_time='2022-02-23' WHERE id=101;
    UPDATE tb_test SET name='luo3', update_time='2023-03-23' WHERE id=102;
    
    #根据年月日查数据
    SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m-%d')='2019-02-22';
    #根据年月查数据
    SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m')='2021-01';
    #根据年查数据
    SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y')='2022';
    
    #根据日期区间查询数据,并排序 
    SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y') BETWEEN '2019' AND '2023' ORDER BY update_time ASC;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG5sdW9sZWk_size_16_color_FFFFFF_t_70 1][]

[20190222123009386.png]: /images/1602958870715.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG5sdW9sZWk_size_16_color_FFFFFF_t_70]: /images/1602958855067.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG5sdW9sZWk_size_16_color_FFFFFF_t_70 1]: /images/1602958840497.png