The Importance of Handling the Current Time in MySQL
MySQL places great importance on handling time in order to properly manage the information stored in a database. In particular, functions that retrieve and manipulate the current time are used in many scenarios, such as logging, schedule management, and recording data update timestamps. For example, you can leverage the current time in cases such as the following.
Order management system: automatically record order timestamps
Log management: record system error logs and access logs
Scheduling feature: store future appointments in the database and send reminders
Thus, handling the current time correctly in MySQL is crucial for improving data integrity and analytical accuracy.
What users who want to know “MySQL current time” are looking for
Many users who search for “MySQL current time” have the following objectives.
Want to retrieve the current time
Want to format the retrieved time
Want to manipulate the time (add/subtract)
Want to query data within a specific time range
Want to handle time with time zone considerations
To meet these needs, this article provides a detailed explanation, from basics to advanced topics, of how to retrieve the current time in MySQL.
What you’ll learn in this article
In this article, you can systematically learn the following points.
Basic methods for retrieving the current time in MySQL
How to change the format of the current time
How to perform date/time calculations (addition/subtraction)
How to handle time with time zone considerations
Practical use cases (log management, schedule management, etc.)
By leveraging this information, you can manage MySQL data more efficiently.
2. How to Get the Current Time in MySQL
Main Methods to Get the Current Time in MySQL
There are several ways to retrieve the current time in MySQL. Primarily, you use the NOW() function, but depending on the use case, you can also use CURRENT_TIMESTAMP, CURDATE(), or CURTIME().
NOW() to Retrieve the Current Time
Basic Syntax of NOW()
SELECT NOW();
Result (Example)
2025-02-01 15:30:45
NOW() retrieves the current date and time in the YYYY-MM-DD HH:MM:SS format.
Using CURRENT_TIMESTAMP
CURRENT_TIMESTAMP also returns the result as NOW().
Basic Syntax of CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP;
Result (Example)
2025-02-01 15:30:45
CURRENT_TIMESTAMP can be used as a column default value.
Example of Setting CURRENT_TIMESTAMP as a Default Value
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
order_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
Getting Only the Date with CURDATE()
Basic Syntax of CURDATE()
SELECT CURDATE();
Result (Example)
2025-02-01
You can obtain just the date without the time.
Getting Only the Time with CURTIME()
Basic Syntax of CURTIME()
SELECT CURTIME();
Result (Example)
15:30:45
CURTIME() is used when you need to retrieve only the time.
Comparison Table of Functions
Function
Data Retrieved
Typical Use
NOW()
Current date and time (YYYY-MM-DD HH:MM:SS)
Recording creation/update timestamps of data
CURRENT_TIMESTAMP
Same as NOW() (can be applied as a column default value)
Default value when creating a table
CURDATE()
Current date only (YYYY-MM-DD)
Getting today’s date / filtering
CURTIME()
Current time only (HH:MM:SS)
Extracting data for specific time ranges
Summary
If you need the current date and time, use NOW()
For column default values, use CURRENT_TIMESTAMP
If you only want the date, use CURDATE()
If you only want the time, use CURTIME()
3. How to format the current time in MySQL
Using DATE_FORMAT() to format the current time
Basic syntax of DATE_FORMAT()
SELECT DATE_FORMAT(NOW(), 'format_string');
By specifying a format_string, you can change the display format of date and time.
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
When using DATETIME
CREATE TABLE reservations (
id INT AUTO_INCREMENT PRIMARY KEY,
reserved_at DATETIME NOT NULL
);
Summary
Error
Cause
Solution
NOW() time is off
Server time zone setting differs
SET SESSION time_zone = 'Asia/Tokyo';
CURRENT_TIMESTAMP returns unexpected date/time
Effect of TIMESTAMP type
Use DATETIME type
Result of TIMESTAMPDIFF() differs from expectations
Rounding error impact
Use TIMESTAMPDIFF(SECOND, datetime1, datetime2)
Don’t understand the difference between TIMESTAMP and DATETIME
Whether time zone affects them
TIMESTAMP is converted to UTC, but DATETIME is fixed
When handling MySQL’s current time, understanding configuration and data type characteristics helps prevent unexpected errors.
8. FAQ (Frequently Asked Questions and Answers)
What is the difference between NOW() and CURRENT_TIMESTAMP?
Answer
In general, both have the same functionality, but only CURRENT_TIMESTAMP can be used when setting a column’s default value.
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
How to retrieve the current time in UTC with MySQL?
Answer
Using the following query, you can retrieve the current UTC time regardless of MySQL’s timezone settings.
SELECT UTC_TIMESTAMP();
Also, if you want to convert the result of NOW() to UTC, use CONVERT_TZ().
SELECT CONVERT_TZ(NOW(), 'Asia/Tokyo', 'UTC');
Should date and time be stored in separate columns?
Answer
Want to store date and time together (logs, reservation management)
CREATE TABLE reservations (
id INT AUTO_INCREMENT PRIMARY KEY,
reserved_at DATETIME NOT NULL
);
Want to manage date and time separately (timesheets, etc.)
CREATE TABLE work_schedules (
id INT AUTO_INCREMENT PRIMARY KEY,
work_date DATE NOT NULL,
work_time TIME NOT NULL
);
Why does the timezone of NOW() shift?
Answer
First, let’s check the current settings.
SHOW VARIABLES LIKE '%time_zone%';
To change the setting:
SET SESSION time_zone = 'Asia/Tokyo';
Can the precision of NOW() be set to milliseconds?
Answer
From MySQL 5.6 onward, you can obtain fractional seconds (milliseconds).
SELECT NOW(3);
Sample output (example)
2025-02-01 14:30:45.123
When storing data with millisecond precision
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
event_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
);
Summary
NOW() and CURRENT_TIMESTAMP are basically the same, but use CURRENT_TIMESTAMP for column default values
Use UTC_TIMESTAMP() to get UTC time
DATETIME is a fixed value, TIMESTAMP is converted according to timezone
Use NOW(3) to retrieve milliseconds
If the timezone shifts, run SET SESSION time_zone = 'Asia/Tokyo';
9. Summary
How to Get the Current Time in MySQL
✅ NOW() → Retrieve the current date and time (YYYY-MM-DD HH:MM:SS) ✅ CURRENT_TIMESTAMP → Can be used as a column default value ✅ CURDATE() → Retrieve only today’s date ✅ CURTIME() → Retrieve only the current time ✅ UTC_TIMESTAMP() → Always retrieve the UTC time
How to Format the Current Time in MySQL
✅ Using DATE_FORMAT(), you can convert dates and times into a readable format.
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
Date Calculations Using the Current Time in MySQL
✅ Get a future date
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
✅ Get a past date
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);
✅ Calculate the difference between two dates
SELECT TIMESTAMPDIFF(DAY, '2025-01-01', NOW());
✅ Convert a time to seconds
SELECT TIME_TO_SEC('01:30:00');
Time Zone Management
✅ Check the current time zone
SHOW VARIABLES LIKE '%time_zone%';
✅ Change the time zone to Japan Standard Time (JST)
SET SESSION time_zone = 'Asia/Tokyo';
✅ How to retrieve UTC time
SELECT UTC_TIMESTAMP();
✅ Convert time with time zone awareness
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');
Practical Use Cases
✅ Log management (automatic recording of creation and update timestamps)
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
event VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
✅ Delete old data (remove records older than 30 days)
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
Key Takeaways
📌 Comprehensive guide to retrieving the current time in MySQL 📌 Explanation of how to use formatting and date calculations 📌 Showcase of practical examples useful in real-world scenarios (log management, schedule management, etc.) 📌 Detailed discussion of common errors and their solutions Time management in MySQL is useful for a variety of purposes, such as logging, data expiration handling, and schedule management. Master the appropriate functions and become able to implement time handling that meets your system requirements.
