Retrieving Data From the Last N Days in SQL

1. Introduction

Extracting data for specific periods is frequently necessary in data analysis and reporting. One common requirement is to fetch data from the last N days.

In this tutorial, we’ll explore various SQL techniques for retrieving data from the last N days, covering SQL Server, MySQL, and PostgreSQL, as well as different data types such as date and timestamp.

2. Understanding the Problem

The key to retrieving recent data is filtering records based on date and time. Most databases store timestamps in a standardized format, and SQL provides robust functions for manipulating and comparing these dates effectively.

The general strategy for fetching data from the last N days involves three main steps:

• Identifying the current date
• Calculating the threshold date by subtracting N days from the current date
• Filtering records where the date is greater than or equal to this threshold date

3. Model and Data

We’ll use the Student and Registration tables from our University database to discuss the queries. The enrollment_date column in the Student table is of type date, while the reg_datetime column in the Registration table is of type timestamp or datetime. We’ll use these two columns in our examples to illustrate the queries.

4. PostgreSQL

In PostgreSQL, we can use the INTERVAL keyword on a date column to define the period for the query:

SELECT id, name, enrollment_date
FROM Student
WHERE enrollment_date >= CURRENT_DATE - INTERVAL '10 days';

We subtracted ten days from the specified date to filter the records based on the enrollment_date.

The syntax INTERVAL ’10 days’ in PostgreSQL defines a time interval of 10 days. We can use other periods, such as weeks or months, or a combination, such as ‘2 months 3 days’.

We can apply the interval to a timestamp column just as we did with the date column. Let’s write the query for the reg_datetime column in the Registration table:

SELECT *
FROM Registration
WHERE reg_datetime >= CURRENT_TIMESTAMP - INTERVAL '10 days';

In this case, we used INTERVAL and CURRENT_TIMESTAMP to build the query.

5. MySQL

In MySQL, we can use a combination of INTERVAL and DATE_SUB. Let’s see again what that looks like with a date column:

SELECT id, name, enrollment_date
FROM Student
WHERE enrollment_date >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

We can use INTERVAL 10 DAY to define a period, and similarly, other units such as WEEK, MONTH, and so on can be used with INTERVAL. The DATE_SUB function subtracts the specified interval from a given date, such as CURRENT_DATE.

Similarly, we can apply the same logic to the same timestamp column from earlier:

SELECT *
FROM Registration
WHERE reg_datetime >= DATE_SUB(NOW(), INTERVAL 10 DAY);

Instead of CURRENT_DATE, we used NOW() since the field is a type timestamp. Even though MySQL and PostgreSQL support the INTERVAL keyword, the syntax differs.

6. SQL Server

Now, let’s look at how we can achieve this in SQL Server. For the date column it looks like:

SELECT id, name, enrollment_date
FROM Student
WHERE enrollment_date >= DATEADD(DAY, -10, CONVERT(date, GETDATE()));

Here, we use the DATEADD function to construct the threshold date. The DATE_ADD function takes three parameters:

1. the time unit
2. the value to add
3. the date to which this value should be added

Since SQL Server doesn’t provide a function to directly get the current date without the time component, we use the CONVERT function to extract just the date part from the current timestamp.

Ant now, let’s apply the same logic to the datetime column:

SELECT *
FROM Registration
WHERE reg_datetime >= DATEADD(DAY, -10, GETDATE());

In this case, we don’t need to use the CONVERT function as the column is of type datetime. Alternatively, we can use the DATEDIFF function to achieve the same:

SELECT *
FROM Registration
WHERE DATEDIFF(DAY, reg_datetime, GETDATE()) <= 10;

7. Conclusion

In this article, we explored how to retrieve rows from the last N days using SQL across different databases, including SQL Server, MySQL, and PostgreSQL. We discussed techniques for date and timestamp columns, providing practical examples to filter data based on recent timeframes.