How to use the NOW function in MySQL to get the current date and time

How to use the NOW function in MySQL to obtain the current date and time

In the MySQL database, the NOW() function is a very commonly used function, which is used to obtain the current date and time. Whether it is setting default values ​​when creating a table, recording timestamps when inserting new data, or performing time-related filtering when querying data, the NOW() function can help us quickly obtain the current date and time information. This article will introduce in detail how to use the NOW() function in MySQL and provide corresponding code examples.

The usage of the NOW() function is very simple. It does not require any parameters, just call it directly. It will return a string containing the date and time in the format "YYYY-MM-DD HH:MM:SS". Among them, YYYY represents the year, MM represents the month, DD represents the date, HH represents the hour, MM represents the minute, and SS represents the second. Below are some common scenarios for using the NOW() function.

1. Set default values ​​when creating a table
   When creating a MySQL table, we can specify a default value so that it automatically records the current date and time when inserting new data. For example, the following code demonstrates creating a table named "users" that contains a "create_time" field whose default value is the current date and time.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    create_time TIMESTAMP DEFAULT NOW()
);

When inserting new data, if the value of the "create_time" field is not explicitly specified, MySQL will automatically insert the current date and time as the default value.

2. Record timestamp when inserting data
   In some cases, we want to record the current date and time when inserting data manually instead of using the default value. At this time, you can use the NOW() function to get the current date and time and insert it as the value of the field. For example, the following code demonstrates how to insert a new record into the "users" table mentioned above and manually specify the value of the "create_time" field as the current date and time.

INSERT INTO users (name, create_time)
VALUES ('John', NOW());

After executing the above code, a new record named "John" will be inserted into the "users" table, and the timestamp at the time of insertion will be recorded.

3. Perform time-related filtering when querying data
   When querying data, we often need to use time to filter, for example, only query records within a certain date range, or only query the most recent period Time records, etc. At this time, you can also use the NOW() function to obtain the current date and time, and combine it with other functions and statements for filtering. For example, the following code demonstrates how to query the "users" table for records created with today's date.

SELECT * FROM users
WHERE DATE(create_time) = CURDATE();

In the above code, the DATE() function is used to extract the date part in the "create_time" field and compared with today's date returned by the CURDATE() function. This will filter out records created today.

It should be noted that the date and time returned by the NOW() function are the current date and time of the database server. If the database server and application server are deployed in different places, the obtained date and time may be inconsistent with the date and time of the application location. In this case, you should use the date and time functions local to the database server to obtain accurate results.

In summary, in the MySQL database, the current date and time can be easily obtained using the NOW() function. Whether it is setting default values ​​when creating a table, recording timestamps when inserting data, or performing time-related filtering when querying data, the NOW() function can help us handle dates and times conveniently. I hope the code examples and instructions provided in this article can help you better understand and use the NOW() function.

The above is the detailed content of How to use the NOW function in MySQL to get the current date and time. For more information, please follow other related articles on the PHP Chinese website!