mysql 根据日期时间查询数据

Author： cfscc
发布时间：August 24, 2020
1151 views
8397 words
Categories：程序代码

mysql 根据日期时间查询数据
 

复制代码
mysql> select * from table1;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
6 rows in set (0.01 sec)

mysql> select * from table1 where minute(order_date)=46;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
4 rows in set (0.00 sec)

mysql> select * from table1 where second(order_date) between 2 and 12;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
4 rows in set (0.00 sec)

mysql> select * from table1 where second(order_date) between 2 and 9;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
3 rows in set (0.00 sec)
复制代码
 

复制代码
mysql> select * from table1;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
6 rows in set (0.01 sec)



mysql> select * from table1 where date(order_date)='2019-08-04';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
6 rows in set (0.00 sec)



mysql> select * from table1 where date(order_date) between '2019-08-04' and '2019-08-04';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
6 rows in set (0.00 sec)



mysql> select * from table1 where order_date between '2019-08-04 20:45:47' and '2019-08-04 20:46:08';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
5 rows in set (0.00 sec)



mysql> select * from table1 where time(order_date) between '20:45:47' and '20:46:08';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
5 rows in set (0.00 sec)



mysql> select * from table1 where time(order_date)='20:46:02';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
+----------+------------+-----+---------------------+
1 row in set (0.00 sec)
复制代码
 

复制代码
mysql> select * from table1 where time(order_date) between "20:45:47" and "20:46:08";
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
5 rows in set (0.00 sec)
复制代码

##查询最近的一条信息

SELECT column_name(s) FROM table_name WHERE column_name = pattern ORDER BY create_time DESC LIMIT 1;

在某些情况下，如果明知道查询结果只有一个，SQL语句中使用LIMIT 1 会提高查询效率，避免全表扫描。

 

##查询今天

SELECT column_name(s) FROM table_name WHERE DATE_FORMAT( create_time,'%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');

DATE_FORMAT()函数用于以不同的格式显示日期/时间数据

DATE_FORMAT(date,format)

date参数是合法的日期。format规定日期/时间的输出格式

NOW()函数返回当前的日期和时间



 

SELECT column_name(s) FROM table_name WHERE TO_DAYS(create_time) =TO_DAYS(NOW());

 

SELECT colum_name(s) FROM table_name WHERE DATE(create_time) =DATE(CURDATE());

DATE()提取日期或日期/时间表达式的日期部分

 



 

 



 

##查询昨天

SELECT column_name(s) FROM table_name WHERE DATE_FORMAT( create_time,'%Y-%m-%d') = DATE_FORMAT(CURDATE()-1,'%Y-%m-%d');

SELECT column_name(s) FROM table_name WHERE DATE(create_time) =DATE(CURDATE()-1);

 

##输出格式：2017-04-14

SELECT CURDATE();

 

##输出格式：2017-04-14 13:44:11

SELECT NOW();

 

##查询一个星期内的数据

## DATE_SUB(date,INTERVAL expr unit) 从日期减去指定的时间间隔

SELECT column_name(s) FROM table_name WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <=DATE(create_time) ORDER BY create_time DESC;

 

##查询一个月内的数据

SELECT column_name(s) FROM table_name WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(create_time) ORDER BY create_time DESC;

 

##返回date的星期索引(1 = Sunday, 2 = Monday, ... 7 = Saturday)。索引值符合 ODBC 的标准

SELECT DAYOFWEEK(create_time) FROM table_name ORDER BY create_time DESC;

 

##格式化时间

SELECT DATE_FORMAT(create_time,'%Y-%m-%d') FROM table_name ;

 

##返回当前时间 输出格式：15:17:51

SELECT CURTIME();

 

##给日期添加指定的时间间隔  DATE_ADD(date,INTERVAL expr unit)

SELECT column_name(s) FROM table_name WHERE DATE_ADD(create_time,INTERVAL 1 DAY) <= DATE(NOW());

 

##返回两个日期之间的天数 DATEDIFF(expr1,expr2)

SELECT column_name(s) FROM table_name WHERE DATEDIFF( NOW(),create_time)=0

 

=0代表查询的是当天，=1 查询的就是昨天……

假如有个表product有个字段add_time,它的数据类型为datetime,有人可能会这样写sql：

select * from product where add_time = '2013-01-12'
对于这种语句，如果你存储的格式是YY-mm-dd是这样的，那么OK，如果你存储的格式是：2013-01-12 23:23:56这种格式你就悲剧了，这是你就可以使用DATE()函数用来返回日期的部分，所以这条sql应该如下处理：

select * from product where Date(add_time) = '2013-01-12'

如果按照日期+时间模式（2013-01-12 23:23:56—时间精确到秒）查询数据库，则可以使用timestamp()函数来查询：

select * from product where timestamp(add_time) = '2013-01-12 23:23:56'；
或者查询一个时间范围，可以用：

select * from product where timestamp(add_time) between '2013-01-12 03:23:56' and '2013-01-12 23:23:56'；
或者

select * from product where timestamp(add_time) >= '2013-01-12 03:23:56' < '2013-01-12 23:23:56'；
再来一个，如果你要查询2013年1月份加入的产品呢？


select * from product where date(add_time) between '2013-01-01' and '2013-01-31'
你还可以这样写：

select * from product where Year(add_time) = 2013 and Month(add_time) = 1

这些你该知道mysql日期函数在对你处理日期比较问题的作用了吧？

其date_col的值是在最后30天以内：


mysql> SELECT something FROM table 
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

| mysql> select DAYOFWEEK('1998-02-03'); 
-> 3 |
WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

| mysql> select WEEKDAY('1997-10-04 22:23:00'); 
-> 5 
mysql> select WEEKDAY('1997-11-05'); 
-> 2 |
DAYOFMONTH(date)
返回date的月份中日期，在1到31范围内。

| mysql> select DAYOFMONTH('1998-02-03'); 
-> 3 |
DAYOFYEAR(date)
返回date在一年中的日数, 在1到366范围内。

| mysql> select DAYOFYEAR('1998-02-03'); 
-> 34 |
MONTH(date)
返回date的月份，范围1到12。

| mysql> select MONTH('1998-02-03'); 
-> 2 |
DAYNAME(date)
返回date的星期名字。


| mysql> select DAYNAME("1998-02-05"); 
-> 'Thursday' |
MONTHNAME(date)
返回date的月份名字。

| mysql> select MONTHNAME("1998-02-05"); 
-> 'February' |
QUARTER(date)
返回date一年中的季度，范围1到4。

| mysql> select QUARTER('98-04-01'); 
-> 2 |

作者：shenyoujian
链接：https://www.jianshu.com/p/9305e8698b52
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

Last modification：August 24, 2020

如果觉得我的文章对你有用，请随意赞赏

mysql 根据日期时间查询数据

cfscc • 2020 年 08 月 24 日

<pre><code class="lang-cs">mysql 根据日期时间查询数据

复制代码
mysql&gt; select * from table1;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
6 rows in set (0.01 sec)

mysql&gt; select * from table1 where minute(order_date)=46;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
4 rows in set (0.00 sec)

mysql&gt; select * from table1 where second(order_date) between 2 and 12;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
4 rows in set (0.00 sec)

mysql&gt; select * from table1 where second(order_date) between 2 and 9;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
3 rows in set (0.00 sec)
复制代码

mysql&gt; select * from table1 where date(order_date)='2019-08-04';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
6 rows in set (0.00 sec)

mysql&gt; select * from table1 where date(order_date) between '2019-08-04' and '2019-08-04';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
| 3hahha   | lee        |   6 | 2019-08-04 20:46:12 |
+----------+------------+-----+---------------------+
6 rows in set (0.00 sec)

mysql&gt; select * from table1 where order_date between '2019-08-04 20:45:47' and '2019-08-04 20:46:08';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
5 rows in set (0.00 sec)

mysql&gt; select * from table1 where time(order_date) between '20:45:47' and '20:46:08';
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
5 rows in set (0.00 sec)

复制代码
mysql&gt; select * from table1 where time(order_date) between &quot;20:45:47&quot; and &quot;20:46:08&quot;;
+----------+------------+-----+---------------------+
| name_new | transactor | pid | order_date          |
+----------+------------+-----+---------------------+
| 1hahha   | 1xiaohong  |   1 | 2019-08-04 20:45:47 |
| 2hahha   | 2xiaohong  |   2 | 2019-08-04 20:45:57 |
| 3hahha   | 3xiaohong  |   3 | 2019-08-04 20:46:02 |
| 4hahha   | 4xiaohong  |   4 | 2019-08-04 20:46:05 |
| 3hahha   | bob        |   5 | 2019-08-04 20:46:08 |
+----------+------------+-----+---------------------+
5 rows in set (0.00 sec)
复制代码</code></pre><p><img src="https://img-blog.csdn.net/20180526182554505" alt="" title="" style=""></p><pre><code class="lang-cs">##查询最近的一条信息

SELECT column_name(s) FROM table_name WHERE column_name = pattern ORDER BY create_time DESC LIMIT 1;

在某些情况下，如果明知道查询结果只有一个，SQL语句中使用LIMIT 1 会提高查询效率，避免全表扫描。

##查询今天

SELECT column_name(s) FROM table_name WHERE DATE_FORMAT( create_time,'%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');

DATE_FORMAT()函数用于以不同的格式显示日期/时间数据

DATE_FORMAT(date,format)

date参数是合法的日期。format规定日期/时间的输出格式

NOW()函数返回当前的日期和时间

SELECT column_name(s) FROM table_name WHERE TO_DAYS(create_time) =TO_DAYS(NOW());

SELECT colum_name(s) FROM table_name WHERE DATE(create_time) =DATE(CURDATE());

DATE()提取日期或日期/时间表达式的日期部分

##查询昨天

SELECT column_name(s) FROM table_name WHERE DATE_FORMAT( create_time,'%Y-%m-%d') = DATE_FORMAT(CURDATE()-1,'%Y-%m-%d');

SELECT column_name(s) FROM table_name WHERE DATE(create_time) =DATE(CURDATE()-1);

##输出格式：2017-04-14

SELECT CURDATE();

##输出格式：2017-04-14 13:44:11

SELECT NOW();

##查询一个星期内的数据

## DATE_SUB(date,INTERVAL expr unit) 从日期减去指定的时间间隔

SELECT column_name(s) FROM table_name WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) &lt;=DATE(create_time) ORDER BY create_time DESC;

##查询一个月内的数据

SELECT column_name(s) FROM table_name WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) &lt;= DATE(create_time) ORDER BY create_time DESC;

##返回date的星期索引(1 = Sunday, 2 = Monday, ... 7 = Saturday)。索引值符合 ODBC 的标准

SELECT DAYOFWEEK(create_time) FROM table_name ORDER BY create_time DESC;

##格式化时间

SELECT DATE_FORMAT(create_time,'%Y-%m-%d') FROM table_name ;

##返回当前时间 输出格式：15:17:51

SELECT CURTIME();

##给日期添加指定的时间间隔  DATE_ADD(date,INTERVAL expr unit)

SELECT column_name(s) FROM table_name WHERE DATE_ADD(create_time,INTERVAL 1 DAY) &lt;= DATE(NOW());

##返回两个日期之间的天数 DATEDIFF(expr1,expr2)

SELECT column_name(s) FROM table_name WHERE DATEDIFF( NOW(),create_time)=0

=0代表查询的是当天，=1 查询的就是昨天……</code></pre><pre><code class="lang-cs">假如有个表product有个字段add_time,它的数据类型为datetime,有人可能会这样写sql：

select * from product where add_time = '2013-01-12'
对于这种语句，如果你存储的格式是YY-mm-dd是这样的，那么OK，如果你存储的格式是：2013-01-12 23:23:56这种格式你就悲剧了，这是你就可以使用DATE()函数用来返回日期的部分，所以这条sql应该如下处理：

select * from product where Date(add_time) = '2013-01-12'

如果按照日期+时间模式（2013-01-12 23:23:56—时间精确到秒）查询数据库，则可以使用timestamp()函数来查询：

select * from product where timestamp(add_time) = '2013-01-12 23:23:56'；
或者查询一个时间范围，可以用：

select * from product where timestamp(add_time) between '2013-01-12 03:23:56' and '2013-01-12 23:23:56'；
或者

select * from product where timestamp(add_time) &gt;= '2013-01-12 03:23:56' &lt; '2013-01-12 23:23:56'；
再来一个，如果你要查询2013年1月份加入的产品呢？

select * from product where date(add_time) between '2013-01-01' and '2013-01-31'
你还可以这样写：

select * from product where Year(add_time) = 2013 and Month(add_time) = 1

这些你该知道mysql日期函数在对你处理日期比较问题的作用了吧？

其date_col的值是在最后30天以内：

mysql&gt; SELECT something FROM table 
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) &lt;= 30;
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

| mysql&gt; select DAYOFWEEK('1998-02-03'); 
-&gt; 3 |
WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

| mysql&gt; select WEEKDAY('1997-10-04 22:23:00'); 
-&gt; 5 
mysql&gt; select WEEKDAY('1997-11-05'); 
-&gt; 2 |
DAYOFMONTH(date)
返回date的月份中日期，在1到31范围内。

| mysql&gt; select DAYOFMONTH('1998-02-03'); 
-&gt; 3 |
DAYOFYEAR(date)
返回date在一年中的日数, 在1到366范围内。

| mysql&gt; select DAYOFYEAR('1998-02-03'); 
-&gt; 34 |
MONTH(date)
返回date的月份，范围1到12。

| mysql&gt; select MONTH('1998-02-03'); 
-&gt; 2 |
DAYNAME(date)
返回date的星期名字。

| mysql&gt; select DAYNAME(&quot;1998-02-05&quot;); 
-&gt; 'Thursday' |
MONTHNAME(date)
返回date的月份名字。

| mysql&gt; select MONTHNAME(&quot;1998-02-05&quot;); 
-&gt; 'February' |
QUARTER(date)
返回date一年中的季度，范围1到4。

| mysql&gt; select QUARTER('98-04-01'); 
-&gt; 2 |

作者：shenyoujian
链接：https://www.jianshu.com/p/9305e8698b52
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。</code></pre>