SQL：各种处理时间的总结

左手的ㄟ右手 2022-03-27 07:22 188阅读 0赞

<table> 
 <thead> 
  <tr> 
   <th>日期类型</th> 
   <th>占用空间(byte)（&lt;5.6）</th> 
   <th>占用空间(byte)(&gt;=5.6)</th> 
   <th>表示范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATETIME</td> 
   <td>8</td> 
   <td>5 + 微秒存储空间</td> 
   <td>1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>3</td> 
   <td>3</td> 
   <td>1000-01-01 ~ 9999-12-31</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4</td> 
   <td>4 + 微秒存储空间</td> 
   <td>1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
   <td>1</td> 
   <td>YEAR(2):1970-2070, YEAR(4):1901-2155</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3</td> 
   <td>3 + 微秒存储空间</td> 
   <td>-838:59:59 ~ 838:59:59</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>微秒位数</th> 
   <th>存储空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>1, 2</td> 
   <td>1 byte</td> 
  </tr> 
  <tr> 
   <td>3, 4</td> 
   <td>2 bytes</td> 
  </tr> 
  <tr> 
   <td>5, 6</td> 
   <td>3 bytes</td> 
  </tr> 
 </tbody> 
</table>

## 实时时间 ##

### 当前时间 ###

#### \[时间+日期\] ####

> select now() AS '当前时间';
    select current_timestamp() AS '当前时间';
    select localtime() AS '当前时间';
    select localtimestamp() AS '当前时间';
    -- 上面四个完全相同，建议用now()
    +---------------------+
    | 当前时间            |
    +---------------------+
    | 2019-01-28 23:56:55 |
    +---------------------+
    
    select sysdate()  AS '当前时间';
    -- sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。

```sql
    --
    -- NOW和SYSDATE的区别
    --
    mysql> select now(6),sysdate(6),sleep(5),now(6),sysdate(6);
    +----------------------------+----------------------------+----------+----------------------------+----------------------------+
    | now(6)                     | sysdate(6)                 | sleep(5) | now(6)                     | sysdate(6)                 |
    +----------------------------+----------------------------+----------+----------------------------+----------------------------+
    | 2015-11-30 21:40:58.572383 | 2015-11-30 21:40:58.572542 |        0 | 2015-11-30 21:40:58.572383 | 2015-11-30 21:41:03.572720 |
    +----------------------------+----------------------------+----------+----------------------------+----------------------------+
    1 row in set (5.00 sec)
    --
    -- 两个now(6)都相等，因为是SQL执行时的时间(可以简单立理解为按回车的时间)
    -- 两个sysdate(6)差了5秒，刚好是sleep(5)的时间
    --
    
    (root@127.0.0.1) [mysql]> select now(6), sysdate(6);
    +----------------------------+----------------------------+
    | now(6)                     | sysdate(6)                 |
    +----------------------------+----------------------------+
    | 2019-01-28 23:56:07.463050 | 2019-01-28 23:56:07.463103 |
    +----------------------------+----------------------------+

*  从MySQL5.6.X开始，支持微秒，最大显示6位

mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2019-01-13 06:06:40 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select now(6);
    +----------------------------+
    | now(6)                     |
    +----------------------------+
    | 2019-01-13 06:06:43.081746 |  //  -- 由于是用了6位微秒位数，根据表格显示，实际存储的空间是 5 + 3 = 8 byte
    +----------------------------+
    1 row in set (0.00 sec)
    
    mysql> select now(7);
    ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.

#### \[当前日期\] ####

(root@127.0.0.1) [mysql]> SELECT  curdate() AS ‘当前日期’;
    +--------------------+
    | ‘当前日期’         |
    +--------------------+
    | 2019-01-28         |
    +--------------------+
    >> current_date(),current_date类似;

#### \[当前时间\] ####

(root@127.0.0.1) [mysql]>  SELECT CURTIME() AS '当前时间';
    +--------------+
    | 当前时间     |
    +--------------+
    | 23:59:53     |
    +--------------+
    > current_time(),current_time 类似

#### \[当前 UTC 日期时间\] ####

(root@127.0.0.1) [mysql]> SELECT utc_date();
    +------------+
    | utc_date() |
    +------------+
    | 2019-01-29 |
    +------------+
    > utc_date(), utc_time(), utc_timestamp() 相同

### 前30分钟&后30分钟 ###

(root@127.0.0.1) [mysql]>  SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE) AS '前30分钟';
    +---------------------+
    | 前30分钟            |
    +---------------------+
    | 2019-01-28 23:31:06 |
    +---------------------+
    1 row in set (0.00 sec)
    
    (root@127.0.0.1) [mysql]> SELECT DATE_SUB(NOW(), INTERVAL -30 MINUTE) AS '后30分钟';
    +---------------------+
    | 后30分钟            |
    +---------------------+
    | 2019-01-29 00:31:14 |
    +---------------------+

### 前一个小时&后一个小时 ###

(root@127.0.0.1) [mysql]> SELECT  DATE_SUB(NOW(), INTERVAL 1 HOUR) AS '前一个小时';
    +---------------------+
    | 前一个小时          |
    +---------------------+
    | 2019-01-28 23:02:04 |
    +---------------------+
    1 row in set (0.00 sec)
    
    (root@127.0.0.1) [mysql]> SELECT DATE_SUB(NOW(), INTERVAL -1 HOUR)AS '后一个小时';
    +---------------------+
    | 后一个小时          |
    +---------------------+
    | 2019-01-29 01:02:10 |
    +---------------------+

## 开始与结束 ##

### 今天的开始与结束 ###

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
    (root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 00:00:00') AS '今天开始';
    +---------------------+
    | 今天开始            |
    +---------------------+
    | 2019-01-29 00:00:00 |
    +---------------------+
    1 row in set (0.00 sec)
    
    (root@127.0.0.1) [mysql]> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';
    (root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 23:59:59') AS '今天结束';
    +---------------------+
    | 今天结束            |
    +---------------------+
    | 2019-01-29 23:59:59 |
    +---------------------+

### 明天的开始与结束 ###

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 00:00:00') AS '明天开始';
    +---------------------+
    | 明天开始            |
    +---------------------+
    | 2019-01-30 00:00:00 |
    +---------------------+
    1 row in set (0.00 sec)
    
    (root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 23:59:59') AS '明天结束';
    +---------------------+
    | 明天结束            |
    +---------------------+
    | 2019-01-30 23:59:59 |
    +---------------------+

### 昨天的开始与结束 ###

(root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
    +---------------------+
    | 昨天开始            |
    +---------------------+
    | 2019-01-28 00:00:00 |
    +---------------------+
    1 row in set (0.00 sec)
    
    (root@127.0.0.1) [mysql]> SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';
    +---------------------+
    | 昨天结束            |
    +---------------------+
    | 2019-01-28 23:59:59 |
    +---------------------+
    1 row in set (0.00 sec)

### 本周的开始与结束 ###

>  *  SELECT DATE\_FORMAT( SUBDATE(CURDATE(),DATE\_FORMAT(CURDATE(),’%w’)-1), ‘%Y-%m-%d 00:00:00’) AS ‘本周一’;
>  *  SELECT DATE\_FORMAT( SUBDATE(CURDATE(),DATE\_FORMAT(CURDATE(),’%w’)-7), ‘%Y-%m-%d 23:59:59’) AS ‘本周末’;
>  *  SELECT DATE\_FORMAT( DATE\_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), ‘%Y-%m-%d 00:00:00’) AS ‘本周一’;
>  *  SELECT DATE\_FORMAT( DATE\_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), ‘%Y-%m-%d 23:59:59’) AS ‘本周末’;![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]  
>     注意：mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周.

### 上周的开始与结束 ###

>  *  SELECT DATE\_FORMAT( DATE\_SUB( DATE\_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), ‘%Y-%m-%d 00:00:00’) AS ‘上周一’;
>  *  SELECT DATE\_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), ‘%Y-%m-%d 23:59:59’) AS ‘上周末’;  
>     ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]

### 本月的开始与结束 ###

>  *  SELECT DATE\_FORMAT( CURDATE(), ‘%Y-%m-01 00:00:00’) AS ‘本月初’;
>  *  SELECT DATE\_FORMAT( LAST\_DAY(CURDATE()), ‘%Y-%m-%d 23:59:59’) AS ‘本月末’;  
>     ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]

### 上月的开始与结束 ###

>  *  SELECT DATE\_FORMAT( DATE\_SUB(CURDATE(), INTERVAL 1 MONTH), ‘%Y-%m-01 00:00:00’) AS ‘上月初’;
>  *  SELECT DATE\_FORMAT( LAST\_DAY(DATE\_SUB(CURDATE(), INTERVAL 1 MONTH)), ‘%Y-%m-%d 23:59:59’) AS ‘上月末’;  
>     ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 3]

# 两个日期之间隔了多少天 #

-- DATEDIFF(o_orderdate, '1970-01-05')
        mysql> select datediff('1971-01-01', '1970-01-05');  -- 随意取一个1971-01-01，作为演示
        +--------------------------------------+
        | datediff('1971-01-01', '1970-01-05') |
        +--------------------------------------+
        |                                  361 |  -- 1971-01-01 减去 1970-01-5 为361天
        +--------------------------------------+
        1 row in set (0.00 sec)
            
        mysql> select datediff('1971-01-01', '1970-01-05') / 7;  -- 求周数
        +------------------------------------------+
        | datediff('1971-01-01', '1970-01-05') / 7 |
        +------------------------------------------+
        |                                  51.5714 |  -- 相隔51.5714周
        +------------------------------------------+
        1 row in set (0.00 sec)

# 总结【时间函数】 #

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>函数说明</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NOW</td> 
   <td>返回SQL执行时的时间 如果不考虑其他因素，可以理解为写完</td> 
   <td>SQL，敲下回车瞬间的时间</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIMESTAMP</td> 
   <td>与NOW()函数同义</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SYSDATE</td> 
   <td>返回函数执行时的时间</td> 
   <td>MySQL处理你的函数时的时间，统一SQL语句中，大于NOW</td> 
  </tr> 
  <tr> 
   <td>DATA_ADD(date, interval expr uint)</td> 
   <td>增加时间</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>DATA_SUB(date, interval expr uint)</td> 
   <td>减少时间 可用ADD，然后unit给负数</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>DATE FORMAT</td> 
   <td>格式化时间</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

所有时间函数参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

mysql> create  table test_field_update(
        -> a int(10),
        -> b timestamp not null default current_timestamp on update current_timestamp
        -> );
    
    mysql> insert into test_field_update values(1, now(6));
    Query OK, 1 row affected (0.03 sec)
    
    mysql> select * from test_field_update;
    +------+---------------------+
    | a    | b                   |
    +------+---------------------+
    |    1 | 2015-11-30 21:55:18 |  -- 上面使用了now(6)，但是这里没有微秒，是因为定义的时候就是timestamp
    +------+---------------------+  -- 如果写成timestamp(6),就可以显示微秒
    
    mysql> update test_field_update set a=100 where a=1;  -- 只更新a字段
    Query OK, 1 row affected (0.03 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from test_field_update;    
    +------+---------------------+
    | a    | b                   |
    +------+---------------------+
    |  100 | 2015-11-30 22:01:03 |  -- 发现b字段跟着改变了
    +------+---------------------+
    
    --
    -- 测试timestamp(6)
    --
    mysql> create table test_time_disp(
        -> a int(10),
        -> b timestamp(6) not null default current_timestamp(6) on update current_timestamp(6)  -- 定义了(6)
        -> );
    
    mysql> insert into test_time_disp values(1, now(6));      
    
    mysql> select * from test_time_disp;
    +------+----------------------------+
    | a    | b                          |
    +------+----------------------------+
    |    1 | 2015-11-30 22:03:23.545406 |  -- 插入了now(6), 这里就显示了6位微秒
    +------+----------------------------+

参考：https://www.jianshu.com/p/095c2952dd20  
http://blog.sina.com.cn/s/blog\_6d39dc6f0100m7eo.html 【推荐：这一篇文章总结的很好，推荐】

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: /images/20220327/69c48d7725674124b724f40d44bedb9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]: /images/20220327/7fe8d7fbed194a04b35962f213166e06.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]: /images/20220327/27eb7c832c2141379c0bade888e1171f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 3]: /images/20220327/2afae071b2cf42c48baa2e6dfaba3af8.png