MySQL timestamp is a very important data type, which can store date, time or date plus time. In the actual development process, rational use of timestamps can improve the efficiency of database operations and facilitate time-related queries and calculations. This article will discuss the functions, features, and application scenarios of MySQL timestamps, and explain them with specific code examples.
There are two types of timestamps in MySQL, one is TIMESTAMP
and the other is DATETIME
. They have different functions and characteristics, which are introduced below:
TIMESTAMP
:
1970- 01-01 00:00:01
UTC to 2038-01-19 03:14:07
UTC. TIMESTAMP
column will automatically update to the current timestamp. DATETIME
:
1000-01-01 00:00:00
to 9999-12-31 23:59:59
. Record the creation time and update time of data:
CREATE TABLE example_table ( id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
In this example, we create a table that records the creation time and update time of the data. By setting the created_at
column to DEFAULT CURRENT_TIMESTAMP
, the current timestamp is automatically recorded when inserting data; and setting the updated_at
column to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
, then update the timestamp when the data is updated.
Query data within the specified time range:
SELECT * FROM example_table WHERE created_at >= '2022-01-01 00:00:00' AND created_at < ;= '2022-12-31 23:59:59';
This query statement will return data records created in 2022. Time range queries can be easily performed using timestamps.
Calculation time interval:
SELECT TIMEDIFF('2022-01-01 12:00:00', '2022-01-01 08:00:00' );
This query statement will return the time interval between two timestamps, which can be used to calculate the difference between two dates and times.
MySQL timestamp is a very practical data type that can help us record the time information of data, perform time range queries, calculate time intervals, etc. operate. Proper use of timestamps can improve the efficiency of database operations and facilitate data management and analysis. I hope this article will help readers understand the functions, features and application scenarios of MySQL timestamps.
The above is the detailed content of Understanding MySQL timestamps: functions, features and application scenarios. For more information, please follow other related articles on the PHP Chinese website!