CURDATE() and CURRENT_DATE() in MySQL 8: Explained with examples

Introduction

In the world of databases, keeping track of dates and times is crucial for a variety of applications such as logging events, analyzing trends or setting up schedules. In MySQL, there are several functions to manage date and time values, among which CURDATE() and CURRENT_DATE() are frequently used to retrieve the current date. In this tutorial, we’re going to go deep into understanding these two functions, their similarities, and differences, along with various code examples that range from the basic usage to some advanced scenarios.

Before we proceed, please ensure that you have MySQL 8 or later installed in your system as the functionalities discussed here may differ in previous versions. If you’re working with an older version of MySQL, some features or functionalities highlighted in this article may not work or may work differently.

Basics of CURDATE() and CURRENT_DATE()

The CURDATE() and the CURRENT_DATE() functions are quite simple – they both return the current date without the time component. The date is returned in ‘YYYY-MM-DD’ format, or ‘YYYYMMDD’ format depending on whether the function is used in a string context or numeric context respectively.

SELECT CURDATE();
-- Output: 2023-04-12

SELECT CURRENT_DATE();
-- Output: 2023-04-12

As you can see from the above examples, both functions return the same output, and they can be used interchangeably.

Using CURDATE() and CURRENT_DATE()

Now let us take a closer look at the different ways you can use these functions in your MySQL queries.

Comparing Dates

One of the common uses of CURDATE() and CURRENT_DATE() is to compare the current date with other dates in your database:

SELECT * FROM orders WHERE order_date = CURDATE();

SELECT * FROM user_logins WHERE last_login_date < CURRENT_DATE();

These queries give you the ability to retrieve data that matches today’s date or filter out records from previous days.

Calculating Age

Another interesting usage is for calculating the age of a person or the number of years an account has been active:

SELECT name, birthdate, (YEAR(CURDATE()) - YEAR(birthdate)) AS age FROM users;

SELECT account_name, created_on, (YEAR(CURRENT_DATE()) - YEAR(created_on)) AS years_active FROM accounts;

These SQL queries will return the names along with their ages based on their birth dates and account names with the number of years they’ve been active, respectively.

Using Within Date Functions

Both CURDATE() and CURRENT_DATE() can be used within other date functions to perform more complex operations:

SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY) AS 'due_date';

SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AS 'last_year';

Here the DATE_ADD function is used with CURDATE() to get the date that’s 30 days ahead, and DATE_SUB function is used with CURRENT_DATE() to get the date that was exactly one year ago.

Advanced Examples

Moving towards more advanced examples, let’s mix CURDATE() and CURRENT_DATE() with other SQL functions and clauses.

Creating Dynamic Date Ranges

You can use CURDATE() and CURRENT_DATE() to create dynamic date ranges that change every day as part of your query:

SELECT * FROM events WHERE event_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 WEEK);

SELECT * FROM users WHERE created_at BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL 6 MONTH);

These queries return events scheduled for the upcoming week and users who have created their accounts in the last 6 months.

Complex Date Comparisons

In more complex scenarios, you might need to compare the current date with parts of other date fields:

SELECT project_name, deadline FROM projects WHERE YEAR(deadline) = YEAR(CURDATE()) AND MONTH(deadline) = MONTH(CURDATE());
-- This will list all projects whose deadline is in the current month and year.

SELECT user_id, last_activity FROM user_stats WHERE DAY(last_activity) = DAY(CURRENT_DATE()) AND HOUR(last_activity) > 12;
-- This selects users who were last active after 12:00 PM today.

These are a bit complex but provide powerful tools for filtering out data according to very specific time-related criteria.

Handling Time Zones

It’s also worth noting that CURDATE() and CURRENT_DATE() operate in the server’s current time zone by default. To use a different time zone, you can set a session time zone like so:

SET time_zone = '+00:00';
SELECT CURDATE();
-- This will return the current date in UTC

In distributed applications where users span across different time zones, handling such cases is important for maintaining consistency in your application’s data.

Conclusion

Throughout the guide, we explored the practical application of CURDATE() and CURRENT_DATE() MySQL functions. Despite their simplicity, understanding how to wield these functions effectively can greatly enhance your database querying capabilities. They are essential for working with any date-centric data and can be a part of complex queries as you handle different scenarios in your database management tasks.