Comprehensive Guide to Retrieving Current Date and Time in MySQL

Introduction

Accurately recording timestamps for data creation or modification is a fundamental requirement in database operations. MySQL provides multiple built-in functions for handling date and time values, with retrieving current system time being one of the most essential functionalities. This paper systematically examines core methods for obtaining current date and time in MySQL based on practical development scenarios.

Core Characteristics of NOW() Function

The NOW() function is the most commonly used method in MySQL for obtaining current date and time, returning the server's current date and time value. According to MySQL official documentation, the return format of NOW() depends on the usage context: it returns "YYYY-MM-DD HH:MM:SS" format in string context and YYYYMMDDHHMMSS format in numeric context.

Basic syntax examples:

SELECT NOW();
-- Returns: '2024-01-15 14:30:45' (string context)
SELECT NOW() + 0;
-- Returns: 20240115143045 (numeric context)

Timestamp Application in INSERT Statements

In actual database operations, there's often a need to automatically record timestamps when inserting records. The original question's code example illustrates this typical scenario:

-- Original problematic code
INSERT INTO servers (
  server_name, online_status, exchange, disk_space, network_shares
) VALUES(
  'm1', 'ONLINE', 'ONLINE', '100GB', 'ONLINE' 'DATETIME' 
)

The correct approach should use the NOW() function:

-- Corrected code
INSERT INTO servers (
  server_name, online_status, exchange, disk_space, network_shares, c_time
) VALUES(
  'm1', 'ONLINE', 'exchange', 'disk_space', 'network_shares', NOW()
)

Note that the number of values in the VALUES list must exactly match the column name list, and the NOW() function doesn't require quotation marks since it returns a datetime value rather than a string.

Comparative Analysis of Related Time Functions

Besides the NOW() function, MySQL provides other functions for obtaining time-related information:

CURDATE() and CURRENT_DATE()

These functions return the current date (without time component):

SELECT CURDATE();      -- Returns: '2024-01-15'
SELECT CURRENT_DATE(); -- Returns: '2024-01-15'
SELECT CURDATE() + 0;  -- Returns: 20240115

CURTIME() and CURRENT_TIME()

These functions return the current time (without date component):

SELECT CURTIME();      -- Returns: '14:30:45'
SELECT CURRENT_TIME(); -- Returns: '14:30:45'
SELECT CURTIME() + 0;  -- Returns: 143045

SYSDATE() Function

SYSDATE() is functionally similar to NOW() but has important behavioral differences:

SELECT NOW(), SLEEP(2), NOW();
-- Returns identical time values (statement execution start time)

SELECT SYSDATE(), SLEEP(2), SYSDATE();
-- Returns different time values (actual function execution time)

This distinction requires special attention in stored procedures, triggers, and replication environments, as SYSDATE()'s non-deterministic nature may affect query result consistency.

Behavioral Characteristics of Time Functions

MySQL time functions exhibit several important behavioral characteristics:

Query Execution Consistency

Multiple calls to NOW() and similar functions within the same query return identical results, as they are computed once at query start:

SELECT NOW() AS time1, SLEEP(1), NOW() AS time2;
-- time1 and time2 have identical values

Timezone Handling

NOW(), CURDATE(), CURTIME() and similar functions return time values in the session timezone, while UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP() return UTC time:

SELECT NOW(), UTC_TIMESTAMP();
-- Returns different time values based on session timezone settings

Advanced Application Scenarios

NOW() Function with Precision

MySQL versions 5.6.4 and above support specifying fractional second precision:

SELECT NOW(3); -- Returns: '2024-01-15 14:30:45.123'

Time Calculation and Formatting

Combining DATE_ADD() and DATE_FORMAT() functions enables complex time processing:

-- Get time 30 minutes from now
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);

-- Format time output
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y at %h:%i %p');
-- Returns: 'Monday, January 15, 2024 at 02:30 PM'

Best Practice Recommendations

Table Design Considerations

When designing database tables, choose appropriate types for timestamp fields:

• DATETIME: Broader support range, no timezone conversion involved
• TIMESTAMP: Automatic timezone conversion, but smaller range (1970-2038)
• Choose whether to enable fractional second precision based on specific requirements

Performance Optimization

In scenarios involving frequent time-related data queries:

• Avoid using time functions in WHERE conditions, as this may cause index inefficiency
• Consider using generated columns to store computed time values
• For fixed time range queries, use BETWEEN operator instead of function calculations

Conclusion

MySQL provides rich time handling functions, with NOW() being the most direct and effective method for obtaining current date and time. Understanding the behavioral characteristics and applicable scenarios of different time functions is crucial for developing efficient and reliable database applications. In actual projects, appropriate time functions should be selected based on specific requirements, with attention to best practices regarding timezones, precision, and performance aspects.