MySQL NOW() Function

The `NOW()` function in MySQL returns the current date and time in the format `YYYY-MM-DD HH:MM:SS`. It is commonly used to capture the exact moment a SQL statement is executed. The result is based on the server's current time zone unless otherwise configured.

Usage

The `NOW()` function is used when you need the current timestamp for operations like data insertion, updates, or logging within SQL contexts such as `SELECT` statements or stored procedures. It is often utilized to track the timing of record creation or modification.

sql
SELECT NOW();
Didukung oleh

In this syntax, `NOW()` produces the current date and time when the query is executed.

Examples

1. Retrieve Current Date and Time

sql
SELECT NOW();
Didukung oleh

This example returns the current date and time from the server.

2. Insert Current Timestamp

sql
INSERT INTO logs (event, created_at)
VALUES ('User login', NOW());
Didukung oleh

Here, the `NOW()` function logs the exact time when a user logs in, storing it in the `created_at` column.

3. Update with Current Timestamp

sql
UPDATE orders
SET last_modified = NOW()
WHERE order_id = 102;
Didukung oleh

This example updates the `last_modified` column of a specific order to the current timestamp, indicating when the last change was made.

Tips and Best Practices

• Use in INSERT and UPDATE. Utilize `NOW()` to automatically timestamp records upon creation or modification.
• Combine with timezone settings. Ensure your database timezone settings match your application needs for accurate timestamps. Use functions like `CONVERT_TZ()` to manage time zones effectively.
• Consider performance impact. `NOW()` is evaluated once per statement, not per row, which mitigates performance overhead in large queries.
• Utilize for logging and auditing. Use `NOW()` to effectively track activity within your applications for debugging and auditing purposes.
• Data type compatibility. The `NOW()` function can be stored in `DATETIME` and `TIMESTAMP` columns, each having different properties regarding time zone handling and auto-updating.
• Transaction considerations. Be cautious of how `NOW()` values may vary between different statements in a transaction if executed at different times.
• Distinguish from CURRENT_TIMESTAMP(). While `NOW()` and `CURRENT_TIMESTAMP()` are often used interchangeably, subtle differences exist; `CURRENT_TIMESTAMP()` can be adjusted by the `DEFAULT CURRENT_TIMESTAMP` and `ON UPDATE CURRENT_TIMESTAMP` attributes in column definitions.