MySQL NOW() Function

The NOW() function retrieves the current date and time from the server. It is commonly used in queries to log timestamps or compare data with the current datetime.

Definition and Usage

• Purpose: Returns the current date and time as per the server's system clock.
• Output Format:
  • String: YYYY-MM-DD HH:MM:SS
  • Numeric: YYYYMMDDHHMMSS.uuuuuu (if used in a numeric context).

Syntax

NOW()

Technical Details

• Introduced in: MySQL 4.0 and later.
• Return Type:
  • String (default).
  • Numeric if used in arithmetic operations.
• Precision: Supports microsecond precision if enabled.

Examples

Example 1: Get Current Date and Time

SELECT NOW();

Result: 2024-12-29 14:45:30 (Example output)

Example 2: Perform Arithmetic Operations

SELECT NOW() + 1;

Result: 20241229144531 (Adds 1 second in numeric format)

Example 3: Using NOW() in a Table

Log the current timestamp when a new record is inserted:

INSERT INTO log_table (event, event_time) VALUES ('User Login', NOW());

Example 4: Compare with a Date Column

Retrieve records added today:

SELECT * FROM orders WHERE DATE(order_date) = DATE(NOW());

Example 5: Microsecond Precision

SELECT NOW(6);

Result: 2024-12-29 14:45:30.123456 (Example with microseconds)

Key Notes

1. The NOW() function is timezone-aware based on the server configuration.
2. For date-only values, use CURDATE(); for time-only values, use CURTIME().
3. Often used in triggers and logs to mark the timestamp of events.

Use Cases

• Logging event times.
• Filtering or analyzing data based on the current datetime.
• Automating time-sensitive tasks, such as scheduling or auditing.