MySQL Current Time Guide: NOW(), CURRENT_TIMESTAMP, DATE_FORMAT, Time Zone Settings & Best Practices

1. Introduction

When Do You Need to Handle the Current Time in MySQL?

In MySQL, retrieving the current time is required in many different scenarios. For example, the following use cases are common:

• Automatically inserting a timestamp when registering data
• For example, recording the creation date and time when saving order data or log data.
• Filtering data based on the current time
• For example, retrieving only data from the past 7 days or searching for records with future dates.
• Formatting dates and times for display
• Formatting date and time values for better readability when generating reports.
• Managing data expiration using the current time
• For example, determining whether a coupon is still valid by comparing it with the current time.

As you can see, properly retrieving and manipulating the current time in MySQL is an important skill in database management.

What You Will Learn in This Article

This article explains the following topics in detail:

• How to retrieve the current time in MySQL (NOW(), CURRENT_TIMESTAMP, etc.)
• How to change date and time formats (using DATE_FORMAT())
• Date and time calculations using the current time (using INTERVAL)
• How to change the time zone (SET SESSION time_zone)
• Using the current time as a default value (CURRENT_TIMESTAMP)
• Common errors and how to fix them (FAQ)

From the basics to advanced usage of handling the “current time” in MySQL, this guide provides practical SQL examples. Be sure to read through to the end.

2. How to Retrieve the Current Time in MySQL

List of Functions to Retrieve the Current Time in MySQL

MySQL provides several functions to retrieve the current time. Understand the differences and use them appropriately.

NOW() Function

NOW() is the most commonly used function in MySQL to retrieve the current time.
It returns both the date and the time.

SELECT NOW();

Output example:

2025-02-11 16:00:00

• NOW() returns the current system time.
• Because it is affected by the time zone, the displayed time may differ depending on your environment (explained in detail in the “Time Zone Settings” section).

How to Use CURRENT_TIMESTAMP

CURRENT_TIMESTAMP behaves almost the same as NOW(). It complies with the SQL standard and can also be used in other databases.

SELECT CURRENT_TIMESTAMP;

Output example:

2025-02-11 16:00:00

Get Only the Date with CURDATE()

CURDATE() is used when you want to retrieve only the current date (year, month, day).

SELECT CURDATE();

Output example:

2025-02-11

Get Only the Time with CURTIME()

If you want to retrieve only the current time (hour, minute, second), use CURTIME().

SELECT CURTIME();

Output example:

16:00:00

Which Function Should You Use?

3. How to Format the Current Time in MySQL

Custom Formatting with DATE_FORMAT()

Basic Syntax of DATE_FORMAT()

In MySQL, you can use the DATE_FORMAT() function to freely change the format of dates and times.

SELECT DATE_FORMAT(datetime_value, 'format_specifiers');

Example: Convert NOW() to the YYYY/MM/DD HH:MM format

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

Output:

2025/02/11 16:45

List of Common Format Specifiers

Extract Only the Time Part with TIME()

If you want to extract only the time portion from the datetime returned by NOW(), use the TIME() function.

SELECT TIME(NOW());

Output:

16:45:30

Extract Parts with YEAR(), MONTH(), and DAY()

To extract only specific parts, use the following functions.

Practical Examples of Formatting

The following SQL is useful for trying various formats in practice.

SELECT 
    NOW() AS 'Original datetime',
    DATE_FORMAT(NOW(), '%Y/%m/%d') AS 'YYYY/MM/DD format',
    DATE_FORMAT(NOW(), '%H:%i:%s') AS 'HH:MM:SS',
    TIME(NOW()) AS 'Time only',
    YEAR(NOW()) AS 'Year',
    MONTH(NOW()) AS 'Month',
    DAY(NOW()) AS 'Day';

4. Date/Time Calculations Using the Current Time in MySQL

Add/Subtract with INTERVAL

Basic Syntax

SELECT current_datetime + INTERVAL number unit;
SELECT current_datetime - INTERVAL number unit;

Add Time Based on NOW()

For example, to retrieve the datetime “one week later”:

SELECT NOW() + INTERVAL 7 DAY;

Output example:

2025-02-18 16:30:00

Calculate the Difference Between Two Dates with DATEDIFF()

SELECT DATEDIFF(NOW(), '2025-01-01');

Output example:

30

Filter by a Date Range with BETWEEN

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

5. MySQL Time Zone Settings

Check the Current Time Zone

SHOW VARIABLES LIKE '%time_zone%';

Output example:

+------------------+----------------+
| Variable_name    | Value          |
+------------------+----------------+
| system_time_zone | UTC            |
| time_zone        | SYSTEM         |
+------------------+----------------+

Change the Time Zone per Session

SET SESSION time_zone = 'Asia/Tokyo';

Change the Server’s Default Time Zone

Add the following to the configuration file (my.cnf):

[mysqld]
default_time_zone = 'Asia/Tokyo'

Get UTC Time with UTC_TIMESTAMP

SELECT UTC_TIMESTAMP();

Convert to Local Time with CONVERT_TZ()

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

6. How to Set the Current Time as a Default Value in MySQL

Set CURRENT_TIMESTAMP as the Default Value

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

Automatic Updates with ON UPDATE CURRENT_TIMESTAMP

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

Difference Between DATETIME and TIMESTAMP

Why You Cannot Use NOW() as a Default Value and the Solution

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

Solution:

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

7. Common MySQL Errors and Solutions (FAQ)

Cannot Use NOW() as a Default Value

Error Example

CREATE TABLE logs (
    created_at DATETIME DEFAULT NOW()
);

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

Solution

CREATE TABLE logs (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CURRENT_TIMESTAMP Time Is Incorrect

SHOW VARIABLES LIKE 'time_zone';

Solution

SET SESSION time_zone = 'Asia/Tokyo';

NOW() Result Is Off by One Hour

SHOW VARIABLES LIKE 'system_time_zone';

Solution

SET GLOBAL time_zone = 'Asia/Tokyo';

BETWEEN Range Filtering Does Not Work as Expected

SELECT * FROM orders
WHERE created_at BETWEEN '2025-02-01' AND '2025-02-28';

Solution

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

8. Best Practices for Handling the Current Time in MySQL

When to Use NOW() vs CURRENT_TIMESTAMP

When to Use TIMESTAMP vs DATETIME

Store in UTC and Convert to Local Time

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

Use >= and < Instead of BETWEEN

SELECT * FROM orders
WHERE created_at >= '2025-02-01 00:00:00' 
AND created_at < '2025-03-01 00:00:00';

Standardize the Usage of INTERVAL

SELECT NOW() + INTERVAL 1 DAY;

Properly Clean Up Old Data

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 1 YEAR LIMIT 1000;