mysql date conversion

When using MySQL database, date processing and conversion are often performed, which is often used in data statistics, analysis and display. Therefore, it is very important for developers and database administrators to be proficient in MySQL date conversion methods.

MySQL provides rich date functions and syntax that can easily convert dates and times into different formats and strings. This article will focus on the syntax and examples of MySQL date conversion, and provide some common application scenarios and techniques.

Date format

In MySQL, dates and times are stored in the default format. The default date format is YYYY-MM-DD and the time format is HH:MM:SS. You can use the DATE_FORMAT() function to convert dates and times into strings in various formats.

The following table lists the MySQL date format options:

##%jDays in year (0 -366)%UThe number of weeks in the year (from Sunday)%uThe number of weeks in the year (from Monday)%XThe complete number of the year, if it is a Sunday, it is the new day The first day of the year, the new year will start on the last Saturday of the previous year %VThe number of weeks in the year (counted from Monday ), if this week is part of the next year, then this week will count as the first week of the new year.

Date functions

MySQL provides many date functions, which can be used for format conversion, query and calculation of date and time. Listed below are some common date functions and their descriptions.

1. NOW()

The NOW() function returns the current date and time. It can be used to add timestamps to fields in data tables.

Syntax:

SELECT NOW();

Result:

2022-01-01 16:12:07

2. DATE_FORMAT()

The DATE_FORMAT() function can convert date and time to specified format.

Syntax:

SELECT DATE_FORMAT(date,format);

Example:

SELECT DATE_FORMAT('2022-01-01 16:25:30','%m/%d/%Y %h:%i:%s %p');

Result:

01/01/2022 04:25:30 PM

3. DATE()

DATE( ) function returns the date portion of a date value.

Syntax:

SELECT DATE(date);

Example:

SELECT DATE('2022-01-01 16:30:00');

Result:

2022-01-01

4. DAY()

DAY( ) function returns the number of days in the date portion of a given date.

Syntax:

SELECT DAY(date);

Example:

SELECT DAY('2022-01-01 16:30:00');

Result:

01

5. MONTH()

MONTH( ) function returns the month of a given date.

Syntax:

SELECT MONTH(date);

Example:

SELECT MONTH('2022-01-01 16:30:00');

Result:

01

6. YEAR()

YEAR( ) function returns the year of a given date.

Syntax:

SELECT YEAR(date);

Example:

SELECT YEAR('2022-01-01 16:30:00');

Result:

2022

7. DATE_ADD()

DATE_ADD( ) function is used to add date and time values.

Syntax:

SELECT DATE_ADD(date, INTERVAL value unit);

Example:

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

Result:

2022-02-01

8. DATE_DIFF()

DATEDIFF( ) function calculates the number of days between two dates.

Syntax:

SELECT DATEDIFF(date1,date2);

Example:

SELECT DATEDIFF('2022-01-01','2021-12-31');

Result:

1

9. STR_TO_DATE()

STR_TO_DATE( ) function converts a string to date format.

Syntax:

SELECT STR_TO_DATE(str, format);

Example:

SELECT STR_TO_DATE('20220101','%Y%m%d');

Result:

2022-01-01

10. TIME()

TIME( ) function returns the time portion of a date/time value.

Syntax:

SELECT TIME(datetime);

Example:

SELECT TIME('2022-01-01 16:45:30');

Result:

16:45:30

Date conversion skills

Master the syntax and functions of MySQL date conversion It is very important, but it needs to be used flexibly in actual projects. Here are some common date conversion techniques.

1. Calculate the difference between two dates

In MySQL, you can use functions such as DATE_DIFF(), DATEDIFF(), TO_DAYS() to calculate the difference between two dates the difference between. For example, calculate the number of days between two dates:

SELECT DATEDIFF('2022-01-02','2021-12-31') AS days;

Result:

2

2. Convert timestamp to date format

If in the database Fields are stored as timestamps, which can be converted to date format using the FROM_UNIXTIME() function.

SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp),'%Y-%m-%d %H:%i:%s') AS date FROM tableName;

3. Convert date format to timestamp

If you need to convert the date format in MySQL to UNIX timestamp, you can use the UNIX_TIMESTAMP() function in the query statement .

SELECT UNIX_TIMESTAMP('2022-01-01 17:00:00') AS timestamp;

Result:

1641054000

4. Display the current month and year

Use the DATE_FORMAT() function to easily display the current month and year.

SELECT DATE_FORMAT(NOW(),'%Y年%m月') AS month;

Result:

2022年01月

Summary

MySQL date conversion is very important for developers and database administrators. This article introduces common date functions and syntax, as well as some common application scenarios and techniques. In actual projects, it needs to be used flexibly according to specific situations to achieve the best date format and effect.

Format (Format)	Description
%Y	Four-digit year
%y	Two-digit year
%m	Two-digit month
%c	Two-digit month, no With leading zeros
%d	two-digit day
%e	Date, no leading Zero
%H	hours (00-23)
%h	hours (01 -12)
%i	two-digit minutes
%s	two Number of seconds in digits
%p	"AM" or "PM"
%W	Full name of the week
%a	Abbreviation of the week
%M	of the month Full name
%b	Abbreviation of month

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