How to use time as a judgment condition in MySQL

Background: During the development process, we often need to query data based on time as a judgment condition, such as: the current month, the current day, the current hour, the next few days...

1. Current month

We only need to use a MySQL MONTH(date) function to achieve this. (Note the year)

MONTH(date);
-- Usage: The MONTH function returns an integer representing the month of the specified date value.

-- Example
SELECT MONTH('2020-11-11 00:00:00')
-- The return value is 11

2. Within 30 days

The reason why I put "within 30 days" after the current month is because I often encounter situations where these two requirements are converted into each other. "Within 30 days" can also be called "within a month."

In this case we need to use the DATEDIFF(expr1,expr2) function.

DATEDIFF(expr1,expr2)
-- Usage: The parameters are two dates, and the difference in days between expr1 and expr2 is returned.

-- Example
SELECT DATEDIFF('2020-11-01 08:00:00','2020-11-11 00:00:00')
-- The return value is -10

3. On the day

The TO_DAYS(date) function needs to be used for the current day.

TO_DAYS(date)
-- Usage: Returns the total number of days from 0000 (1 AD) to the current date.

-- Example
SELECT TO_DAYS('0000-01-01')
-- The return value is 1
SELECT TO_DAYS('0001-01-01')
-- The return value is 366

4. Current hour

In this case, the HOUR(date) and CURDATE() functions need to be used together.

HOUR(date)
-- Usage: Returns the current time as the hour of today
-- Example
SELECT HOUR('2020-11-11 11:11:11')
-- The return value is 11

CURDATE()
-- Usage: Return today's date, excluding hours, minutes and seconds, yyyy-MM-dd

-- Usage examples
select * from table where created_at > CURDATE() and HOUR(created_at) = HOUR(now())

5. Within x days

This can be achieved using the DATE_SUB(date,INTERVAL expr unit) function.

DATE_SUB(date,INTERVAL expr unit)
-- Usage: start date minus a time period

There are many values ​​for the unit behind it, as shown in the following table:

-- Example
-- Data query within seven days
select * from table where created_at > DATE_SUB(CURDATE(),INTERVAL 7 DAY)

6. Data statistics within a few days

We often encounter this requirement, which is to count the amount of data every day within 7 days. In this case, we need to consider the case where there is no data push, that is, we must get it even if it is 0.

My thoughts are as follows:

SELECT
	 DATE_SUB( CURDATE(), INTERVAL ms day ) AS orderDate
FROM
	(
  SELECT 0 as s UNION ALL
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4 UNION ALL
  SELECT 5 UNION ALL
  SELECT 6 
	)

Use the above SQL query results as a temporary table to match the data table to count the number of data within a certain period of time

-- Example: Query the number of data in each month within 12 months SELECT COUNT(t.created_at),res.date FROM 
	(SELECT
	 DATE_FORMAT(DATE_SUB( CURDATE(), INTERVAL ms MONTH ),'%Y-%m') AS date
FROM
	(
  SELECT 0 as s UNION ALL
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4 UNION ALL
  SELECT 5 UNION ALL
  SELECT 6 UNION ALL
  SELECT 7 UNION ALL
  SELECT 8 UNION ALL
  SELECT 9 UNION ALL
  SELECT 10 UNION ALL
  SELECT 11 
	) m)res
	left join 
	table t on res.date = DATE_FORMAT(t.created_at,'%Y-%m')
	GROUP BY date

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.