Retrieving the current time in MySQL is a very important aspect of database management.
For example, you need to obtain the current time in situations such as the following.
Automatically record creation timestamp when inserting data (e.g., timestamp for new user registration)
Record user login times (e.g., managing login history)
Retrieve data for a specific period (e.g., fetching order history from the past 24 hours)
Log system execution events (e.g., managing error logs and event logs)
Thus, properly obtaining the “current time” for data management and logging is essential for database operations. In this article, we will thoroughly explain how to retrieve the current time in MySQL, including actual SQL queries.
We will also cover the differences between commonly used functions, appropriate usage distinctions, and practical query examples, making it useful for both beginners and intermediate users.
2. MySQL Functions for Retrieving the Current Time (Comparison Table)
MySQL provides several functions for retrieving the current time.
Choosing the appropriate function based on the use case is important, but many people find the differences hard to understand. In this section, we compare the major functions for retrieving the current time in MySQL in a table and explain each of their characteristics in detail.
2.1 Comparison Table of MySQL Time Retrieval Functions
Function Name
Information Retrieved
Format
Features
NOW()
Current date and time
YYYY-MM-DD HH:MM:SS
Most commonly used
SYSDATE()
Date and time at execution
YYYY-MM-DD HH:MM:SS
Unlike NOW(), it changes with each execution
CURTIME()
Current time
HH:MM:SS
No date, only time
CURRENT_TIME()
Current time
HH:MM:SS
Same as CURTIME()
CURRENT_TIMESTAMP()
Current date and time
YYYY-MM-DD HH:MM:SS
Almost the same as NOW()
UTC_TIMESTAMP()
Current UTC date and time
YYYY-MM-DD HH:MM:SS
Time zone fixed to UTC
Looking at the table above, most functions retrieve the “current date and time” or “current time,” but the function you should choose varies depending on the purpose.
2.2 Overview and Features of Each Function
NOW() Function
The common function for retrieving the current date and time
Returns the time at which the SQL query started
Often used to obtain timestamps when inserting or updating data
SELECT NOW();
Example result
2025-02-14 15:30:45
SYSDATE() Function
Gets the current time at the moment the query is executed
The difference from NOW() is that SYSDATE() returns a different time for each execution (even within a transaction)
SELECT SYSDATE();
Example result
2025-02-14 15:30:47
CURTIME() Function
Retrieves only the time (HH:MM:SS)
Used when the date is not needed
SELECT CURTIME();
Example result
15:30:45
CURRENT_TIME() Function
Behaves the same as CURTIME()
Function name compliant with the SQL standard
SELECT CURRENT_TIME();
Example result
15:30:45
CURRENT_TIMESTAMP() Function
Same behavior as NOW()
Can be specified as a DEFAULT value for a table column (more suitable than NOW())
SELECT CURRENT_TIMESTAMP();
Example result
2025-02-14 15:30:45
Set as default value when creating a table:
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
UTC_TIMESTAMP() Function
Retrieves the current UTC (Coordinated Universal Time) time
Provides consistent time regardless of the server’s time zone
SELECT UTC_TIMESTAMP();
Example result
2025-02-14 06:30:45
2.3 Which Function Should You Use?
Use Case
Recommended Function
Retrieve current date and time
NOW()
Get a different time for each transaction
SYSDATE()
Need only the current time (HH:MM:SS)
CURTIME() or CURRENT_TIME()
Set as a table default value
CURRENT_TIMESTAMP()
Retrieve UTC (Coordinated Universal Time)
UTC_TIMESTAMP()
In general, for ordinary date‑time retrieval, using NOW() works fine! However, choosing the appropriate function for your use case enables more flexible data manipulation.
3. Details of the NOW() Function
MySQL’s NOW() function is the most common function for retrieving the current date and time (year-month-day plus time).
It is used in a wide range of scenarios, such as when storing data in the database or retrieving data for a specific period.
3.1 Basic Usage of the NOW() Function
NOW() Syntax
SELECT NOW();
Example Result
2025-02-14 15:30:45
Outputs in the format YYYY-MM-DD HH:MM:SS (year-month-day hour:minute:second).
The time is retrieved based on MySQL’s default time zone setting.
3.2 Uses of the NOW() Function
① Record the current time when inserting data
When inserting data into the database, you can use NOW() to record the creation timestamp.
INSERT INTO users (name, created_at) VALUES ('Sato', NOW());
② Record the current time when updating data
UPDATE users SET last_login = NOW() WHERE id = 1;
③ Retrieve data for a specific period
SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 1 DAY;
Specifying INTERVAL 1 DAY retrieves data from the past 1 day.
3.3 Return Type of the NOW() Function and Changing the Format
Return Data Type of NOW()
DATETIME
type (YYYY-MM-DD HH:MM:SS)
It can also be treated as a string
SELECT NOW(), typeof(NOW());
How to Change the Format
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s') AS formatted_time;
Result
2025/02/14 15:30:45
3.4 Differences Between NOW() Function and SYSDATE() Function
Function Name
Retrieval Timing
Characteristics
NOW()
At query start
Gets a fixed timestamp at the moment the query is executed
SYSDATE()
At query execution
Retrieves a different time for each> each execution of the query
🚨 When dealing with transactions, NOW() is recommended because it provides consistency!
3.5 Limitations and Considerations of the NOW() Function
① Depends on Time Zone
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');>
② Use CURRENT_TIMESTAMP as the default value for tables
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3.6 Summary of the NOW() Function
NOW() is the most common function in MySQL for obtaining the current date and time.
It can be used for various purposes such as inserting, updating, and retrieving data.
If you need a consistent timestamp within a transaction, use NOW().
SYSDATE() retrieves a different time for each execution, so use it when an exact execution timestamp is needed.
To obtain the time in a different time zone, use CONVERT_TZ().
📌 Conclusion: If you want to get the current date and time in MySQL, using NOW() is generally the best choice!
4. SYSDATE() Function Characteristics
SYSDATE() function is one way to obtain the current date and time in MySQL, but it behaves differently from NOW().
In this section, we will explain in detail the basic usage of SYSDATE(), its differences from NOW(), and important considerations.
4.1 What Is the SYSDATE() Function?
SYSDATE() is a function that returns the time at which the query is executed.
Unlike NOW(), it has the characteristic of retrieving a different time each time it is executed.
SYSDATE() Syntax
SELECT SYSDATE();
Example Result
2025-02-14 16:00:45
Returned in YYYY-MM-DD HH:MM:SS format
The exact moment the query is executed is obtained
4.2 Differences Between SYSDATE() and NOW()
</tr
Function Name
Acquisition Timing
Characteristics
NOW()
At query start
Returns a fixed time at the point the query is executed
NOW()maintains the time at query start, so the time does not change.
SYSDATE()returns a different time each execution, so a different time is shown after 3 seconds.
🚨 Use NOW() for transaction processing
SYSDATE() behaves differently from NOW() in multithreaded environments, so for transaction processing it is recommended to use a consistent NOW().
4.3 SYSDATE() Usage Examples
① Record current time when inserting data
INSERT INTO logs (event, created_at) VALUES ('User login', SYSDATE());
② Record current time when updating data
UPDATE users SET last_login = SYSDATE() WHERE id = 1;
③ Retrieve current time in a specific format
SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:%s') AS formatted_time;
Example Result
2025-02-14 16:05:30
4.4 SYSDATE() Limitations and Considerations
① Impact on Transactions
NOW()holds the time at the start of the query, allowing consistent time usage within a transaction.
SYSDATE()retrieves a different time each execution, which can cause time drift within a transaction.
② Cannot Be Used as Default Value
SYSDATE()cannot be set as a table DEFAULT value.
✅ Solution: Use DEFAULT CURRENT_TIMESTAMP.
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
4.5 When Should You Use SYSDATE()?
Use Case
Recommended Function
Standard current time retrieval
NOW()
Use a consistent time in transactions
NOW()
When you need the exact execution time
SYSDATE()
🚀 SYSDATE() is suitable for real-time logging, but not ideal for transaction processing.
4.6 Summary of SYSDATE()
SYSDATE() differs from NOW() in that it retrieves the time at the moment the query is executed.
Because it returns a different time each execution, it is suitable for real-time logging.
If consistency within a transaction is required, NOW() is recommended.
As a default value, you need to use CURRENT_TIMESTAMP.
📌 Conclusion: Use NOW() for general time retrieval, but choose SYSDATE() when you need the exact execution time!
5. CURTIME() Function and CURRENT_TIME() Function
When retrieving the current time in MySQL, CURTIME() and CURRENT_TIME() are used when you only want the time (HH:MM:SS). This section provides a detailed explanation of each function’s basic usage, differences, and practical examples.
5.1 What is the CURTIME() Function?
CURTIME() is a MySQL function that retrieves the current time (hour, minute, second). It is characterized by returning only the time information without the date part.
Syntax of CURTIME()
SELECT CURTIME();
Example Result
16:30:45
Format is HH:MM:SS
Based on the server’s time zone setting
Unlike NOW(), it is suitable when date information is not needed
5.2 What is the CURRENT_TIME() Function?
CURRENT_TIME() is also a function that retrieves the current time (HH:MM:SS), just like CURTIME(). In practice, it works exactly the same as CURTIME().
Syntax of CURRENT_TIME()
SELECT CURRENT_TIME();
Example Result
16:30:45
📌 What are the differences?
CURTIME() is a MySQL-specific function
CURRENT_TIME() is an SQL standard function
Both behave the same regardless of which you use
You can choose based on readability preference (there is no performance difference)
5.3 Comparison of CURTIME() and CURRENT_TIME()
Function Name
Information Retrieved
Format
Characteristics
CURTIME()
Current time (hour, minute, second)
HH:MM:SS
MySQL-specific function
CURRENT_TIME()
Current time (hour, minute, second)
HH:MM:SS
SQL standard function
5.4 Practical Examples of CURTIME() / CURRENT_TIME()
① Record the current time when inserting data
INSERT INTO user_logs (event, event_time) VALUES ('login', CURTIME());
② Filtering (retrieve data for a specific time range)
SELECT * FROM logs WHERE TIME(created_at) BETWEEN '09:09:00' AND '18:00:00';
③ Change format (include milliseconds)
SELECT CURTIME(3); -- display up to three decimal places
Example Result
16:30:45.123
5.5 Limitations and Considerations of CURTIME() / CURRENT_TIME()
① Since there is no date information, you need to combine it with a date
SELECT CONCAT(CURDATE(), ' ', CURTIME()) AS full_datetime;
② When using as a default value
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
event_time TIME DEFAULT CURTIME()
);
5.6 When Should You Use CURTIME() / CURRENT_TIME()?
Use Case
Recommended Function
Only the current time (hour, minute, second) is needed
CURTIME() or CURRENT_TIME()
Retrieve the current date and time
NOW()
Retrieve time with millisecond precision
CURTIME(3)
📌 In general, either CURTIME() or CURRENT_TIME() works fine! Choose based on readability preference.
5.7 Summary of CURTIME() / CURRENT_TIME()
CURTIME() is a MySQL-specific function, CURRENT_TIME() is an SQL standard function
Both retrieve the current time (HH:MM:SS)
Use CURTIME(3) or DATE_FORMAT() to change the format
If you need a date, combine with CURDATE() or NOW()
It is common to use CURRENT_TIMESTAMP as a default value
📌 Conclusion: If you only want the current “time”, using CURTIME() is fine!
6. Practical Query Examples Using the Current Time
Now that you understand how to retrieve the current time in MySQL, let’s look at how to use it in practice.
This section introduces practical query examples that use the current time.
We explain techniques useful for everyday database operations such as inserting, updating, and searching data.
6.1 Record the Current Time When Inserting Data
① Record the current time during user registration
INSERT INTO users (name, email, created_at)
VALUES ('Sato', 'sato@example.com', NOW());
6.2 Record the Current Time When Updating Data
② Record the user’s last login timestamp
UPDATE users SET last_login = NOW() WHERE id = 1;
6.3 Retrieve Data from the Past ◯ Days
③ Retrieve order data from the past 7 days
SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY;
6.4 Retrieve Data for a Specified Time Range
④ Retrieve data created today between 09:00 and 18:00
SELECT * FROM logs WHERE TIME(created_at) BETWEEN '09:00:00' AND '18:00:00';
6.5 Retrieve the Current Time Considering Time Zones</h3
⑤ Convert UTC time to Japan Standard Time (JST)
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');
6.6 Set the Record Creation Timestamp to the Current Time by Default
⑥ Automatically set created_at to the current time
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
event VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
6.7 Delete Data After a Certain Period
⑦ Delete data older than 30 days
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;
6.8 Retrieve the Current Time with Millisecond (Microsecond) Precision
⑧ Get the current time with millisecond precision
SELECT NOW(3);
Result
2025-02-14 16:30:45.123
6.9 Selection Guide by Use Case for the Current Time
Use Case
Recommended Function
Example
Record the current time when inserting data
NOW()
INSERT INTO users (name, created_at) VALUES ('Sato', NOW());
Record the current time when updating data
NOW()
UPDATE users SET last_login = NOW() WHERE id = 1;
Retrieve data from the past ◯ days
NOW() - INTERVAL X DAY
SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY;
Retrieve data for a specific time range
CURTIME()
SELECT * FROM logs WHERE TIME(created_at) BETWEEN '09:00:00' AND '18:00:00';
Convert UTC time to JST
CONVERT_TZ()
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');
Delete old data (older than 30 days)
NOW() - INTERVAL X DAY
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;
6.10 Summary
Using NOW() you can obtain the basic current timestamp
CURTIME() and CURRENT_TIME() are useful when you only need the time portion
Retrieving data from the past ◯ days, time-range filtering, millisecond precision, etc., can be applied to many scenarios
Because time zones affect results, use CONVERT_TZ() to get the appropriate time
For database size management, an automatic cleanup of old data is also important
📌 Conclusion: Leveraging the current time makes data management in MySQL more efficient!
7. FAQ (Frequently Asked Questions)
We have compiled Frequently Asked Questions (FAQ) about how to get the current time in MySQL.
Each question is explained clearly.
7.1 What is the difference between the NOW() function and the SYSDATE() function?
Answer
NOW() and SYSDATE() both retrieve the current date and time, but they differ in the time of retrieval.
Function Name
Retrieval Timing
Features
NOW()
At query start
Gets a fixed timestamp at the time the query is executed
SYSDATE()
At query execution
Retrieves a different time each time it is executed
🚨 Using NOW() is recommended for transaction processing!
7.2 Are CURRENT_TIMESTAMP() and NOW() the same?
Answer
In general they behave the same, but CURRENT_TIMESTAMP() can be used as a default value.
Example when creating a table
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
7.3 How to get the current time in a specific time zone?
Answer
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');
7.4 How to automatically record the current time?
Answer
If you specify DEFAULT CURRENT_TIMESTAMP when creating a table, the current time is automatically saved in created_at.
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
event VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
7.5 How to retrieve data from the past ◯ days?
Answer
SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY;
7.6 How to get the current time with millisecond (microsecond) precision?
Answer
SELECT NOW(3);
Result
2025-02-14 16:30:45.123
7.7 Summary
NOW() and SYSDATE() have different retrieval timings
CURRENT_TIMESTAMP() can be used as a DEFAULT value
Using CONVERT_TZ() allows you to retrieve times in different time zones
To automatically record the current time, use DEFAULT CURRENT_TIMESTAMP
To retrieve data from the past ◯ days, use NOW() - INTERVAL X DAY
With NOW(3) or NOW(6) you can get timestamps with millisecond or microsecond precision
📌 Conclusion: Choosing the right function is essential for handling the current time in MySQL!
8. Summary
In this article, we explained in detail how to retrieve the current time in MySQL.
Centered on the NOW() function, we introduced differences among similar functions, practical query examples, and cautions.
8.1 Main Functions for Getting the Current Time in MySQL
Function Name
Information Retrieved
Format
Features
NOW()
Current date and time
YYYY-MM-DD HH:MM:SS
Most common function
SYSDATE()
Date and time at execution
YYYY-MM-DD HH:MM:SS
Returns a different time for each query execution
CURTIME()
Current time
HH:MM:SS
No date, time only
CURRENT_TIME()
Current time
HH:MM:SS
Same as CURTIME()
CURRENT_TIMESTAMP()
Current date and time
YYYY-MM-DD HH:MM:SS
Almost the same as NOW()
UTC_TIMESTAMP()
Current UTC date and time
YYYY-MM-DD HH:MM:SS
Time zone fixed to UTC
📌 Conclusion:
For general current time retrieval, using NOW() is best.
If you need the exact time at execution, use SYSDATE().
If you only need the time without a date, choose CURTIME().
8.2 Practical Queries Using the Current Time
Record the current time when inserting data
INSERT INTO users (name, created_at) VALUES ('Sato', NOW());
Record the current time when updating data
UPDATE users SET last_login = NOW() WHERE id = 1;
Retrieve data from the past 7 days
SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY;
Retrieve the time considering the time zone
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');
Delete old data (older than 30 days)
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;
8.3 Time Zone Considerations
MySQL’s NOW() is based on the default time zone setting, so results may differ across environments.
To get the time in a different time zone, use CONVERT_TZ().
When changing the server’s time zone
SET GLOBAL time_zone = 'Asia/Tokyo';
8.4 FAQ on Getting the Current Time in MySQL
Q1. What is the difference between NOW() and SYSDATE()?
➡ NOW() retrieves the time at the start of the query, while SYSDATE() retrieves the time at query execution.
Q2. Are CURRENT_TIMESTAMP() and NOW() the same?
➡ Almost the same, but CURRENT_TIMESTAMP() can be used as a default value.
Q3. How to get the current time down to milliseconds (microseconds)?
➡ Use NOW(3) for milliseconds and NOW(6) for microseconds.
