In Oracle database, the to_char() function is used to manipulate date variables, while in MySQL, date_format() function is used to count date-related cycles. The date_format() function takes two arguments: the date argument represents the date variable, and the format argument represents the date format.

If you want to view the date_format() function parameters and their values, you can query the date_format() function parameters in the search engine. For example, type “MYsql date_format” to see a detailed description of this function in W3school. www.w3school.com.cn/sql/func_da…

  • 1. Statistics by day The format parameter is set to ‘%y%m%d’, which displays statistics by day.
SELECT DATE_FORMAT(postDateTime,'%y%m%d') as d,count(*) FROM table GROUP BY DATE_FORMAT(postDateTime,'%y%m%d') ORDER BY d asc;Copy the code

The following output is displayed:

  • 2. Statistics by natural week When the format parameter of the format() function is set to ‘%y%u’, statistics can be collected by year or midweek. If the where condition limits the period of a particular year, you can simply set the format parameter to ‘%u’. Otherwise, you must use ‘%y%u’, otherwise the NTH week of different years will be merged together and cause confusion.
SELECT DATE_FORMAT(postDateTime,'%y year %u week ') as w,min(postDateTime) as st,count(*) FROM table GROUP BY DATE_FORMAT(postDateTime,'%y%u') ORDER BY w asc;Copy the code

The following output is displayed:

  • 3. Statistics by month When the format parameter of the format() function is set to ‘%y%m’, the summary results by month can be displayed.
SELECT DATE_FORMAT(postDateTime,'%y%m') as m,count(*) FROM table GROUP BY DATE_FORMAT(postDateTime,'%y%m') ORDER BY m ascCopy the code

The statistical results are as follows:

  • 4. The date_format() function does not directly output the results by season, but it is not difficult for the data analyst to use the monthly aggregate results to process the following:
SELECT FLOOR((DATE_FORMAT(postDateTime,'%m')-1)/3)+1 as q,min(postDateTime) as st,count(*)
FROM table
WHERE DATE_FORMAT(postDateTime,'%Y') = 2018
GROUP BY FLOOR((DATE_FORMAT(postDateTime,'%m')-1)/3)+1
ORDER BY q asc;
Copy the code

The floor function is used to determine the season according to the month and output the corresponding start month of the season.

The result is as follows:

  • 5. Statistics by year If the format parameter of the date_format() function is set to ‘%Y’ or ‘%Y’, statistics by year can be displayed.
SELECT DATE_FORMAT(postDateTime,'%Y') as y,count(*)
FROM table
GROUP BY DATE_FORMAT(postDateTime,'%Y')
ORDER BY y asc;
Copy the code

👏👏👏 take a look at our previous article 😃😃😃 🌺 Excel data analysis tool library – correlation coefficient 🌺 dry goods, hand in hand to teach you to do correlation analysis 🌺 5 years of data analysis road, summary. 🌺 user segmentation and portrait analysis 🌺 K-nearest neighbor algorithm and practice

Welcome to our wechat official account.Home of data analysts“

Scan the QR code to follow us

💁 provides career planning, resume guidance, interview counseling services

QQ communication group: 254674155