How to Get the Current Date in MySQL
-
Use the
curdate()Function to Get the Current Date in MySQL -
Use the
now()Function to Get the Current Date in MySQL
When working with databases, various use-cases typically arise for implementation. When there is a need to get the current date from the system for computation, the curdate() or the now() functions in MySQL are viable tools.
Use the curdate() Function to Get the Current Date in MySQL
According to the official documentation for this function, it returns a value of the current date of the query. This return is of a date data type and can be formatted or manipulated for information or computation purposes in the database.
The curdate() function has synonym functions that work in the same fashion i.e. current_date(), current_date.
The use of the curdate() function and its synonyms are illustrated in the following code block.
-- Testing out various current date functions in mysql
SELECT curdate() as "Today's date with curdate";
SELECT current_date() as "Today's date with current_date()";
SELECT current_date as "Today's date with current_date";
Output:
Today's date with curdate
2022-02-03
0.000 sec / 0.000 sec
1 row(s) returned
-----------------------------------------------------------------------------------------
Today's date with current_date()
2022-02-03
0.015 sec / 0.000 sec
1 row(s) returned
-----------------------------------------------------------------------------------------
Today's date with current_date
2022-02-03
0.000 sec / 0.000 sec
1 row(s) returned
It is observed from the speed of the query that the curdate() function gets the current date quickly enough. Also, one of its synonyms/alias current_date() takes a little longer.
While a few seconds may seem trivial for minor datasets and queries, serious issues arise for large-scale applications.
Use the now() Function to Get the Current Date in MySQL
The now() function, as opposed to the curdate() function or its synonyms/aliases, returns a datetime of the current day.
Since datetime comprises two parts, namely date and time, an external date-grabbing function like extract() or date() will be necessary to grab the needed data.
A good reference for extracting date elements from datetime is available via this documentation.
-- Illustrating the now() function
SELECT now() as "Today's datetime", date(now()) as "Today's date";
Output:
Today's datetime Today's date
2022-02-03 20:25:03 2022-02-03
-----------------------------------------------------------------------------------------
0.000 sec / 0.000 sec
1 row(s) returned
The now() function also has synonyms like CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, LOCALTIME(), LOCALTIME, LOCALTIMESTAMP(), LOCALTIMESTAMP.
Let us consider a simple example of why the current date may be necessary to grab. Let us create a sample table called students, with id, name, and date_of_birth.
/* Creating a sample table for illustrating a use-case of the current date methods */
CREATE TABLE students(
id INT AUTO_INCREMENT,
name VARCHAR(255),
date_of_birth date,
PRIMARY KEY(id)
);
-- Populating the students table
INSERT INTO students(name, date_of_birth) VALUES
('Susan Doyle', '1991-02-24'),
('James Maddison', '1991-07-22'),
('Christine Pile', '1993-09-02'),
('Damien Black', '1987-03-14');
-- Viewing the table
SELECT * FROM students;
Output:
id name date_of_birth
1 Susan Doyle 1991-02-24
2 James Maddison 1991-07-22
3 Christine Pile 1993-09-02
4 Damien Black 1987-03-14
-----------------------------------------------------------------------------------------
0.969 sec
0 row(s) affected
0.516 sec
4 row(s) affected Records: 4 Duplicates: 0 Warnings: 0
0.000 sec / 0.000 sec
4 row(s) returned
Now, let us add an extra column called age. This column will compute each student’s age as a difference between the current year and their dates of birth.
-- Modifying the existing table to add a new column
ALTER TABLE students
ADD AGE TINYINT;
Output:
0.766 sec
0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0
Notice that this value is stored using the TINYINT data type. Storing values like this allow for better memory efficiency since our age data is of a small size.
Here is the official documentation on the various integer datatypes.
Now we can update the values of the AGE column.
/* Updating the table with values. The 'generated always' constraint would have been an excellent way to implement this. However, it does not accept the curdate() nor now() function (nor their aliases) */
UPDATE students
SET AGE = (SELECT (YEAR(CURDATE()) - YEAR(students.date_of_birth))) -- using curdate()
WHERE students.id > 0;
Output:
0.140 sec
4 row(s) affected Rows matched: 4 Changed: 4 Warnings: 0
Let us preview our final table.
SELECT * FROM students;
Output:
id name date_of_birth AGE
1 Susan Doyle 1991-02-24 31
2 James Maddison 1991-07-22 31
3 Christine Pile 1993-09-02 29
4 Damien Black 1987-03-14 35
-----------------------------------------------------------------------------------------
0.000 sec / 0.000 sec
4 row(s) returned
The current approach works. However, the results can be improved to properly update the age of students whose birthdays are still far from the current date.
We combine the DATEDIFF function with the FLOOR function to achieve this result.
-- Improving the age update algorithm
UPDATE students
SET AGE = (SELECT FLOOR(DATEDIFF(CURDATE(),students.date_of_birth)/365))
WHERE students.id > 0;
SELECT * FROM students;
Output:
id name date_of_birth AGE
1 Susan Doyle 1991-02-24 30
2 James Maddison 1991-07-22 30
3 Christine Pile 1993-09-02 28
4 Damien Black 1987-03-14 34
-----------------------------------------------------------------------------------------
0.093 sec
4 row(s) affected Rows matched: 4 Changed: 4 Warnings: 0
0.000 sec / 0.000 sec
4 row(s) returned
Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.
LinkedIn GitHub