Use of MySQL DATE_FORMAT function

Suppose Taobao encourages people to shop during Double 12 by rewarding the two users who placed the most orders on Double 11: User 1: “Chen Haha, the Shopping Emperor” and User 2: “Liu Dali, the Tentacle Monster” with RMB 10,000 each;

Requirement 1: You are asked to use the MySQL order table to count the number of purchase orders placed by these two people every hour on Double 11. How would you write this SQL?

I remember when I first came into contact with MySQl a few years ago, I was young and frivolous. When I learned that there was no need to consider efficiency, I wrote an interface that looped twenty-four times and sent 24 SQL statements to check (covering my face). Because of that interface, I was ridiculed by the technical manager~~ He said that he had written more SQL statements than I had eaten rice. Although we Shandong people basically don't eat rice, I still feel ashamed. .

Then the manager calls a DATE_FORMAT function to process the group query, and everything is OK. The efficiency is dozens of times higher than mine. From then on, I secretly planned to study SQL skills in depth.

The next day, I played two rounds of King of Glory with my friends, so the plan was postponed for a few years.

In MySQL, there is a specially encapsulated DATE_FORMAT function for processing time fields. It can be said that the DATE_FORMAT function can basically meet the processing requirements of any time field.

DATE_FORMAT(date,format) function

Parameter analysis:

1. date: represents a specific time field, or can be now() to query the current time;
2. format: DATE_FORMAT converts the incoming Date type data into the format you need, such as %Y-%m-%d %H:%i:%s will convert the incoming Time data into the "yyyy-MM-dd HH:mm:ss" format
%Y-%m-%d %H:%i:%s corresponds to yyyy-MM-dd HH:mm:ss and is also the most commonly used format. Here are a few simple examples:

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- Result: 2020-12-07 22:18:58
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'); -- Result: 2020-12-07 22:18
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H'); -- Result: 2020-12-07 22
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); -- Result: 2020-12-07
SELECT DATE_FORMAT(NOW(),'%H:%i:%s'); -- Result: 22:18:58
SELECT DATE_FORMAT(NOW(),'%H'); -- Result: 22

For requirement 1 above, the query method using the DATE_FORMAT function is as follows:

SELECT name as 'user name', DATE_FORMAT(createTime,'%Y-%m-%d %H') as 'time/hour', count(*) as 'order volume' from t_order 
 where DATE_FORMAT(createTime,'%Y-%m-%d') = '2020-11-11' 
  GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H');

Query results:

mysql> SELECT name as 'user name', DATE_FORMAT(createTime,'%Y-%m-%d %H') as 'time/hour', count(*) as 'order volume' from t_order where DATE_FORMAT(createTime,'%Y-%m-%d') = '2020-11-11' GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H');
+------------------+---------------+-----------+
| Username| Time/Hour| Order Volume|
+------------------+---------------+-----------+
| Shopping Emperor Chen Haha | 2020-11-11 00 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 01 | 10 |
| Shopping Emperor Chen Haha | 2020-11-11 02 | 6 |
| Shopping Emperor Chen Haha | 2020-11-11 03 | 3 |
| Shopping Emperor Chen Haha | 2020-11-11 04 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 05 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 06 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 07 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 08 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 09 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 10 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 11 | 0 |
| Shopping Emperor Chen Haha | 2020-11-11 12 | 12 |
| Shopping Emperor Chen Haha | 2020-11-11 13 | 6 |
| Shopping Emperor Chen Haha | 2020-11-11 14 | 3 |
| Shopping Emperor Chen Haha | 2020-11-11 15 | 7 |
| Shopping Emperor Chen Haha | 2020-11-11 16 | 2 |
| Shopping Emperor Chen Haha | 2020-11-11 17 | 3 |
| Shopping Emperor Chen Haha | 2020-11-11 18 | 11 |
| Shopping Emperor Chen Haha | 2020-11-11 19 | 2 |
| Shopping Emperor Chen Haha | 2020-11-11 20 | 1 |
| Shopping Emperor Chen Haha | 2020-11-11 21 | 3 |
| Shopping Emperor Chen Haha | 2020-11-11 22 | 2 |
| Shopping Emperor Chen Haha | 2020-11-11 23 | 0 |
+------------------+---------------+-----------+
24 rows in set (0.00 sec)

Here, GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H') represents the combination of "year, month, day + hour", specifically the hour of a certain day;

Of course, it can also be written in the following two forms, both divided by hours, but...:

1. GROUP BY DATE_FORMAT(createTime,'%H')
2. GROUP BY HOUR(createTime)
It should be noted that when the where condition specifies a certain day, these three functions are the same, but if the where condition does not specify a certain day, it will be very different. Let's take a look at the query results;

SELECT name as 'user name', DATE_FORMAT(createTime,'%H') as 'time/hour', count(*) as 'order volume' from t_order
 GROUP BY DATE_FORMAT(createTime,'%H');

Query results

mysql> SELECT name as 'user name', DATE_FORMAT(createTime,'%H') as 'time/hour', count(*) as 'order volume' from t_order GROUP BY DATE_FORMAT(createTime,'%H');
+-----------------+---------------+-----------+
| Username| Time/Hour| Order Volume|
+-----------------+---------------+-----------+
| Tentacle Monster Liu Dali | 00 | 11 |
| Tentacle Monster Liu Dali | 01 | 302 |
| Tentacle Monster Liu Dali | 02 | 277 |
| Tentacle Monster Liu Dali | 03 | 122 |
| Tentacle Monster Liu Dali | 04 | 6 |
| Tentacle Monster Liu Dali | 05 | 11 |
| Tentacle Monster Liu Dali | 06 | 0 |
| Tentacle Monster Liu Dali | 07 | 0 |
| Tentacle Monster Liu Dali | 08 | 1 |
| Tentacle Monster Liu Dali | 09 | 4 |
| Tentacle Monster Liu Dali | 10 | 5 |
| Tentacle Monster Liu Dali | 11 | 92 |
| Tentacle Monster Liu Dali | 12 | 1937 |
| Tentacle Monster Liu Dali | 13 | 1602 |
| Tentacle Monster Liu Dali | 14 | 108 |
| Tentacle Monster Liu Dali | 15 | 78 |
| Tentacle Monster Liu Dali | 16 | 110 |
| Tentacle Monster Liu Dali | 17 | 108 |
| Tentacle Monster Liu Dali | 18 | 138 |
| Tentacle Monster Liu Dali | 19 | 66 |
| Tentacle Monster Liu Dali | 20 | 44 |
| Tentacle Monster Liu Dali | 21 | 59 |
| Tentacle Monster Liu Dali | 22 | 21 |
| Tentacle Monster Liu Dali | 23 | 8 |
+-----------------+---------------+-----------+
24 rows in set (0.01 sec)

From the query results, we can see that the data found is the distribution of all the user's historical orders in each hour. DATE_FORMAT(createTime,'%H') represents the hour of any day, which is equivalent to GROUP BY HOUR(createTime).

With these data, I believe that students who write recommendation algorithms will know which time period will be most effective in promoting advertisements to "Tentacle Monster Liu Dali".

This is the end of this article about the use of MySQL DATE_FORMAT function. For more relevant MySQL DATE_FORMAT content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: