How to modify mysql time

1. Introduction to Mysql timestamp

The timestamp (Timestamp) in MySQL is a data type used to represent the precise time when an event occurs. It is often used to record the creation time and update time of data, as well as other time-related operations.

Time stamps in Mysql come in two different formats: Unix timestamp and Mysql timestamp.

Unix timestamp refers to the number of seconds difference between the current time and 0:00:00 on January 1, 1970. Unix Timestamp is the term used in MySQL for this type of timestamp format.. It can use Mysql's built-in UNIX_TIMESTAMP() function to convert datetime type time data into a Unix timestamp, for example:

SELECT UNIX_TIMESTAMP(NOW()); //Get the current Unix timestamp

Mysql's timestamp is represented in YYYY-MM-DD HH:MI:SS format. For example, it can use Mysql's built-in NOW() function to obtain the current timestamp:

SELECT NOW(); //Get the current Mysql timestamp

In addition, Mysql also provides FROM_UNIXTIME () function can convert Unix timestamp to Mysql timestamp, for example:

SELECT FROM_UNIXTIME(1602590500); //Convert Unix timestamp 1602590500 to Mysql timestamp

2. Modification time The stamp is the current timestamp

Sometimes, we need to change the timestamp of a record in the database to the current timestamp. In Mysql, you can use the NOW() function to get the current timestamp, and then use the UPDATE statement to update the timestamp field of the corresponding record to the current timestamp, for example:

UPDATE table_name SET timestamp_field = NOW() WHERE id = 1;

Among them, table_name is the table name, timestamp_field is the timestamp field name, and id is the primary key value of the target record.

If you need to batch update the timestamps of multiple records to the current timestamp, you can use the UPDATE statement combined with conditional statements, for example:

UPDATE table_name SET timestamp_field = NOW() WHERE condition;

Among them, condition is a conditional statement for filtering records that need to be updated. To update all records with a status of 1 in the timestamp field, you can use the following statement:

UPDATE table_name SET timestamp_field = NOW() WHERE status = 1;

3. Modify the timestamp to the specified Timestamp

Sometimes it is necessary to modify the timestamp of a certain record to a specified timestamp instead of the current timestamp. In Mysql, you can use the DATE_FORMAT() function to convert the specified time string into a Mysql timestamp, for example:

SELECT DATE_FORMAT('2020-10-13 18:03:00', '%Y- %m-%d %H:%i:%s'); //Convert the time string to a Mysql timestamp

Then, you can use the UPDATE statement to update the timestamp field of the corresponding record to the specified Timestamp, for example:

UPDATE table_name SET timestamp_field = '2020-10-13 18:03:00' WHERE id = 1;

where, '2020-10-13 18:03 :00' is the specified timestamp string, table_name is the table name, timestamp_field is the timestamp field name, and id is the primary key value of the target record.

To batch update the timestamps of multiple records to a specified timestamp, you can also use the UPDATE statement with conditional statements. For example:

UPDATE table_name SET timestamp_field = '2020-10-13 18:03:00' WHERE condition;

where condition is a conditional statement to filter the records that need to be updated. For example, if you need to update the timestamp field of all records with status 1 to 2020-10-13 18:03:00, you can use the following statement:

UPDATE table_name SET timestamp_field = '2020-10-13 18 :03:00' WHERE status = 1;

The above is the detailed content of How to modify mysql time. For more information, please follow other related articles on the PHP Chinese website!