MySQL Current Timestamp: How to Get the Current Date and Time (NOW, SYSDATE, UTC_TIMESTAMP)

1. Introduction

Getting the current time in MySQL is a crucial part of database management.
For example, you may need to retrieve the current time in situations like the following:

• Automatically recording a creation timestamp when inserting data (e.g., a timestamp when a new user signs up)
• Logging user login times (e.g., managing login history)
• Retrieving data within a certain time range (e.g., fetching order history from the past 24 hours)
• Recording system execution logs (e.g., managing error logs and event logs)

As you can see, properly retrieving the “current time” for data management and logging is essential for operating a database.

In this article, we’ll explain how to get the current time in MySQL in detail using real SQL queries.
We’ll also cover the differences between commonly used functions, how to choose the right one, and practical query examples—useful for beginners through intermediate users.

2. MySQL Functions to Get the Current Date/Time (Comparison Table)

MySQL provides multiple functions to retrieve the current time.
Choosing the right function for your use case is important, but many people find the differences confusing.

In this section, we compare the main functions used to get the current date/time in MySQL and explain their characteristics in detail.

2.1 Comparison Table of MySQL Time Functions

From the table above, most functions return either the “current date and time” or the “current time,” but the best function depends on your purpose.

2.2 Overview and Characteristics of Each Function

NOW() Function

• The most common function to get the current date and time
• Returns the time at the moment the SQL statement begins
• Frequently used for timestamps when inserting or updating data

SELECT NOW();

Example output

2025-02-14 15:30:45

SYSDATE() Function

• Gets the current time at the moment of execution
• Unlike NOW(), SYSDATE() can return a different value each time it’s evaluated (it can change even within a transaction)

SELECT SYSDATE();

Example output

2025-02-14 15:30:47

CURTIME() Function

• Gets only the time (HH:MM:SS)
• Useful when the date is not needed

SELECT CURTIME();

Example output

15:30:45

CURRENT_TIME() Function

• Works the same as CURTIME()
• Function name compliant with the SQL standard

SELECT CURRENT_TIME();

Example output

15:30:45

CURRENT_TIMESTAMP() Function

• Behaves like NOW()
• Can be specified as a column DEFAULT value (often more appropriate than NOW() for defaults)

SELECT CURRENT_TIMESTAMP();

Example output

2025-02-14 15:30:45

Set as a default value when creating a table:

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

UTC_TIMESTAMP() Function

• Gets the current UTC (Coordinated Universal Time)
• Not dependent on the server time zone, enabling consistent timestamps

SELECT UTC_TIMESTAMP();

Example output

2025-02-14 06:30:45

2.3 Which Function Should You Use?

In general, for typical date/time retrieval, NOW() is usually the right choice.
However, selecting the best function for your use case allows for more flexible and reliable data operations.

3. NOW() Function Details

The MySQL NOW() function is the most commonly used function for getting the current date and time (date + time).
It’s widely used for storing timestamps in the database and retrieving data within specific time ranges.

3.1 Basic Usage of NOW()

Syntax of NOW()

SELECT NOW();

Example output

2025-02-14 15:30:45

• Output format is YYYY-MM-DD HH:MM:SS (year-month-day hour:minute:second).
• The time is retrieved based on MySQL’s default time zone settings.

3.2 Common Use Cases for NOW()

① Record the current time when inserting data

When inserting data into a database, you can use NOW() to record a 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 from a certain time period

SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 1 DAY;

• By specifying INTERVAL 1 DAY, you can retrieve “data from the past 1 day.”

3.3 Return Type and How to Change the Output Format

Return 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;

Example output

2025-02-14 15:30:45

3.4 Differences Between NOW() and SYSDATE()

SELECT NOW(), SLEEP(3), NOW();
SELECT SYSDATE(), SLEEP(3), SYSDATE();

Example output

NOW()        | SLEEP(3) | NOW()
2025-02-14 15:30:45 | wait 3 seconds | 2025-02-14 15:30:45

SYSDATE()    | SLEEP(3) | SYSDATE()
2025-02-14 15:30:45 | wait 3 seconds | 2025-02-14 15:30:48

🚨 If you’re working with transactions, NOW() is recommended for consistency.

3.5 Limitations and Notes for NOW()

① Depends on the time zone

SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');

② For table default values, use CURRENT_TIMESTAMP

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3.6 Summary of NOW()

• NOW() is the most common function to get the current date and time in MySQL.
• It can be used for inserts, updates, and retrieving time-based data.
• Use NOW() when you need a consistent timestamp within a transaction.
• SYSDATE() returns a different time on each evaluation, so use it when you need the exact execution time.
• Use CONVERT_TZ() to retrieve time in a different time zone.

📌 Conclusion: In most cases, NOW() is the best choice to get the current date and time in MySQL.

4. SYSDATE() Function Characteristics

The SYSDATE() function is another way to get the current date and time in MySQL, but it behaves differently from NOW().
In this section, we’ll explain the basic usage of SYSDATE(), how it differs from NOW(), and important notes.

4.1 What Is SYSDATE()?

SYSDATE() is a function that returns the time at statement execution.
Unlike NOW(), it returns a different timestamp each time it is evaluated.

SYSDATE() syntax

SELECT SYSDATE();

Example output

2025-02-14 16:00:45

• Returned in the YYYY-MM-DD HH:MM:SS format
• Represents the moment the query is executed

4.2 Differences Between SYSDATE() and NOW()

Behavior comparison: NOW() vs SYSDATE()

SELECT NOW(), SLEEP(3), NOW();
SELECT SYSDATE(), SLEEP(3), SYSDATE();

Example output

NOW()        | SLEEP(3) | NOW()
2025-02-14 16:00:45 | wait 3 seconds | 2025-02-14 16:00:45

SYSDATE()    | SLEEP(3) | SYSDATE()
2025-02-14 16:00:45 | wait 3 seconds | 2025-02-14 16:00:48

📌 Key points

• NOW() keeps the timestamp from the start of the statement, so it doesn’t change.
• SYSDATE() returns a different timestamp each time it is evaluated, so it changes after 3 seconds.

🚨 Use NOW() for transaction processing

• Because SYSDATE() can behave differently from NOW() in multi-threaded environments, NOW() is generally recommended for transactional consistency.

4.3 Examples of Using SYSDATE()

① Record the current time when inserting data

INSERT INTO logs (event, created_at) VALUES ('User login', SYSDATE());

② Record the current time when updating data

UPDATE users SET last_login = SYSDATE() WHERE id = 1;

③ Get the current time in a specific format

SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:%s') AS formatted_time;

Example output

2025-02-14 16:05:30

4.4 Limitations and Notes for SYSDATE()

① Transaction behavior

• NOW() keeps the timestamp from the start of the statement, so it can be used consistently within a transaction.
• SYSDATE() returns a different timestamp each time, so time drift can occur within a transaction.

② Cannot be used as a DEFAULT value

• SYSDATE() cannot be set as a table column 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()?

🚀 SYSDATE() is suitable for real-time logging, but it’s not ideal for transaction processing.

4.6 Summary of SYSDATE()

• SYSDATE() retrieves the timestamp at execution time, unlike NOW().
• Because it can return different values each time, it’s useful for real-time logging.
• For transactional consistency, NOW() is recommended.
• For default values, use CURRENT_TIMESTAMP.

📌 Conclusion: For general timestamp retrieval, NOW() is best; for exact execution times, choose SYSDATE().

5. CURTIME() and CURRENT_TIME()

When you want to get the current time in MySQL, CURTIME() and CURRENT_TIME() are used when you need time only (HH:MM:SS).
In this section, we’ll explain the basic usage, differences, and practical examples for each.

5.1 What Is CURTIME()?

CURTIME() is a function that gets the current time (hour, minute, second) in MySQL.
Its key feature is that it does not include the date—only the time portion is returned.

CURTIME() syntax

SELECT CURTIME();

Example output

16:30:45

• Format: HH:MM:SS
• Depends on the server time zone setting
• Unlike NOW(), it’s suitable when you don’t need date information

5.2 What Is CURRENT_TIME()?

CURRENT_TIME(), like CURTIME(), is a function that gets the current time (HH:MM:SS).
In practice, it behaves exactly the same as CURTIME().

CURRENT_TIME() syntax

SELECT CURRENT_TIME();

Example output

16:30:45

📌 What’s the difference?

• CURTIME() is MySQL-specific
• CURRENT_TIME() is SQL-standard
• They behave the same
• Choose based on readability preference (no performance difference)

5.3 CURTIME() vs CURRENT_TIME() (Comparison)

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 within a specific time range)

SELECT * FROM logs WHERE TIME(created_at) BETWEEN '09:00:00' AND '18:00:00';

③ Change precision (include milliseconds)

SELECT CURTIME(3);  -- Display up to 3 fractional digits

Example output

16:30:45.123

5.5 Limitations and Notes for CURTIME() / CURRENT_TIME()

① No date information, so combine with a date when needed

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()?

📌 In most cases, either CURTIME() or CURRENT_TIME() works fine.
Choose whichever you find more readable.

5.7 Summary of CURTIME() / CURRENT_TIME()

• CURTIME() is MySQL-specific; CURRENT_TIME() is SQL-standard
• Both retrieve the current time (HH:MM:SS)
• Use CURTIME(3) or DATE_FORMAT() to adjust formatting/precision
• If you need a date, combine with CURDATE() or use NOW()
• For defaults, CURRENT_TIMESTAMP is commonly used

📌 Conclusion: If you only need the current time, CURTIME() is a solid choice.

6. Practical Query Examples Using the Current Time

Now that you understand how to retrieve the current time in MySQL, let’s look at practical ways to use it.
In this section, we’ll introduce real-world query examples using the current time.
You’ll learn techniques useful for day-to-day database tasks such as inserts, updates, and searches.

6.1 Record the current time when inserting data

① Record the current time when a user registers

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 N days

③ Retrieve order data from the past 7 days

SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY;

6.4 Retrieve data created within a specific time range

④ Retrieve data created today between 9:00 AM and 6:00 PM

SELECT * FROM logs WHERE TIME(created_at) BETWEEN '09:00:00' AND '18:00:00';

6.5 Retrieve the current time with time zones in mind

⑤ Convert UTC time to Japan time (JST)

SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');

6.6 Set a record creation time to the current timestamp by default

⑥ Automatically set created_at to the current timestamp

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 amount of time

⑦ Delete data older than 30 days

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

6.8 Get the current time with millisecond (microsecond) precision

⑧ Get the current timestamp with millisecond precision

SELECT NOW(3);

Example output

2025-02-14 16:30:45.123

6.9 Use-Case Selection Guide

6.10 Summary

• NOW() handles basic current timestamp retrieval
• CURTIME() and CURRENT_TIME() are useful when you need only the time
• You can use current time for time-window queries, time-of-day filtering, and millisecond precision timestamps
• Because timestamps depend on time zones, use CONVERT_TZ() to get the correct time
• For database size management, designing a mechanism to automatically delete old data is also important

📌 Conclusion: Using the current time effectively helps you manage data more efficiently in MySQL.

7. FAQ (Frequently Asked Questions)

Here are frequently asked questions (FAQ) about how to get the current time in MySQL.
Each question is answered clearly and concisely.

7.1 What is the difference between NOW() and SYSDATE()?

Answer

Both NOW() and SYSDATE() retrieve the current date and time, but they differ in when the value is evaluated.

Test example

SELECT NOW(), SLEEP(3), NOW();
SELECT SYSDATE(), SLEEP(3), SYSDATE();

Example output

NOW()        | SLEEP(3) | NOW()
2025-02-14 16:30:45 | wait 3 seconds | 2025-02-14 16:30:45

SYSDATE()    | SLEEP(3) | SYSDATE()
2025-02-14 16:30:45 | wait 3 seconds | 2025-02-14 16:30:48

🚨 For transaction processing, NOW() is generally recommended.

7.2 Are CURRENT_TIMESTAMP() and NOW() the same?

Answer

They behave almost 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 do I get the current time in a specific time zone?

Answer

SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');

7.4 How can I automatically record the current time?

Answer

When creating a table, specify DEFAULT CURRENT_TIMESTAMP to automatically store the current time in created_at.

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

7.5 How do I retrieve data from the past N days?

Answer

SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY;

7.6 How do I get the current time with millisecond (microsecond) precision?

Answer

SELECT NOW(3);

Example output

2025-02-14 16:30:45.123

7.7 Summary

• NOW() and SYSDATE() differ in evaluation timing
• CURRENT_TIMESTAMP() can be used as a DEFAULT value
• Use CONVERT_TZ() to retrieve timestamps in other time zones
• Use DEFAULT CURRENT_TIMESTAMP to auto-record creation timestamps
• Use NOW() - INTERVAL X DAY to retrieve data from the past X days
• NOW(3) or NOW(6) supports millisecond/microsecond precision timestamps

📌 Conclusion: Choosing the right MySQL time function is essential for working with current timestamps.

8. Summary

In this article, we explained how to get the current time in MySQL.
Focusing on the NOW() function, we covered differences between similar functions, practical query examples, and important notes.

8.1 Main MySQL Functions to Get the Current Time

📌 Conclusion:

• For general current timestamp retrieval, NOW() is usually best.
• If you need the exact execution time, use SYSDATE().
• If you only need the time (no 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 time with time zones in mind

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 Notes About Time Zones

• MySQL NOW() depends on the default time zone setting, so results can differ across environments.
• Use CONVERT_TZ() to retrieve timestamps in another time zone.
• If you want to change the server time zone:

SET GLOBAL time_zone = 'Asia/Tokyo';

8.4 FAQ About MySQL Current Time Functions

Q1. What’s the difference between NOW() and SYSDATE()?

➡ NOW() retrieves the time at query start, while SYSDATE() retrieves the time at execution.

Q2. Are CURRENT_TIMESTAMP() and NOW() the same?

➡ They are almost the same, but CURRENT_TIMESTAMP() can be used as a DEFAULT value.

Q3. How do I get the current time with millisecond (microsecond) precision?

➡ Use NOW(3) (milliseconds) or NOW(6) (microseconds).

SELECT NOW(3); -- Example: 2025-02-14 16:30:45.123

8.5 Summary

• In MySQL, selecting the right function for the current time is important.
• Use NOW() for typical cases, and SYSDATE() when you need exact execution timestamps.
• Use CURTIME() for time only, and CONVERT_TZ() for time zone conversion.
• You can use the current time across inserts, updates, searches, and deletions.

📌 Conclusion: Master MySQL time functions to manage data more efficiently.