mysql time conversion

Time conversion in MySQL refers to the process of converting time data in different formats to each other, such as converting dates to timestamps, converting timestamps to dates, etc. Correct time conversion can make it easier for us to manage and use time data in the database. Next, we will start with different time formats and introduce the commonly used time conversion methods in MySQL.

1. Convert date to timestamp

In MySQL, you can use the UNIX_TIMESTAMP function to convert date to timestamp. The syntax of the UNIX_TIMESTAMP function is as follows:

UNIX_TIMESTAMP(date)

Among them, date is a legal date expression, which can be a date-type column name, a string-type date, or a date-type variable. Here are some examples:

SELECT UNIX_TIMESTAMP('2022-07-01 00:00:00');

SELECT UNIX_TIMESTAMP(NOW());

SELECT UNIX_TIMESTAMP(date_column) FROM table_name;

In the above example, the first SELECT statement converts the date string '2022-07-01 00:00:00' into a timestamp; the second SELECT statement converts the current time Convert to timestamp; the third SELECT statement converts the date column in the table to timestamp. It should be noted that the UNIX_TIMESTAMP function returns a second-level timestamp. If you need a millisecond-level timestamp, you can use the UNIX_TIMESTAMP*1000 function.

2. Convert timestamp to date

Similarly, in MySQL, you can use the FROM_UNIXTIME function to convert timestamps to dates. The syntax of the FROM_UNIXTIME function is as follows:

FROM_UNIXTIME(unix_timestamp, format)

Among them, unix_timestamp is the timestamp to be converted, which can be a number or a date column name; format is the format string of the date. For example, to convert the timestamp to a date in the 'yyyy-mm-dd hh:mm:ss' format, you can use the following statement:

SELECT FROM_UNIXTIME(1625041238, '%Y-%m-%d %H:%i:%s');

This statement will return 2021-06-30 11:53: 58, and individual placeholders in the format string can also be adjusted freely. The following are some commonly used formatting characters:

##%aAbbreviated week name (for example: Sun to Sat)%bAbbreviated month name (for example: Jan to Dec)%pAM or PM (capital letters)

Formatting characters	Meaning
%Y	Four-digit year (for example: 2022)
%m	Two-digit month (for example: 01 or 12)
%d	Two-digit date (for example: 01 or 31)
%H	Two-digit hour (for example: 00 or 23)
%i	Two-digit minute (for example: 00 or 59)
%s	Two-digit seconds (for example: 00 or 59)
%f	Microseconds (000000 ~ 999999)
%W	Week (for example: Sunday to Saturday)

Convert date string to date

When we save date data, we often save it in the form of a string. If we need to perform date operations, we need to convert the string into a date type. In MySQL, you can use the STR_TO_DATE function to convert a string into a date. The syntax of the STR_TO_DATE function is as follows:

STR_TO_DATE(str, format)

Among them, str is the string to be converted, and format is the format string of the date. For example, to convert the string '2022-07-01' to date type, you can use the following statement:

SELECT STR_TO_DATE('2022-07-01', '%Y-%m-%d');

This statement will return data in the form of date: 2022-07-01.

Convert date to string

Similarly, in MySQL, you can use the DATE_FORMAT function to convert date to string. The syntax of the DATE_FORMAT function is as follows:

DATE_FORMAT(date, format)

Among them, date is the date to be converted, which can be a date column name or variable, or a date in the form of a string; format is the formatting character of the date string. For example, to convert the date into a string in the 'yyyy-mm-dd' format, you can use the following statement:

SELECT DATE_FORMAT('2022-07-01', '%Y-%m-%d');

This statement will return the string format of '2022-07-01'.

To sum up, time conversion in MySQL mainly covers four aspects: converting dates to timestamps, converting timestamps to dates, converting date strings to dates, and converting dates to characters. string. Correct time conversion can make it easier for us to manage and use time data in the database.

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