MySQL Current Time Guide (NOW(), TIMESTAMP, Time Zones)

1. Introduction

What are the cases for handling the current time in MySQL?

• Automatic timestamp entry when registering data
• For instance, when saving order data or log data, record the creation date and time of the data.
• Filtering data based on the current time
• For example, retrieving only data from the past 7 days or searching for data with future dates.
• Manipulating and displaying dates and times
• When creating reports, format the date and time for better readability.
• Managing data expiration using the current time
• For example, determine a coupon’s expiration by comparing it with the current time.

What you’ll learn in this article

• How to retrieve the current time in MySQL (NOW(), CURRENT_TIMESTAMP, etc.)
• Changing date and time formats (how to use DATE_FORMAT())
• Date and time calculations using the current time (date manipulation with INTERVAL)
• How to change the time zone (SET SESSION time_zone)
• Using the current time as a default value (leveraging CURRENT_TIMESTAMP)
• Common error causes and solutions (FAQ)

2. How to Get the Current Time in MySQL

List of Functions to Retrieve the Current Time in MySQL

Using the NOW() Function

SELECT NOW();

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 from expectations depending on the environment (see the “Time Zone Settings” section for details).

How to Use CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP;

2025-02-11 16:00:00

Getting Only the Date with CURDATE()

SELECT CURDATE();

2025-02-11

Getting Only the Time with CURTIME()

SELECT CURTIME();

16:00:00

Which Function Should You Use?

3. How to format the current time in MySQL

DATE_FORMAT() Custom Format

DATE_FORMAT() Basic Syntax

SELECT DATE_FORMAT(datetime_to_format, 'format_specifier');

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

2025/02/11 16:45

List of format specifiers

Retrieve only the time part with TIME()

SELECT TIME(NOW());

16:45:30

Partial extraction with YEAR(), MONTH(), DAY()

Practical examples of format changes

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

4. Date and time calculations using the current time in MySQL

Using INTERVAL for addition and subtraction

Basic syntax

SELECT current_time + INTERVAL numeric_value unit;
SELECT current_time - INTERVAL numeric_value unit;

Addition based on NOW()

SELECT NOW() + INTERVAL 7 DAY;

2025-02-18 16:30:00

Using DATEDIFF() to calculate the difference between two dates

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

30

Using BETWEEN to filter a date range

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%';

+------------------+----------------+
| 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

[mysqld]
default_time_zone = 'Asia/Tokyo'

Retrieve UTC time using UTC_TIMESTAMP

SELECT UTC_TIMESTAMP();

Convert to local time using CONVERT_TZ()

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

6. How to set the current time as the default value in MySQL

CURRENT_TIMESTAMP set as default value

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

Automatic update 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 type and TIMESTAMP type

Why NOW() cannot be used 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)

NOW() cannot be used 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 off

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 specification does not work correctly

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 Current Time in MySQL

When to use NOW() vs CURRENT_TIMESTAMP

When to use TIMESTAMP vs DATETIME

Design: 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 use of INTERVAL

SELECT NOW() + INTERVAL 1 DAY;

Clean up data correctly

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