学习笔记——MYSQL时间日期函数

获取当前的时间

获取年月日时分秒

• select now();
• select sysdate();
  区别：now() 在执行开始时值就得到， sysdate() 在函数执行时动态得到值。

select now(),sleep(5),now();
+---------------------+----------+---------------------+
| now()               | sleep(5) | now()               |
+---------------------+----------+---------------------+
| 2020-07-25 14:25:20 |        0 | 2020-07-25 14:25:20 |
+---------------------+----------+---------------------+
1 row in set (5.02 sec)

select sysdate(),sleep(5),sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(5) | sysdate()           |
+---------------------+----------+---------------------+
| 2020-07-25 14:25:53 |        0 | 2020-07-25 14:25:58 |
+---------------------+----------+---------------------+
1 row in set (5.03 sec)

获取年月日

• select current_date(); 或 select curdate();

获取时分秒

• select current_time(); 或 select curtime();

注意：() 内表示保留多少位数字

时间格式的转换

字符串转换为日期格式

• str_to_date(时间字符串，字符串日期格式)

日期的格式

格式	描述
%Y	年，4 位
%y	年，2 位
%M	英文名称的月名（一月为January，二月为February）
%m	数值月份 (00-12)
%D	带有英文后缀的天（不太直观）
%d	数值天 (00-31)
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%S	秒(00-59)
%s	秒(00-59)
%a	英文缩写星期名
%b	英文缩写月名
%c	月，数值
%f	微秒
%p	显示是 AM 还是 PM
%r	显示时分秒时间，12小时制（hh:mm:ss AM 或 PM）
%T	显示时分秒时间, 24小时制 (hh:mm:ss)
%j	显示当前日期是今年的第几天 (001-366)

select str_to_date('08/09/2008', '%m/%d/%Y');
+---------------------------------------+
| str_to_date('08/09/2008', '%m/%d/%Y') |
+---------------------------------------+
| 2008-08-09                            |
+---------------------------------------+

select str_to_date('08.09.2008', '%m.%d.%Y');
+---------------------------------------+
| str_to_date('08.09.2008', '%m.%d.%Y') |
+---------------------------------------+
| 2008-08-09                            |
+---------------------------------------+

select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s');
+---------------------------------------------------------+
| str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s') |
+---------------------------------------------------------+
| 2008-08-09 08:09:30                                     |
+---------------------------------------------------------+

日期转换为特殊字符串形式

• date_format (日期，字符串格式)
  能够把一个日期转换为各种样式的字符串

 select date_format(now(),'%Y-%M-%d %H') ;
+----------------------------------+
| date_format(now(),'%Y-%M-%d %H') |
+----------------------------------+
| 2020-July-25 15                  |
+----------------------------------+

提取时间信息

直接用year、month等日期函数提取

时间函数名	作用
date	获取日期（年月日）
time	获取时间（时分秒）
year	获取年份
month	获取月份
day	获取日
hour	获取时
minute	获取分
second	获取秒
microsecond	获取毫秒
quarter	获取季度
week	获取周
weekofyear	获取周
dayofyear	日期在年度中第几天
dayofmonth	日期在月度中第几天
dayofweek	日期在周中第几天；周日为第一天
weekday	日期在周中第几天；周二为第一天
yearweek	年和周
dayname	返回英文星期
monthname	返回英文月份
last_day	返回月份中最后一天

例：
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2020-07-25  |
+-------------+
1 row in set (0.03 sec)

mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 16:02:01    |
+-------------+
1 row in set (0.02 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2020 |
+-------------+
1 row in set (0.01 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|         25 |
+------------+
1 row in set (0.02 sec)

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              207 |
+------------------+
1 row in set (0.02 sec)

日期的运算

对现有日期进行偏移

• date_sub(日期, interval 要减少偏移的间隔 类型)
• date_add(日期, interval 要增加偏移的间隔 类型)

时间函数名	作用
YEAR	增加减少年
MONTH	增加减少月
DAY	增加减少天
HOUR	增加减少小时
MINUTE	增加减少分钟
SECOND	增加减少秒
MICROSECOND	增加减少毫秒
WEEK	增加减少周
QUARTER	增加减少季度
SECOND_MICROSECOND	增加减少秒到毫秒
MINUTE_MICROSECOND	增加减少分钟到毫秒
MINUTE_SECOND	增加减少分钟到秒
HOUR_MICROSECOND	增加减少小时到毫秒
HOUR_SECOND	增加减少小时到秒
HOUR_MINUTE	增加减少小时到分钟
DAY_MICROSECOND	增加减少天到毫秒
DAY_SECOND	增加减少天到秒
DAY_MINUTE	增加减少天到分钟
DAY_HOUR	增加减少天到小时
YEAR_MONTH	增加减少年到月

select date_add('2008-08-08 10:12:33', interval '01:10:30' hour_second);
+------------------------------------------------------------------+
| date_add('2008-08-08 10:12:33', interval '01:10:30' hour_second) |
+------------------------------------------------------------------+
| 2008-08-08 11:23:03                                              |
+------------------------------------------------------------------+

select date_add('2008-08-08 10:12:33', interval '01:10:30' hour);
+-----------------------------------------------------------+
| date_add('2008-08-08 10:12:33', interval '01:10:30' hour) |
+-----------------------------------------------------------+
| 2008-08-08 11:12:33                                       |
+-----------------------------------------------------------+

两个日期计算天数差

• datediff (time1，time2）
  返回两个日期之间（time1-time2）的天数

select datediff('2008-08-08','2008-08-01');
+-------------------------------------+
| datediff('2008-08-08','2008-08-01') |
+-------------------------------------+
|                                   7 |
+-------------------------------------+

两个日期计算时间差

• timediff(time1,time2)
  两个日期相减 time1 - time2，返回 time 差值。
  注意：timediff(time1,time2) 函数的两个参数类型必须相同。

两个日期得到指定差（推荐）

• timestampdiff(unit,begin,end)
  返回end-begin的结果，其中begin和end是date或datetime格式

unit格式	unit实际含义
YEAR	年份
MONTH	月份
DAY	天
HOUR	小时
MINUTE	分钟
SECOND	秒
MICROSECOND	微秒
QUARTER	季度
WEEK	周、

select timestampdiff(year,'2002-05-01','2003-01-01');
+-----------------------------------------------+
| timestampdiff(year,'2002-05-01','2003-01-01') |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.00 sec)

select timestampdiff(year,'2002-05-01','2003-05-01');
+-----------------------------------------------+
| timestampdiff(year,'2002-05-01','2003-05-01') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+