Complete Guide: Managing Current Time in MySQL | NOW() vs CURRENT_TIMESTAMP

1. Introduction

Reasons for Obtaining the Current Time in MySQL

When using a database, obtaining the current date and time is necessary in many situations. For example, the following use cases can be considered.

• Recording Creation and Update Dates and Times for Data
  By keeping records, it becomes easier to manage the data’s change history.
• Analysis of Real-Time Data
  When handling time-series data, obtaining the current time makes data processing smoother.
• Obtaining Data for a Certain Period
  For example, queries like “obtain data updated within the past 24 hours” require the current time.

Purpose and Overview of This Article

In this article, we will explain in detail how to obtain the current time in MySQL. We cover a wide range from introducing basic functions to changing date and time formats, calculation methods, and time zone management. In particular, to make it easy for beginners to understand, we will explain with code examples.

The Simplest Method to Obtain the Current Time (Conclusion First)

The simplest way to obtain the current date and time in MySQL is to use the NOW() function. Executing the following SQL will retrieve the current date and time.

SELECT NOW();

Executing this query will yield results like the following.

+---------------------+
| NOW()              |
+---------------------+
| 2025-02-23 14:35:00 |
+---------------------+

In this way, you can obtain the current “date” and “time”.However, MySQL also has other functions like SYSDATE() and CURRENT_TIMESTAMP, each with different characteristics.

2. How to Get the Current Time in MySQL

MySQL provides several functions for obtaining the current date and time. Each function has different characteristics, so you need to select the appropriate one depending on the use case.

Functions Available for Obtaining the Current Time in MySQL

NOW() Function

NOW() is the most common function for obtaining the current date and time.

Usage

SELECT NOW();

Output Example

+---------------------+
| NOW()              |
+---------------------+
| 2025-02-23 14:35:00 |
+---------------------+

Features

• NOW() obtains the time at the time of query execution.
• It is returned as DATETIME type data.

SYSDATE() Function

SYSDATE() is also a function that obtains the current date and time like NOW(), but its behavior is different.

Usage

SELECT SYSDATE();

Output Example

+---------------------+
| SYSDATE()          |
+---------------------+
| 2025-02-23 14:35:02 |
+---------------------+

Difference from NOW()

For example, executing a query like the following will show the difference.

SELECT NOW(), SLEEP(2), NOW();

SELECT SYSDATE(), SLEEP(2), SYSDATE();

Execution Result (Example)

+---------------------+----------+---------------------+
| NOW()              | SLEEP(2) | NOW()              |
+---------------------+----------+---------------------+
| 2025-02-23 14:35:00 | 0        | 2025-02-23 14:35:00 |
+---------------------+----------+---------------------+

+---------------------+----------+---------------------+
| SYSDATE()          | SLEEP(2) | SYSDATE()          |
+---------------------+----------+---------------------+
| 2025-02-23 14:35:00 | 0        | 2025-02-23 14:35:02 |
+---------------------+----------+---------------------+

Points

• NOW() returns the time at the start of query execution.
• SYSDATE() returns the time at the point of query evaluation, so it can return a different value after delays like SLEEP(2).

CURDATE() and CURTIME()

CURDATE() obtains the current date (YYYY-MM-DD), and CURTIME() obtains the current time (HH:MM:SS).

Usage

SELECT CURDATE(), CURTIME();

Output Example

+------------+----------+
| CURDATE()  | CURTIME() |
+------------+----------+
| 2025-02-23 | 14:35:00 |
+------------+----------+

Features

• CURDATE() obtains only the date, so it is handled as DATE type.
• CURTIME() obtains only the time, so it is handled as TIME type.

CURRENT_TIMESTAMP Function

CURRENT_TIMESTAMP is a function that works almost the same as NOW().

Usage

SELECT CURRENT_TIMESTAMP;

Output Example

+---------------------+
| CURRENT_TIMESTAMP  |
+---------------------+
| 2025-02-23 14:35:00 |
+---------------------+

Features

• NOW() and CURRENT_TIMESTAMP basically return the same value.
• It can be used as the default value for TIMESTAMP type columns.

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

UTC_TIMESTAMP() Function

UTC_TIMESTAMP() obtains the current time in UTC (Coordinated Universal Time).

Usage

SELECT UTC_TIMESTAMP();

Output Example (Japan Time JST = UTC+9)

+---------------------+
| UTC_TIMESTAMP()    |
+---------------------+
| 2025-02-23 05:35:00 |
+---------------------+

Features

• Regardless of the server’s time zone, it can obtain UTC time.
• It is convenient when processing that considers time zones is necessary.

Summary of Function Comparison

3. Formatting and Displaying the Current Time

In MySQL, after obtaining the current time, there may be cases where you want to display it not in the default YYYY-MM-DD HH:MM:SS format, but in a specific format. For example, cases such as “want to obtain only the year”, “want to display only the time portion”, “want to change to YYYY/MM/DD format”.

In MySQL, this can be achieved using the DATE_FORMAT() function.

DATE_FORMAT() Function Basics

DATE_FORMAT() is a function that converts the specified date and time data to an arbitrary format.

Syntax

DATE_FORMAT(target date/time, 'format specifier')

For example, to change the current time obtained with NOW() to the YYYY/MM/DD HH:MM format, execute the following SQL.

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i');

Output Example

+----------------------+
| DATE_FORMAT(NOW())  |
+----------------------+
| 2025/02/23 14:35    |
+----------------------+

Key Points

• %Y → 4-digit year (e.g.: 2025)
• %m → 2-digit month (e.g.: 02)
• %d → 2-digit day (e.g.: 23)
• %H → Hour in 24-hour format (e.g.: 14)
• %i → Minutes (e.g.: 35)

DATE_FORMAT() Format Specifiers List

With DATE_FORMAT(), you can freely change the date and time format using symbols like the following.

Common Format Examples

(1) Display in YYYY/MM/DD Format

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');

Output

+----------------------+
| DATE_FORMAT(NOW())  |
+----------------------+
| 2025/02/23          |
+----------------------+

(2) Notation Using Year, Month, Day Labels for YYYY year MM month DD day

SELECT DATE_FORMAT(NOW(), '%Y year %m month %d day');

Output

+----------------------+
| DATE_FORMAT(NOW())  |
+----------------------+
| 2025 year 02 month 23 day      |
+----------------------+

(3) Display in 12-Hour Format (AM/PM)

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i %p');

Output

+------------------------+
| DATE_FORMAT(NOW())    |
+------------------------+
| 2025-02-23 02:35 PM   |
+------------------------+

Obtaining Only the Time Portion or Date Portion

In some cases, you may want to obtain only the current date or obtain only the current time.

(1) Obtain Only the Date (YYYY-MM-DD)

SELECT CURDATE();

Output

+------------+
| CURDATE()  |
+------------+
| 2025-02-23 |
+------------+

(2) Obtain Only the Time (HH:MM:SS)

SELECT CURTIME();

Output

+----------+
| CURTIME() |
+----------+
| 14:35:50 |
+----------+

Formats Including Milliseconds (Microseconds)

Starting from MySQL 5.6.4, by using functions like NOW(6), it is possible to obtain microseconds (6 digits after the decimal point).

(1) Obtain Current Time with Microseconds

SELECT NOW(6);

Output

+----------------------------+
| NOW(6)                    |
+----------------------------+
| 2025-02-23 14:35:50.123456 |
+----------------------------+

(2) Format Including Microseconds

SELECT DATE_FORMAT(NOW(6), '%Y-%m-%d %H:%i:%s.%f');

Output

+------------------------------+
| DATE_FORMAT(NOW(6))         |
+------------------------------+
| 2025-02-23 14:35:50.123456   |
+------------------------------+

Summary

• Using DATE_FORMAT() allows you to freely format dates and times.
• Using CURDATE() or CURTIME() allows you to obtain only the date or time.
• From MySQL 5.6.4 onward, microseconds can also be handled.
• You can handle various formats such as AM/PM notation, YYYY/MM/DD format, YYYY year MM month DD day, and more.

4. Date and Time Calculations Using the Current Time

MySQL allows date and time calculations based on the current time. For example, you can perform operations such as obtaining the time 1 hour later, the date 3 days ago, or the difference between two dates and times.

INTERVAL for Date and Time Addition and Subtraction

In MySQL, you can add and subtract from dates and times using INTERVAL.

(1) Obtaining the Time 1 Hour After the Current Time

SELECT NOW() AS Current Time, NOW() + INTERVAL 1 HOUR AS One Hour Later;

Output

+---------------------+---------------------+
| Current Time        | One Hour Later      |
+---------------------+---------------------+
| 2025-02-23 14:35:00 | 2025-02-23 15:35:00 |
+---------------------+---------------------+

(2) Obtaining the Date 7 Days After the Current Date

SELECT CURDATE() AS Today, CURDATE() + INTERVAL 7 DAY AS One Week Later;

Output

+------------+------------+
| Today      | One Week Later |
+------------+------------+
| 2025-02-23 | 2025-03-02 |
+------------+------------+

(3) Obtaining the Date 3 Days Before the Current Date

SELECT CURDATE() AS Today, CURDATE() - INTERVAL 3 DAY AS Three Days Ago;

Output

+------------+------------+
| Today      | Three Days Ago |
+------------+------------+
| 2025-02-23 | 2025-02-20 |
+------------+------------+

(4) Obtaining the Date 1 Month After a Specific Date and Time

SELECT DATE_ADD('2025-02-23', INTERVAL 1 MONTH) AS One Month Later;

Output

+------------+
| One Month Later |
+------------+
| 2025-03-23 |
+------------+

(5) Obtaining the Date 1 Year After a Specific Date and Time

SELECT DATE_ADD('2025-02-23', INTERVAL 1 YEAR) AS One Year Later;

Output

+------------+
| One Year Later |
+------------+
| 2026-02-23 |
+------------+

Date Difference Calculation Using DATEDIFF()

Using the DATEDIFF() function, you can obtain the difference between two dates in days.

(1) Calculating the Difference Between Two Dates

SELECT DATEDIFF('2025-03-01', '2025-02-23') AS Day Difference;

Output

+--------------+
| Day Difference |
+--------------+
| 6            |
+--------------+

(2) Calculating the Difference Between the Current Date and a Specific Date

SELECT DATEDIFF(NOW(), '2025-01-01') AS Days Elapsed;

Output

+--------------+
| Days Elapsed |
+--------------+
| 53           |
+--------------+

Time Unit Difference Calculation Using TIMESTAMPDIFF()

Using TIMESTAMPDIFF(), you can perform difference calculations in hours, minutes, and seconds.

(1) Calculating the Difference Between Two Dates and Times in Hours

SELECT TIMESTAMPDIFF(HOUR, '2025-02-23 12:00:00', '2025-02-23 18:30:00') AS Time Difference;

Output

+----------------+
| Time Difference |
+----------------+
| 6              |
+----------------+

(2) Calculating the Difference Between Two Dates and Times in Minutes

SELECT TIMESTAMPDIFF(MINUTE, '2025-02-23 12:00:00', '2025-02-23 12:30:00') AS Minute Difference;

Output

+-----------------+
| Minute Difference |
+-----------------+
| 30              |
+-----------------+

(3) Calculating the Difference Between Two Dates and Times in Seconds

SELECT TIMESTAMPDIFF(SECOND, '2025-02-23 12:00:00', '2025-02-23 12:00:45') AS Second Difference;

Output

+------------------+
| Second Difference |
+------------------+
| 45               |
+------------------+

Date Range Filtering Using BETWEEN

In cases where you want to retrieve data within a specific period from the database, you can easily achieve this using BETWEEN.

(1) Retrieving Data from the Past Week

SELECT * FROM orders WHERE order_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE();

(This query retrieves order data from the past 7 days)

(2) Retrieving Data from February 1, 2025 to February 15, 2025

SELECT * FROM orders WHERE order_date BETWEEN '2025-02-01' AND '2025-02-15';

Summary

• Using INTERVAL makes it easy to add and subtract dates and times.
• Using DATEDIFF() enables difference calculations in days.
• Using TIMESTAMPDIFF() allows difference calculations in hours, minutes, and seconds.
• Using BETWEEN makes it easy to retrieve data within a specific period.

5. Time Zone Settings and Management

MySQL obtains the current time based on the server’s default time zone. However, when developing system specifications or global applications, time management in different time zones may be necessary.

How to Check the Current Time Zone

In MySQL, you can check the currently set time zone with the following SQL.

SELECT @@global.time_zone, @@session.time_zone;

Output Example

+--------------------+------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+------------------+
| SYSTEM            | SYSTEM           |
+--------------------+------------------+

Explanation

• @@global.time_zone indicates the server-wide time zone.
• @@session.time_zone indicates the current session’s time zone.

By default, it is set to SYSTEM, which uses the OS’s time zone settings.

Changing the Time Zone per Session

In MySQL, you can temporarily change the time zone. For example, to change to JST (Japan Standard Time), execute the following SQL.

SET time_zone = 'Asia/Tokyo';

Alternatively, to set it to UTC (Coordinated Universal Time), do as follows.

SET time_zone = '+00:00';

Checking the Time Zone After Setting

SELECT @@session.time_zone;

Output Example

+------------------+
| @@session.time_zone |
+------------------+
| Asia/Tokyo       |
+------------------+

Key Points

• This setting applies only to the current session and reverts when the connection is disconnected.
• To apply it persistently, you need to change the server settings (see the next section).

How to Set the Server-Wide Default Time Zone

To change the server-wide default time zone, edit the MySQL configuration file (my.cnf or my.ini) and set default_time_zone.

(1) Edit the Configuration File

For Linux (my.cnf):

[mysqld]
default_time_zone = 'Asia/Tokyo'

For Windows (my.ini):

[mysqld]
default_time_zone = '+09:00'

(2) Restart MySQL

After changing the settings, restart the MySQL server.

Linux:

sudo systemctl restart mysql

Windows:

net stop mysql
net start mysql

(3) Verify the Settings

SELECT @@global.time_zone;

Output Example

+--------------------+
| @@global.time_zone |
+--------------------+
| Asia/Tokyo         |
+--------------------+

Obtaining UTC Time (UTC_TIMESTAMP())

When building global systems, it is important to handle unified UTC time without depending on the server’s time zone.

In MySQL, you can obtain the current UTC time using UTC_TIMESTAMP().

SELECT UTC_TIMESTAMP();

Output Example

+---------------------+
| UTC_TIMESTAMP()    |
+---------------------+
| 2025-02-23 05:35:00 |
+---------------------+

Key Points

• UTC_TIMESTAMP() is not affected by the time zone.
• It is convenient for managing global data without specifying a time zone.

Time Conversion Using CONVERT_TZ()

You can convert a date and time from one time zone to another using MySQL’s CONVERT_TZ().

(1) Converting from UTC to JST

SELECT CONVERT_TZ('2025-02-23 05:35:00', '+00:00', '+09:00') AS Japanese Time;

Output

+---------------------+
| Japanese Time       |
+---------------------+
| 2025-02-23 14:35:00 |
+---------------------+

(2) Using Time Zone Names

If the MySQL time_zone table is set up, you can specify time zone names for conversion as follows.

SELECT CONVERT_TZ('2025-02-23 05:35:00', 'UTC', 'Asia/Tokyo');

Output

+---------------------+
| Japanese Time       |
+---------------------+
| 2025-02-23 14:35:00 |
+---------------------+

(3) Updating MySQL’s Time Zone Data

When using CONVERT_TZ() in MySQL, the time_zone table may be empty. In that case, you can update the data with the following command (Linux environment).

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Summary

• You can check the current time zone with SELECT @@global.time_zone, @@session.time_zone;.
• You can change the time zone per session with SET time_zone = 'Asia/Tokyo';.
• By setting default_time_zone in my.cnf and restarting MySQL, you can change the server-wide time zone.
• Using UTC_TIMESTAMP() allows you to obtain UTC time regardless of the server’s time zone.
• Using CONVERT_TZ() enables conversion between different time zones.

6. How to Set the Current Timestamp as the Default Value

In database design, it’s common to need to set the current timestamp as the default value for certain columns in a table. For example, when automatically recording the creation date and time or update date and time of records.

Setting CURRENT_TIMESTAMP as the Default Value

In MySQL, you can set CURRENT_TIMESTAMP as the default value for columns of type TIMESTAMP or DATETIME. This automatically sets the current time when a record is inserted.

(1) Setting CURRENT_TIMESTAMP as the Default Value

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

When inserting data into this table, the created_at column will automatically be set to the current time.

INSERT INTO users (name) VALUES ('Alice');

SELECT * FROM users;

Output

+----+-------+---------------------+
| id | name  | created_at          |
+----+-------+---------------------+
|  1 | Alice | 2025-02-23 14:35:00 |
+----+-------+---------------------+

Automatic Updates with ON UPDATE CURRENT_TIMESTAMP

You can combine CURRENT_TIMESTAMP with the ON UPDATE clause to automatically set the current time when a record is updated.

(1) Setting ON UPDATE CURRENT_TIMESTAMP

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• The created_at is set to the current time only on the initial record creation.
• The updated_at is automatically updated to the current time when the record is updated.

(2) Inserting Data

INSERT INTO users (name) VALUES ('Bob');

SELECT * FROM users;

Output

+----+------+---------------------+---------------------+
| id | name | created_at          | updated_at          |
+----+------+---------------------+---------------------+
|  1 | Bob  | 2025-02-23 14:40:00 | 2025-02-23 14:40:00 |
+----+------+---------------------+---------------------+

(3) Updating Data

UPDATE users SET name = 'Bobby' WHERE id = 1;

SELECT * FROM users;

Output

+----+-------+---------------------+---------------------+
| id | name  | created_at          | updated_at          |
+----+-------+---------------------+---------------------+
|  1 | Bobby | 2025-02-23 14:40:00 | 2025-02-23 14:42:10 |
+----+-------+---------------------+---------------------+

As such, the value of updated_at is automatically updated.

Differences Between DATETIME and TIMESTAMP Types

In MySQL, there are two types for handling dates and times: DATETIME and TIMESTAMP. It’s important to understand their characteristics and use them appropriately.

Points for Choosing Between Them

• Cases to Use DATETIME
• Don’t want influence from time zones (want to store fixed dates and times).
• Possibility of handling data after 2038.
• Cases to Use TIMESTAMP
• Want to handle times according to MySQL’s default time zone.
• Want to use CURRENT_TIMESTAMP as the default value (explicit setting required for DATETIME).

Why You Can’t Set NOW() as the Default Value

While CURRENT_TIMESTAMP can be used as a default value, the NOW() function cannot be set directly as a default value.

(1) Attempting to Set NOW() as Default Results in Error

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

Error

ERROR 1067 (42000): Invalid default value for 'created_at'

This error indicates that NOW() is a function and cannot be used directly as a default value.

(2) Solution

Instead, use CURRENT_TIMESTAMP.

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

Additionally, if you want to use NOW(), you can also use a BEFORE INSERT trigger.

CREATE TRIGGER set_created_at BEFORE INSERT ON logs
FOR EACH ROW
SET NEW.created_at = NOW();

Summary

• By setting CURRENT_TIMESTAMP as DEFAULT, you can automatically insert the current time when creating a record.
• Setting ON UPDATE CURRENT_TIMESTAMP automatically updates the timestamp when the record is updated.
• It’s important to understand the differences between DATETIME and TIMESTAMP and use them appropriately.
• NOW() cannot be used as a default, but CURRENT_TIMESTAMP can substitute for it.
• Using a BEFORE INSERT trigger allows you to handle NOW() like a default value.

7. Practical Examples Using MySQL’s Current Time

After learning how to obtain and manipulate the current time in MySQL, next, let’s specifically look at how it can be utilized in actual business or development.

This section introduces the following practical examples.

• Adding the Current Time to Log Records
• Obtaining Data from the Past 24 Hours
• Updating the User’s Last Login Time
• Automatically Recording Data Creation and Update Times
• SQL to Obtain Data Within a Certain Period

Adding the Current Time to Log Records

When recording system logs or error logs, it is common to save them along with the occurrence date and time. This makes troubleshooting easier.

(1) Creating the Log Table

CREATE TABLE system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(255),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

(2) Adding a Log

INSERT INTO system_logs (event_type, message)
VALUES ('ERROR', 'Failed to connect to server');

(3) Obtaining the Latest Logs

SELECT * FROM system_logs ORDER BY created_at DESC LIMIT 10;

In this way, by using CURRENT_TIMESTAMP, the log creation date and time can be automatically recorded.

Obtaining Data from the Past 24 Hours

In EC sites or user management systems, etc., there are frequent cases where “we want to obtain events or orders that occurred in the past 24 hours“.

(1) Obtaining Users Registered Within the Past 24 Hours

SELECT * FROM users WHERE created_at >= NOW() - INTERVAL 1 DAY;

Executing this query obtains users registered within the past 24 hours from the current time.

(2) Obtaining Orders from the Past 24 Hours

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

Updating the User’s Last Login Time

By updating the last login time each time a user logs in, management of active users becomes possible.

(1) Creating the User Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• last_login is automatically updated each time the user logs in.

(2) Updating the Login Time When a User Logs In

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

(3) Obtaining Users Whose Last Login Was More Than 1 Week Ago

SELECT * FROM users WHERE last_login < NOW() - INTERVAL 7 DAY;

Using this query, we can extract “users whose last login was more than 1 week ago“.

Automatically Recording Data Creation and Update Times

In databases, it is common to “automatically manage the record creation date/time and update date/time“.

(1) Table That Automatically Records Creation and Update Times

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• created_at → Automatically set when the record is created
• updated_at → Automatically updated each time the record is updated

(2) Adding a New Article

INSERT INTO articles (title, content)
VALUES ('How to Use MySQL's Current Time', 'This explains in detail the MySQL NOW() function.');

(3) Updating the Article Content

UPDATE articles SET content = 'In addition to NOW(), we also explain CURRENT_TIMESTAMP in detail.'
WHERE id = 1;

In this way, each time the record is updated, updated_at is automatically changed, so there is no need to manually set the update date and time.

SQL to Obtain Data Within a Certain Period

Obtaining data registered within a specific period is useful for sales analysis and measuring user activity.

(1) Obtaining Orders Within the Specified Period

SELECT * FROM orders WHERE order_date BETWEEN '2025-02-01' AND '2025-02-15';

This query obtains orders from February 1, 2025 to February 15, 2025.

(2) Obtaining Users Registered Within the Past Month

SELECT * FROM users WHERE created_at >= NOW() - INTERVAL 1 MONTH;

• By using INTERVAL 1 MONTH, we can obtain “users registered within the past month”.

Summary

• By using CURRENT_TIMESTAMP, we can automatically record the creation time of logs and events.
• By using NOW() - INTERVAL X DAY, we can easily obtain data from the past X days.
• By utilizing ON UPDATE CURRENT_TIMESTAMP, we can automatically manage the user’s last login time.
• By using BETWEEN, we can obtain data within a specific period.
• It is convenient to utilize TIMESTAMP for managing data creation and update dates and times.

8. Frequently Asked Questions (FAQ)

When performing operations related to the current time in MySQL, you may encounter several questions or issues. In this section, we explain common questions and their solutions in a Q&A format.

NOW() and CURRENT_TIMESTAMP: What is the difference?

Q:Both NOW() and CURRENT_TIMESTAMP can retrieve the current time, but are there any differences?A:They perform almost the same operation, but there are slight differences.

Especially, CURRENT_TIMESTAMP can be automatically applied to TIMESTAMP type columns, so it is useful in many table design scenarios.

I want to get the current time with NOW(), but it’s not working correctly

Q:Even when I execute SELECT NOW();, I can’t get the time as expected. What is the cause?A:The possible causes are as follows.

1. The server’s time zone is not set appropriately

   SELECT @@global.time_zone, @@session.time_zone;

Use this to check the server and session time zones, and change them as necessary.

   SET time_zone = 'Asia/Tokyo';

2. The time zone in MySQL settings is set to SYSTEM

• In the case of SYSTEM, it depends on the OS time zone settings.
• Therefore, it is important to unify the MySQL and OS time zone settings.

Causes of Time Drift in CURRENT_TIMESTAMP and Solutions

Q:Even though I set CURRENT_TIMESTAMP as the default value, what is the cause if the time differs from expected?A:Several factors can be considered.

1. The database time zone and the application time zone are different

• Let’s check with SELECT @@global.time_zone, @@session.time_zone;.
• If they are different, we recommend unifying them.

1. Unlike the DATETIME type, the TIMESTAMP type is affected by the server’s time zone

• If you want to avoid the influence of the time zone, it is better to use the DATETIME type.

Reasons Why NOW() Cannot Be Set as a Default Value

Q:When I try to set NOW() as the default value, I get an error. Why?A:NOW() is a function, and MySQL has a restriction that functions cannot be used as default values.

Error Example

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

ERROR 1067 (42000): Invalid default value for 'created_at'

✅ Solution

Instead, let’s use CURRENT_TIMESTAMP.

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

Alternatively, there is also a method using triggers.

CREATE TRIGGER set_created_at BEFORE INSERT ON logs
FOR EACH ROW
SET NEW.created_at = NOW();

How to Handle Cases Where Range Specification Using BETWEEN Doesn’t Work

Q:I tried to retrieve data in a specific range using BETWEEN, but it doesn’t work correctly.A:When using BETWEEN, differences in data types or time truncation, etc., can cause unexpected results.

(1) Correct Usage of BETWEEN

For example, when retrieving data from February 1, 2025, to February 10, 2025:

SELECT * FROM orders WHERE order_date BETWEEN '2025-02-01 00:00:00' AND '2025-02-10 23:59:59';

Points

• Omitting the time like BETWEEN '2025-02-01' AND '2025-02-10' results in judgment at 00:00:00, which can lead to unintended results.
• Explicitly specifying 23:59:59 allows correct range specification up to the end of the date.

Cases Where You Should Use SYSDATE() and Cases Where You Shouldn’t

Q:What is the difference between SYSDATE() and NOW()? When should I use it?A:SYSDATE() is similar to NOW(), but the difference is that it retrieves the time at the evaluation timing, not at the query execution point.

(1) In the Case of NOW()

SELECT NOW(), SLEEP(2), NOW();

Output

+---------------------+----------+---------------------+
| NOW()              | SLEEP(2) | NOW()              |
+---------------------+----------+---------------------+
| 2025-02-23 14:00:00 | 0        | 2025-02-23 14:00:00 |
+---------------------+----------+---------------------+

(2) In the Case of SYSDATE()

SELECT SYSDATE(), SLEEP(2), SYSDATE();

Output

+---------------------+----------+---------------------+
| SYSDATE()          | SLEEP(2) | SYSDATE()          |
+---------------------+----------+---------------------+
| 2025-02-23 14:00:00 | 0        | 2025-02-23 14:00:02 |
+---------------------+----------+---------------------+

✅ Cases Where You Should Use SYSDATE()

• When you want to strictly record the execution time of each query within a transaction.
• When recording logs or real-time data and seeking precise timing.

Summary

• NOW() and CURRENT_TIMESTAMP are almost the same, but CURRENT_TIMESTAMP is convenient when using with TIMESTAMP type.
• When specifying date ranges with BETWEEN, include up to 23:59:59.
• SYSDATE() is useful to leverage in situations where real-time time is needed.

9. Summary

In the previous sections, we have explained in detail how to obtain, manipulate, and manage the current time in MySQL. In this final section, we will review each point and organize the important points for properly handling the current time in MySQL.

Summary of Methods to Obtain the Current Time in MySQL

✅NOW() is the most commonly used, but other functions can be utilized depending on the purpose!

Formatting and Manipulation of Current Time

Obtained time should be formatted into a more readable form or have only specific parts extracted, which is important.

• Format using DATE_FORMAT()

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

Output

  2025-02-23 14:35:00

• Obtain only the date and time parts

  SELECT CURDATE(), CURTIME();

Output

  +------------+----------+
  | 2025-02-23 | 14:35:00 |
  +------------+----------+

Calculations Using Current Time

• Add or subtract time using INTERVAL

  SELECT NOW() + INTERVAL 1 HOUR; -- 1 hour later
  SELECT NOW() - INTERVAL 3 DAY;  -- 3 days ago

• Calculate the difference in days

  SELECT DATEDIFF('2025-03-01', '2025-02-23');

Output

  6 days

• Calculate the difference in time

  SELECT TIMESTAMPDIFF(HOUR, '2025-02-23 12:00:00', '2025-02-23 18:30:00');

Output

  6 hours

Time Zone Management

• Check the current time zone

  SELECT @@global.time_zone, @@session.time_zone;

• Change time zone per session

  SET time_zone = 'Asia/Tokyo';

• Convert between different time zones

  SELECT CONVERT_TZ('2025-02-23 05:35:00', 'UTC', 'Asia/Tokyo');

Output

  2025-02-23 14:35:00

Setting Current Time as Default Value

• Automatically record the date and time when creating a record

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

• Automatically update updated_at on update

  CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );

Practical Examples of Current Time in MySQL

• Log Recording

  INSERT INTO system_logs (event_type, message)
  VALUES ('ERROR', 'Server connection failed');

• Obtain data from the past 24 hours

  SELECT * FROM users WHERE created_at >= NOW() - INTERVAL 1 DAY;

• Update the last login time

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

• Obtain data within a certain period

  SELECT * FROM orders WHERE order_date BETWEEN '2025-02-01' AND '2025-02-15';

Next Steps

• Try SQL to obtain the current time in MySQL (SELECT NOW(); etc.)
• Try building actual SQL using date calculations and formatting
• Apply to actual applications (log recording, user management, etc.)

Conclusion

✅Using NOW() or CURRENT_TIMESTAMP allows easy acquisition of the current time
✅Using DATE_FORMAT() enables converting time into a more readable format
✅Using INTERVAL or DATEDIFF() makes date and time calculations possible
✅Using the TIMESTAMP type allows automatic recording of creation and update dates and times
✅ Proper time zone management enables correct handling of times in different regionsProperly handling the current time in MySQL is important in all scenarios, such as log management, data aggregation, and transaction processing. Utilize what you learned in this article to achieve more efficient database operations!