How to combine date and time values ​​together using TIMESTAMP function in MySQL

How to use the TIMESTAMP function in MySQL to combine date and time values ​​

Introduction:
The TIMESTAMP function in MySQL is used to convert and combine between a date and a time value Tool of. Using the TIMESTAMP function, we can combine a date value and a time value into a single field, or break a field containing date and time into separate date and time parts. This article will introduce how to use the TIMESTAMP function to combine and decompose date and time values.

1. Combine date and time into one field:

In MySQL, DATE and TIME type fields are used to store date and time values ​​respectively. If you need to combine them into one field, you can use the TIMESTAMP function. Here is an example:

SELECT TIMESTAMP('2022-01-01', '12:00:00') AS datetime;

The above code will merge the date '2022-01-01' and the time '12:00:00' into one field with date and time. The query results are as follows:

+---------------------+
| datetime            |
+---------------------+
| 2022-01-01 12:00:00 |
+---------------------+

2. Split the date and time into independent fields:

If we have a merged date and time field and want to split it into independent fields For the date and time part, you can also use the TIMESTAMP function. The following is an example:

SELECT DATE(datetime) AS date_part, TIME(datetime) AS time_part
FROM your_table;

The above code will extract the date and time parts from the datetime field in your_table table and store them in the date_part and time_part fields respectively. The query results are as follows:

+------------+-----------+
| date_part  | time_part  |
+------------+-----------+
| 2022-01-01 | 12:00:00  |
+------------+-----------+

3. Other uses of the TIMESTAMP function:

In addition to merging or decomposing date and time values, the TIMESTAMP function can also be used to perform other functions related to date and time. Related operations, such as calculating the difference between dates, adding or subtracting certain time intervals, etc. Here are some examples:

1. Calculate the difference in days between two dates:

SELECT DATEDIFF('2022-06-01', '2022-01-01') AS day_diff;

2. Add a period of time to a date:

SELECT DATE_ADD('2022-01-01', INTERVAL 1 HOUR) AS new_date;

3. Subtract a period of time from a date:

SELECT DATE_SUB('2022-01-01', INTERVAL 1 WEEK) AS new_date;

Conclusion:
TIMESTAMP function is one of the important tools for processing date and time values ​​in MySQL. By combining date and time into one field or breaking it into separate date and time parts, we can more easily manipulate and calculate date and time values. At the same time, the TIMESTAMP function also provides other operations and calculation functions on date and time, which can meet different needs.

Related code examples can refer to the following link: https://www.mysqltutorial.org/mysql-introduction/mysql-create-database/

Reference materials:

• MySQL official documentation: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

The above is the detailed content of How to combine date and time values ​​together using TIMESTAMP function in MySQL. For more information, please follow other related articles on the PHP Chinese website!