mysql的日期与时间函数，varchar与date相互转换

痛定思痛。 2023-09-28 16:46 6阅读 0赞

## 1.mysql获取系统时间 ##

### 1.获取当前系统时间 ###

在后面加上一个0，可以将当前查询到的时间转成数字输出。

#### 1.获取当前日期+时间 ####

**函数：**

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>now()</code></td> 
   <td align="left">函数开始执行就获取到值</td> 
  </tr> 
  <tr> 
   <td align="left"><code>sysdate()</code></td> 
   <td align="left">函数执行期间动态获取值</td> 
  </tr> 
 </tbody> 
</table>

select now(), sysdate();

![f4ac58fcdef94e578193d5a7f32955c6.png][]

#### 2.获取当前日期 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>curdate()</code></td> 
  </tr> 
  <tr> 
   <td align="left"><code>current_date()</code></td> 
  </tr> 
  <tr> 
   <td align="left"><code>current_date</code></td> 
  </tr> 
 </tbody> 
</table>

select curdate(), current_date(), current_date, CURRENT_DATE() + 0;

![4580d18b5fab4670a9a7b73429a6eaf6.png][]

#### 3.获取当前时间 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>curtime()</code></td> 
  </tr> 
  <tr> 
   <td align="left"><code>current_time()</code></td> 
  </tr> 
  <tr> 
   <td align="left"><code>current_time</code></td> 
  </tr> 
 </tbody> 
</table>

select curtime(), current_time(), current_time, current_time()+0;

![91793a6b8b214647b60390aecfd12be2.png][]

#### 4.获取UTC时间 ####

select utc_date()+0, utc_time(), utc_timestamp;

![91bd0e040ce24a19bd49bc34b1c6a3c0.png][]

### 2.获取昨天、今天、明天的，上一小时，下一小时的时间 ###

#### 1.昨天、今天、明天 ####

**日期相减**

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><p><code>DATE_SUB(date,INTERVAL expr type)</code></p></td> 
   <td align="left">date：合法的日期表达式<br>expr：时间间隔<br>type：second、hour、day、minute…</td> 
  </tr> 
 </tbody> 
</table>

**日期 + 时间：**

select date_sub(now(),interval 1 day) as '昨天',
    	   date_sub(now(),interval 0 day) as '今天',
    	   date_sub(now(),interval -1 day) as '明天'

**日期：**

select date_sub(curdate(),interval 1 day) as '昨天',
    	   date_sub(curdate(),interval 0 day) as '今天',
    	   date_sub(curdate(),interval -1 day) as '明天'

![0ae7a671f16247afabe437bf02bd85c5.png][]

#### 2.上一小时，前30分钟，后四十五分钟 ####

select date_sub(now(),interval 1 hour) as '上一小时',
    	   date_sub(now(),interval 30 minute) as '前30分钟',
    	   date_sub(now(),interval 45 minute) as '后四十五分钟';

![032611bd0a9f48d88692d6c2bd5199cd.png][]

#### 3.日期相减、时间相减 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>DATEDIFF(date1,date2)</code></td> 
   <td align="left">date1：合法的日期表达式<br>date2：合法的日期表达式</td> 
  </tr> 
  <tr> 
   <td align="left"><code>TIMEDIFF(time1,time2)</code></td> 
   <td align="left">time1：合法的时间表达式<br>time2：合法的时间表达式</td> 
  </tr> 
 </tbody> 
</table>

select datediff(now(),date_sub(now(),interval 1 day)) as '日期相减';

![e100a7b56c9f4e5d81420d7b49caf0a6.png][]

select timediff(now(),date_sub(date_sub(now(),interval 45 minute),interval 1 day)) as '时间相减';

![6af846e84c6e4ab2aa3c2837778618ef.png][]

#### 4.日期相加、时间相加 ####

**日期相加**

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>DATE_ADD(date,INTERVAL expr type)</code></td> 
   <td align="left"><a rel="nofollow">见DATE_SUB</a></td> 
  </tr> 
  <tr> 
   <td align="left"><code>TIMEDIFF(time1,time2)</code></td> 
   <td align="left">time1：合法的时间表达式<br>time2：合法的时间表达式</td> 
  </tr> 
 </tbody> 
</table>

select date_add(now(),interval 3 day) as '三天后', 
    	   date_add(now(),interval 30 minute) as '半小时后';

![7665c912cd3f402fbaaac1262064794c.png][]

#### 5.补充——sleep() ####

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>sleep()</code></td> 
   <td align="left">让SQL语句执行一段时间，单位是秒</td> 
  </tr> 
 </tbody> 
</table>

select sleep(5), now(), sysdate();

![d73eee276c4d4694805ffc608243d4e0.jpeg][]

## 2.mysql的varchar与date的转换 ##

### 1.varchar转date ###

select str_to_date('2022 7 26','%Y %m %d') as '今天',
    	   str_to_date('2022,27,7','%Y,%d,%m') as '明天',
    	   str_to_date('2022725','%Y%m%d') as '昨天';

![a530d58c259f40ae976001c87548539d.png][]

select str_to_date('26-7-2022-123456', '%d-%m-%Y') as '今天',
    	   str_to_date('2022', '%Y') as '今年';

![db15e213080a408093831388503a6ea7.png][]

select str_to_date('221322','%H%i%s') as '现在1',
    	   str_to_date('22:13:22','%H:%i:%s') as '现在2';

![7c91c8a9e0a2463fa32de8e8be1f811d.png][]

select str_to_date('221322','%H%i%s') as '现在1',
    	   str_to_date('22:13:22','%H:%i:%s') as '现在2',
    	   str_to_date('2022,7,26 22:13:22','%Y,%m,%e %H:%i:%s') as '现在3';

![42c1e3b9932048fbb8056019b76d2f39.png][]

### 2.date转varchar ###

<table> 
 <thead> 
  <tr> 
   <th align="left">函数</th> 
   <th align="left">格式</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><a href="https://www.w3school.com.cn/sql/func_date_format.asp" rel="nofollow"><strong>date_format</strong></a></td> 
   <td align="left"><code>DATE_FORMAT(date,format)</code></td> 
   <td align="left">date：合法的日期<br>format ：规定日期/时间的输出格式</td> 
  </tr> 
 </tbody> 
</table>

select date_format(now(), '%Y %m %d') as '今天',
    	   date_format(now(), '%Y-%m-%d %H:%i:%s') as '现在';

![63b00877107a41d6ae1fbe73a741d861.png][]

### 3.补充 ###

**获取某天某一时刻的字符串**

select date_format(trim(concat((select DATE_SUB(curdate(),INTERVAL 0 DAY)),' 00:00:00')), '%Y-%m-%d %H:%i:%s') as '今天凌晨',
    	   date_format(trim(concat((select DATE_SUB(curdate(),INTERVAL 1 DAY)),' 00:00:00')), '%Y-%m-%d %H:%i:%s') as '昨天凌晨',
    	   date_format(trim(concat((select DATE_SUB(curdate(),INTERVAL -1 DAY)),' 00:00:00')), '%Y-%m-%d %H:%i:%s') as '明天凌晨';

![5bef90cacefb4c9599fa6dac3b1dd5ab.png][]

[f4ac58fcdef94e578193d5a7f32955c6.png]: https://img-blog.csdnimg.cn/f4ac58fcdef94e578193d5a7f32955c6.png
[4580d18b5fab4670a9a7b73429a6eaf6.png]: https://img-blog.csdnimg.cn/4580d18b5fab4670a9a7b73429a6eaf6.png
[91793a6b8b214647b60390aecfd12be2.png]: https://img-blog.csdnimg.cn/91793a6b8b214647b60390aecfd12be2.png
[91bd0e040ce24a19bd49bc34b1c6a3c0.png]: https://img-blog.csdnimg.cn/91bd0e040ce24a19bd49bc34b1c6a3c0.png
[0ae7a671f16247afabe437bf02bd85c5.png]: https://img-blog.csdnimg.cn/0ae7a671f16247afabe437bf02bd85c5.png
[032611bd0a9f48d88692d6c2bd5199cd.png]: https://img-blog.csdnimg.cn/032611bd0a9f48d88692d6c2bd5199cd.png
[e100a7b56c9f4e5d81420d7b49caf0a6.png]: https://img-blog.csdnimg.cn/e100a7b56c9f4e5d81420d7b49caf0a6.png
[6af846e84c6e4ab2aa3c2837778618ef.png]: https://img-blog.csdnimg.cn/6af846e84c6e4ab2aa3c2837778618ef.png
[7665c912cd3f402fbaaac1262064794c.png]: https://img-blog.csdnimg.cn/7665c912cd3f402fbaaac1262064794c.png
[d73eee276c4d4694805ffc608243d4e0.jpeg]: https://img-blog.csdnimg.cn/d73eee276c4d4694805ffc608243d4e0.jpeg
[a530d58c259f40ae976001c87548539d.png]: https://img-blog.csdnimg.cn/a530d58c259f40ae976001c87548539d.png
[db15e213080a408093831388503a6ea7.png]: https://img-blog.csdnimg.cn/db15e213080a408093831388503a6ea7.png
[7c91c8a9e0a2463fa32de8e8be1f811d.png]: https://img-blog.csdnimg.cn/7c91c8a9e0a2463fa32de8e8be1f811d.png
[42c1e3b9932048fbb8056019b76d2f39.png]: https://img-blog.csdnimg.cn/42c1e3b9932048fbb8056019b76d2f39.png
[63b00877107a41d6ae1fbe73a741d861.png]: https://img-blog.csdnimg.cn/63b00877107a41d6ae1fbe73a741d861.png
[5bef90cacefb4c9599fa6dac3b1dd5ab.png]: https://img-blog.csdnimg.cn/5bef90cacefb4c9599fa6dac3b1dd5ab.png