Query to Retrieve Rows With Datetime Column Equal to Today in SQL

1. Introduction

When working with SQL databases, it’s a common requirement to get specific subsets of data based on date and time, such as retrieving all records where a DATETIME column matches today’s date. This type of filtering is beneficial for tasks like generating daily reports, tracking user activity within the current day, or monitoring real-time system logs.

In this tutorial, we’ll look at how to do this in PostgreSQL, MySQL, and SQL Server.

2. Model and Data

We’ll use the Registration table from our University database to explore the queries. Let’s update some rows in the database to use the current timestamp:

UPDATE Registration SET reg_datetime = current_timestamp WHERE id = 1;
UPDATE Registration SET reg_datetime = current_timestamp WHERE id = 101;
UPDATE Registration SET reg_datetime = current_timestamp WHERE id = 201;

3. PostgreSQL

In this section, we explore several ways in PostgreSQL to retrieve the details of all the registrations that are done on the current day.

3.1. Using BETWEEN Clause

First, we can use the BETWEEN clause to get all rows where the reg_datetime is greater than the start of the current day and less than the start of the next day:

SELECT *
FROM Registration
WHERE reg_datetime BETWEEN CURRENT_DATE 
AND (CURRENT_DATE + INTERVAL '1 day' - INTERVAL '1 microsecond');

We used CURRENT_DATE and INTERVAL to create start and end values for the filter. To include only the current day, we subtract one microsecond from the end, as BETWEEN is inclusive.

Alternatively, we can use the > and < operators for the same result:

SELECT *
FROM Registration
WHERE reg_datetime > CURRENT_DATE 
AND reg_datetime < (CURRENT_DATE + INTERVAL '1 day');

If we use the < operator, we don’t need to subtract anything, as the endpoint of the isn’t included in the so-defined interval.

3.2. Using CAST()

Further, we can use the CAST() function to convert a column type to another. In this case, we can use it to convert timestamps to date and then compare. Let’s look at the query:

SELECT *
FROM Registration
WHERE CAST(reg_datetime AS date) = CURRENT_DATE;

This converts the timestamp values to dates, ignoring the time component. While the query is concise and straightforward, the downside is that casting can prevent the use of column indexes, potentially leading to performance issues in large databases.

3.3. Using DATE_TRUNC()

Another approach is to use DATE_TRUNC() function:

SELECT *
FROM Registration
WHERE DATE_TRUNC('day', reg_datetime) = CURRENT_DATE;

The query uses DATE_TRUNC() to remove the time component from the timestamp, enabling direct comparison with the current date. This functions like CAST() but offers more control over the extracted part. However, this function also prevents using an index, making it less performant on large datasets.

4. MySQL

In this section, let’s look at achieving this in MySQL.

4.1. Using BETWEEN Clause

Similar to PostgreSQL, we can use the BETWEEN clause to filter the rows for the current date:

SELECT *
FROM Registration
WHERE reg_datetime BETWEEN CURRENT_DATE 
AND (CURRENT_DATE + INTERVAL 1 day - INTERVAL 1 microsecond);

Unlike PostgreSQL, the INTERVAL shouldn’t be wrapped in single quotes(‘).

We can also use < and > operators to achieve the same behavior:

SELECT *
FROM Registration
WHERE reg_datetime > CURRENT_DATE 
AND reg_datetime < (CURRENT_DATE + INTERVAL 1 day);

Both these approaches utilize indexes if they are available for the reg_datetime column. As a result, they’re efficient in large databases.

4.2. Using DATE()

Alternatively, we can use the DATE() function to extract the date part from a timestamp value and then compare it with the current date:

SELECT *
FROM Registration
WHERE DATE(reg_datetime) = CURRENT_DATE;

Although more concise, this query is inefficient in large databases because it bypasses indexes.

4.3. Using CAST()

Additionally, we can use the CAST() function to extract only the date part from the timestamp and compare:

SELECT *
FROM Registration
WHERE CAST(reg_datetime AS DATE) = CURRENT_DATE;

This works the same as CAST() in PostgreSQL.

While DATE() is specifically used to extract the date part from a timestamp, the CAST() function is more generic and can be applied to various data types.

5. SQL Server

Lastly, let’s look at various ways to get the same result in SQL Server.

5.1. Using BETWEEN

Unlike PostgreSQL and MySQL, creating a BETWEEN query in SQL Server is less straightforward due to the lack of INTERVAL syntax and a direct function to get the current date. However, we can still achieve the desired result:

SELECT *
FROM Registration
WHERE reg_datetime BETWEEN CAST(GETDATE() AS DATE) 
AND DATEADD(MILLISECOND, -3, 
DATEADD(DAY, 1, CAST(CONVERT(DATE, GETDATE()) AS DATETIME)));

As we can see, constructing the required query involved several functions such as DATEADD(), CAST(), CONVERT(), and so on.

In SQL Server, DATETIME has a 3-millisecond precision, which is why -3 milliseconds is used to include the entire day.

We can simplify this query by using the > and < operators instead of BETWEEN:

SELECT *
FROM Registration
WHERE reg_datetime >= CAST(GETDATE() AS DATE)
AND reg_datetime < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE);

This way, we can achieve the same result with a more readable query.

5.2. Using CAST()

We can cast the reg_datetime to a date type using the CAST() function and compare it with the current date:

SELECT *
FROM Registration
WHERE CAST(reg_datetime AS DATE) = CAST(GETDATE() AS DATE);

This is a simple query, but it may be inefficient on large databases due to the lack of index usage. This is quite similar to the queries used in both PostgreSQL and MySQL.

6. Summary

Let’s summarize the approaches discussed in this article:

7. Conclusion

In this article, we covered ways to retrieve rows with a DATETIME column equal to today’s date in PostgreSQL, MySQL, and SQL Server. While functions like CAST() or DATE() are simple, they may impact performance on large databases by limiting index usage. Using operators like BETWEEN, >, and < can be more efficient, avoiding column transformations and enabling better index use.