How to handle date and time data in MySQL?

How to handle date and time data in MySQL?

MySQL is a popular relational database management system that is widely used in a variety of applications. In many applications, date and time data need to be processed, such as recording user registration time, publishing time, etc. This article will explain how to correctly handle date and time data in MySQL and provide some code examples.

Date data types in MySQL

MySQL provides a variety of date and time data types, the most commonly used of which are DATE, TIME, DATETIME and TIMESTAMP.

• DATE stores date values ​​in the format of 'YYYY-MM-DD'.
• TIME stores time values ​​in the format of 'HH:MM:SS'.
• DATETIME stores date and time values ​​in the format 'YYYY-MM-DD HH:MM:SS'.
• TIMESTAMP stores date and time values ​​in the format of 'YYYY-MM-DD HH:MM:SS', and automatically records the timestamp.

Create a table and add date and time data

First, we need to create a table to store date and time data. You can use the following SQL statement to create a table named "users":

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  registration_date DATE,
  last_login_time TIMESTAMP
);

The above SQL statement creates a table named "users", which has four fields: id, name, registration_date and last_login_time . id is the primary key and is automatically incremented; name is the user name; registration_date is the registration date; last_login_time is the last login time, using the TIMESTAMP data type.

Insert data:

INSERT INTO users (name, registration_date) VALUES ('张三', '2021-01-01');

This SQL statement inserts a record into the "users" table, setting the name field to 'Zhang San' and the registration_date field to '2021-01-01'. The last_login_time field automatically records the current timestamp.

Querying and filtering date data

In MySQL, you can use various functions and operators to query and filter date data.

Query today’s registered users:

SELECT * FROM users WHERE registration_date = CURDATE();

Query users whose registration date is this month:

SELECT * FROM users WHERE MONTH(registration_date) = MONTH(CURDATE());

Filter out users who have logged in within the past week:

SELECT * FROM users WHERE last_login_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Update date and time data

You can use the UPDATE statement to update date and time data.

For example, update the user's last login time to the current time:

UPDATE users SET last_login_time = CURRENT_TIMESTAMP WHERE id = 1;

Delete date and time data

You can use the DELETE statement to delete date and time data.

For example, delete users whose registration date is earlier than '2020-01-01':

DELETE FROM users WHERE registration_date < '2020-01-01';

Summary

This article introduces how to process date and time data in MySQL, Including creating tables, inserting data, querying and filtering data, updating data, and deleting data. Correct processing of date and time data is an important part of database applications. I hope these examples can help readers better understand and apply MySQL's date and time functions.

The above is the detailed content of How to handle date and time data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!