Looking for events in the last 30 days
Another common task is searching for rows that belong to a specific date range. In principle, this is not difficult if the start and end dates of the range are fixed: SQL can compare dates just as well as numbers.
Comparison operators can be applied to dates: < , > , <= , etc. In addition, there is a special BETWEEN operator that can also work with dates. For example, let's write a query that will select all tasks from the task table that need to be done in the summer of 2022. The request will look like this:
SELECT * FROM task
WHERE task.deadline BETWEEN '2022-06-01' AND '2022-08-31'
The result of this query will be:
| id | emploee_id | name | deadline |
|---|---|---|---|
| 1 | 1 | Fix a bug on the frontend | 2022-06-01 |
| 2 | 2 | Fix a bug on the backend | 2022-06-15 |
| 3 | 5 | Buy coffee | 2022-07-01 |
| 4 | 5 | Buy coffee | 2022-08-01 |
But what to write in the request if we want to get tasks, for example, for the last 30 days. Where the last 30 days are directly tied to the query execution time. That is, if the query is executed on July 25, then you need to select dates from June 25 to July 24. How to bind a date range to the current time?
To do this, use the date addition function:
DATE_ADD (date, INTERVAL expression units)I think it will be easier to explain with a few examples:
| # | Request | Result |
|---|---|---|
| 1 | DATE_ADD('2022-06-04', INTERVAL 5 DAY) | 2022-06-09 |
| 2 | DATE_ADD('2022-06-04', INTERVAL 4 WEEK) | 2022-07-02 |
| 3 | DATE_ADD('2022-06-04', INTERVAL 2 MONTH) | 2022-08-04 |
| 4 | ||
| 5 | DATE_SUB('2022-06-04', INTERVAL 5 DAY) | 2022-05-30 |
| 6 | DATE_SUB('2022-06-04', INTERVAL 4 WEEK) | 2022-05-07 |
| 7 | DATE_SUB('2022-06-04', INTERVAL 2 MONTH) | 2022-04-04 |
By the way, a certain number of days, weeks, months and years can also be subtracted from a certain date.
And if we return to our request - to get data for the last 30 days, then we need two dates in this range:
- CURDATE() - end of range
- DATE_SUB( CURDATE() , INTERVAL 30 DAY) - start of range
The request will look like this:
SELECT * FROM task WHERE task.deadline
BETWEEN DATE_SUB( CURDATE() , INTERVAL 30 DAY) AND CURDATE()
The result of this query will be like this (I have today June 4, 2022):
| id | emploee_id | name | deadline |
|---|---|---|---|
| 1 | 1 | Fix a bug on the frontend | 2022-06-01 |
How to find the difference between two dates
And another useful feature. Sometimes you may need to calculate the difference between two dates in days. To do this, SQL has a special function - DATEDIFF ().
It is very easy to work with her:
DATEDIFF (date 1, date 2)The second date is simply subtracted from the first date. If date2 comes after date1, then the result will be negative.
By the way, we can rewrite our previous query using the DATEDIFF() function. We need to select all tasks from the task table whose deadline differs from the current date by no more than 30 days. Here's what that request would look like:
SELECT * FROM task WHERE DATEDIFF(task.deadline, CURDATE() ) < 30
The result of this query will be like this (I have today June 4, 2022):
| id | emploee_id | name | deadline |
|---|---|---|---|
| 1 | 1 | Fix a bug on the frontend | 2022-06-01 |
| 2 | 2 | Fix a bug on the backend | 2022-06-15 |
| 3 | 5 | Buy coffee | 2022-07-01 |
This time, SQL found as many as three rows. Think about why last time there was one line, and this time there are three?
GO TO FULL VERSION