How to convert date and time in mysql

MySQL is a popular open source relational database management system that supports a variety of data types, including date and time. MySQL provides a variety of functions to handle date and time data, including date-time conversion.

In MySQL, dates and times can be represented in a variety of formats. For example, dates can be represented using the format "YYYY-MM-DD" and times can be represented using the format "HH:MM:SS". In addition, MySQL also supports date and time formats, such as "YYYY-MM-DD HH:MM:SS".

When performing date and time conversion, we can use MySQL's date and time function. The following are some commonly used date and time functions and their functions:

1. DATE_FORMAT(date,format): Format the date into the specified format.
2. STR_TO_DATE(date,format): Convert string to date.
3. DATE_ADD(date,INTERVAL expr unit): Adds a specified amount of time to the date.
4. DATE_SUB(date,INTERVAL expr unit): Subtract a specified amount of time from the date.
5. UNIX_TIMESTAMP(): Returns the UNIX timestamp of the current date and time.
6. FROM_UNIXTIME(unix_timestamp): Convert UNIX timestamp to date time format.

Here are some examples that demonstrate how to use these functions for date-time conversions:

1. Format dates into different formats:

SELECT DATE_FORMAT('2022-01-01','%m/%d/%Y') AS 'Formatted Date';

This will return a format of "01/01/2022" String.

2. Convert string to date:

SELECT STR_TO_DATE('01/01/2022','%m/%d/%Y') AS 'Converted Date';

This will return a date value with the date format "2022-01-01".

3. Add the amount of time to the date:

SELECT DATE_ADD('2022-01-01', INTERVAL 1 MONTH) AS 'New Date';

This will return a date value with the date "2022-02-01".

4. Subtract the amount of time from the date:

SELECT DATE_SUB('2022-01-01', INTERVAL 1 DAY) AS 'New Date';

This will return a date value with the date "2021-12-31".

5. Return the UNIX timestamp of the current date and time:

SELECT UNIX_TIMESTAMP() AS 'Current UNIX Timestamp';

This will return an integer value , representing the UNIX timestamp of the current date and time.

6. Convert UNIX timestamp to datetime format:

SELECT FROM_UNIXTIME(1640995200) AS 'Converted Date';

This will return a date The date and time value is in the time format "2022-01-01 00:00:00".

When doing date and time conversion, we need to understand the different formats and functions of date and time in MySQL in order to correctly process and convert date and time data. In actual development, we often need to process date and time data, so it is very important to understand date and time functions.

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