Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the MySQL date function has, which is very detailed and has a certain reference value. Friends who are interested must read it!

MYSQL Common date function and date conversion format function

1. DAYOFWEEK (date)

SELECT DAYOFWEEK ('2016-01-16') SELECT DAYOFWEEK ('2016-01-16 00 00')-> 7 (means, remember: Sunday = 1, Monday = 2,. Saturday = 7)

2. WEEKDAY (date)

SELECT WEEKDAY ('2016-01-16') SELECT WEEKDAY ('2016-01-16 00 date 00')-> 5 (indicates that the return number of the week is the sequence number of the week. In Western calendars, the week usually begins on Sunday and starts counting with 0, so remember: 0 = Monday, 1 = Tuesday,... 5 = Saturday)

3. DAYOFMONTH (date)

SELECT DAYOFMONTH ('2016-01-16') SELECT DAYOFMONTH ('2016-01-16 00 date 0000')-> 16 (indicates the day of the month when date is returned, the 1st returns 1st, and the 31st returns 31)

4. DAYOFYEAR (date)

SELECT DAYOFYEAR ('2016-03-31') SELECT DAYOFYEAR ('2016-03-31 00 date 0000')-> 91 (indicates the day of the year when 01.01 is returned.

5. MONTH (date)

SELECT MONTH ('2016-01-16') SELECT MONTH ('2016-01-16 00 date 0000')-> 1 (indicates the month of the year in which the date is returned, the month in January and 12 in December)

6. DAYNAME (date)

SELECT DAYNAME ('2016-01-16') SELECT DAYNAME ('2016-01-16 00 date 0000')-> Saturday (indicates that the full name of the day of the week is returned)

7. MONTHNAME (date)

SELECT MONTHNAME ('2016-01-16') SELECT MONTHNAME ('2016-01-16 00 date 0000')-> January (indicates the English name of the month of the year that returned the date)

8. QUARTER (date)

SELECT QUARTER ('2016-01-16') SELECT QUARTER ('2016-01-16 00 date 0000')-> 1 (indicates the quarter of the year in which the date is returned, and returns 1, 2, 3, 4)

9. WEEK (date,index)

SELECT WEEK ('2016-01-03') SELECT WEEK ('2016-01-03, 0) SELECT WEEK (' 2016-01-03, 1)-> 1 (this function returns the week ordinal of date, date (01.03) is Sunday, the default is Sunday as the first day of the week, the function returns 1 here can be understood in two ways: 1, return 0 in the first week, return 1 in the second week. 2. Start counting with the full week of the year. The first week returns 1, the second week returns 2, and the last week returns 53)-> 1 (the default index of week () is 0. So the result is the same as above)-> 0 (when index is 1, the first day of the week is Monday, so Monday the 4th is the beginning of the second week)

10. YEAR (date)

SELECT YEAR ('70-01-16') SELECT YEAR ('2070-01-16') SELECT YEAR ('69-01-16 00 SELECT YEAR')-> 1970 (indicating the 4-digit year in which date is returned)-> 2070-> 1969

It should be noted that if the year has only two digits, then the automatic completion mechanism is limited by the default time of 1970.01.01, and the completion 19 of > = 70

< 70 的补全 20 11、HOUR(time) SELECT HOUR('11:22:33') SELECT HOUR('2016-01-16 11:22:33')->

11-> 11

Returns the hourvalue of the date or time, in the range of 0-23

12. MINUTE (time)

SELECT MINUTE ('11 22 33') SELECT MINUTE ('2016-01-16 11 15 14 14 44)

Returns the detail value of the time, in the range of 0-59

13. SECOND (time)

SELECT SECOND ('11 22 33') SELECT SECOND ('2016-01-16 11 11 Suzhou 44 14 22')-> 33-> 22

Returns the detail value of the time, in the range of 0-59

14. PERIOD_ADD (month,add)

SELECT PERIOD_ADD (1601) SELECT PERIOD_ADD (191602)-> 201603-> 191605-> 191511

This function returns the result of adding or subtracting month. The format of month is yyMM or yyyyMM, and the results are all in yyyyMM format. Add can pass negative values.

15. PERIOD_DIFF (monthStart,monthEnd)

SELECT PERIOD_DIFF (1601 ~ 1603) SELECT PERIOD_DIFF (191602 ~ 191607) SELECT PERIOD_DIFF (1916-02 ~ 1916-07) SELECT PERIOD_DIFF (1602 ~ 9002)->-2->-5-> 5-> 312

This function returns the number of months between monthStart-monthEnd

16. DATE_ADD (date,INTERVAL number type), same as ADDDATE ()

SELECT DATE_ADD ("2015-12-31 23:59:59", INTERVAL 1 SECOND) SELECT DATE_ADD ("2015-12-31 23:59:59", INTERVAL 1 DAY) SELECT DATE_ADD ("2015-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND) SELECT DATE_ADD ("2016-01-01 00:00:00" INTERVAL "- 1 10" DAY_HOUR)-> 2016-01-01 0000-> 2016-01-01 23-059-> 2016-01-01 00-01-01 00-01-0100-> 2015-12-30 14:00:00

DATE_ADD () and ADDDATE () return the result of the date operation

1. The format of date can be "15-12-31", "15-12-31 23:59:59" or "2015-12-31 23:59:59". If the parameter date is in date format, the result will be returned in date format. If the parameter date is in datetime format, the result in datetime format will be returned.

2. Type format:

SECOND second SECONDS

MINUTE minute MINUTES

HOUR time HOURS

DAY days DAYS

MONTH monthly MONTHS

YEAR year YEARS

MINUTE_SECOND minutes and seconds "MINUTES:SECONDS"

HOUR_MINUTE hours and minutes "HOURS:MINUTES"

DAY_HOUR days and hours "DAYS HOURS"

YEAR_MONTH year and month "YEARS-MONTHS"

HOUR_SECOND hours, minutes, "HOURS:MINUTES:SECONDS"

DAY_MINUTE days, hours, minutes "DAYS HOURS:MINUTES"

DAY_SECOND days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"

3. In addition, if you do not use a function, you can also consider using the operator "+", "-". The example is as follows:

SELECT "2016-01-01"-INTERVAL 1 SECOND SELECT "2016-01-01"-INTERVAL 1 DAY SELECT '2016-12-31 23 INTERVAL 59' + INTERVAL 1 SECOND SELECT '2016-12-31 23 23 Fraser 59' + INTERVAL "1:1" MINUTE_SECOND

Return the result:

-> 2015-12-31 23mm 59 purl 59-> 2015-12-31-> 2017-01-01 00VOV 0000-> 2017-01-01 00:01:00

17. DATE_SUB (date,INTERVAL number type), same as SUBDATE ()

The usage and DATE_ADD () are similar to ADDDATE (), one is to add, the other is to subtract, refer to 16:00 when using, please refer to DATE_ADD () and ADDDATE () for specific usage.

18. TO_DAYS (date)

SELECT TO_DAYS ('2016-01-16') SELECT TO_DAYS ('20160116') SELECT TO_DAYS ('160116')-> 736344-> 736344-> 736344

Returns the total number of days from year 0 to date date

19. FROM_DAYS (date)

SELECT FROM_DAYS-> 0001-01-02

Returns the date value of the number of days since the year 2000

20. DATE_FORMAT (date,format): format the date according to the parameters.

SELECT DATE_FORMAT ('2016-01-16 22 SELECT DATE_FORMAT 2300 SELECT DATE_FORMAT% H% k% I% r% T% S% w') SELECT DATE_FORMAT (' 2016-01-16 22 22 V% T% S% w') SELECT DATE_FORMAT ('2016-01-16 22 22 V% T% S% w') '% Y-%m-%d% HRV% iRO% s')-> Saturday January 2016-> 16th 16 Sat 16 01 Jan 016-> 22 22 10 10:23:00 PM 22:23:00 00 6-> 2016-01-16 22:23:00

The format of format is listed:

% M month name (January... December)

% W week name (Sunday... Saturday)

% D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )

% Y year, number, 4 digits

% y year, number, 2 digits

% an abbreviated name of the week (Sun... Sat)

Number of days in% d month, number (00. 31)

Number of days in% e month, number (0... 31)

% m month, number (01... 12)

% c month, number (1 …... 12)

% b abbreviated month name (Jan... Dec)

% j days of the year (001... 366)

% H hours (00. 23)

% k hours (0. 23)

% h hours (01 …... 12)

% I hours (01 …... 12)

% l hours (1 …... 12)

% I minutes, number (00. 59)

% r time, 12 hours (hh:mm:ss [AP] M)

% T time, 24 hours (hh:mm:ss)

% s seconds (00. 59)

% s seconds (00. 59)

% p AM or PM

% w days in a week (0=Sunday... 6=Saturday)

% U week (0 …... Here Sunday is the first day of the week

% u weeks (0. Here Monday is the first day of the week

% character%)

TIME_FORMAT (time,format):

The usage is similar to DATE_FORMAT (), but TIME_FORMAT only deals with hours, minutes, and seconds (the rest of the symbols produce a null value or 0)

21. Get the current date of the system

SELECT CURDATE () SELECT CURRENT_DATE ()-> 2016-01-16-> 2016-01-16

Get the current time of the system

SELECT CURTIME () SELECT CURRENT_TIME ()-> 17move44move22-> 17:44:22

23. NOW (), SYSDATE (), CURRENT_TIMESTAMP (), LOCALTIME (): get the current date and time of the system

SELECT NOW () SELECT SYSDATE () SELECT CURRENT_TIMESTAMP () SELECT CURRENT_TIMESTAMP SELECT LOCALTIME () SELECT LOCALTIME- > 2016-01-16 17 purl 441-> 2016-01-16 17 purl 441-> 2016-01-16 17 VLV 44 41-> 2016-01-16 17 VLV 44 41-> 2016-01-16 17RV 44 purl 41-> 2016-01-16 17:44:41

24. UNIX_TIMESTAMP (date): get the timestamp

SELECT UNIX_TIMESTAMP () SELECT UNIX_TIMESTAMP ('2016-01-16') SELECT UNIX_TIMESTAMP ('2016-01-16 23 SELECT UNIX_TIMESTAMP 59')-> 1452937627-> 1452873600-> 1452959999

25. FROM_UNIXTIME (unix_timestamp,format): converts a timestamp to a date time

SELECT FROM_UNIXTIME (1452959999) SELECT FROM_UNIXTIME (1452959999) 99% Ymuri% mmurf% d% HRV% iRV% s')-> 2016-01-16 23 59-> 2016-01-16 23:59:59

26. SEC_TO_TIME (seconds): convert seconds into time

SELECT SEC_TO_TIME (2378)-> 00:39:38

27. TIME_TO_SEC (time): convert time into seconds

SELECT TIME_TO_SEC ('22 2300')-> 2378

28. ADDTIME (time,times): add times to time

SELECT ADDTIME ("2015-12-31 23:59:59",'01-01-01-01 01:01:00 ")

29. CONVERT_TZ (date,from_tz, to_tz): convert the time zone

SELECT CONVERT_TZ ('2004-01-01 12-0-0-01-12)-> 2004-01-01 22:00:00

30. STR_TO_DATE (date,format): date time that converts a string to format format

SELECT STR_TO_DATE ('2015-01-01-01,'% YMY% mmury% d')-> 2015-01-01

31. LAST_DAY (date): gets the date of the last day of the month of the date

SELECT LAST_DAY (SYSDATE ()) SELECT LAST_DAY ('2015-02-02') SELECT LAST_DAY ('2015-02-02-02 SELECT LAST_DAY 22-33')-> 2016-01-31-> 2015-02-28-> 2015-02-28

32. MAKEDATE (year, dayofyear): get the date according to the parameter (year, number of days)

SELECT MAKEDATE (2015, 32)-> 2015-02-01

33. MAKETIME (hour, minute, second): get the time according to the parameters (hours, minutes, seconds)

SELECT MAKETIME (12, 23, 34)-> 12:23:34

34. YEARWEEK (date): get the year and week of the date

SELECT YEARWEEK (SYSDATE ()) SELECT YEARWEEK ('2015-01-10') SELECT YEARWEEK ('2015-01-10)-> 201602-> 201501-> 201502

35. WEEKOFYEAR (date): in what week of the year is the day of acquisition?

SELECT WEEKOFYEAR (SYSDATE ()) SELECT WEEKOFYEAR ('2015-01-10')-> 2-> 2

-> 2

-> 2

Several time format conversion functions commonly used in mysql are organized as follows

1Fromstarting unixtime (timestamp, format):

Timestamp is int time. For example, 14290450779 format is the converted format, which includes the following format:

% M month name (January... December)

% W week name (Sunday... Saturday)

% D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )

% Y year, number, 4 digits

% y year, number, 2 digits

% an abbreviated name of the week (Sun... Sat)

Number of days in% d month, number (00. 31)

Number of days in% e month, number (0... 31)

% m month, number (01... 12)

% c month, number (1 …... 12)

% b abbreviated month name (Jan... Dec)

% j days of the year (001... 366)

% H hours (00. 23)

% k hours (0. 23)

% h hours (01 …... 12)

% I hours (01 …... 12)

% l hours (1 …... 12)

% I minutes, number (00. 59)

% r time, 12 hours (hh:mm:ss [AP] M)

% T time, 24 hours (hh:mm:ss)

% s seconds (00. 59)

% s seconds (00. 59)

% p AM or PM

% w days in a week (0=Sunday... 6=Saturday)

% U week (0 …... Here Sunday is the first day of the week

% u weeks (0. 52), here Monday is the first day of the week

2 unix recording timestamp (date):

The effect is just the opposite of from_unixtime (), which converts unix timestamps into readable times, while unix_timestamp () converts readable times into unix timestamps, which are used when sorting datetime stored times. For example, unix_timestamp ('2009-08-06 10 10 40'), you get 1249524739.

If unix_timestamp () does not pass an argument, the now () function is called to fetch the current time automatically.

3 date datekeeper format (date, format):

Date_format () converts date or datetime type values to any time format. For example, in a common application scenario, a table has a field whose update time is updated and stores the datetime type, but the foreground only needs to display the xxxx-xx-xx. At this time, it can be processed with date_format (date,'%Y-%m-%d') instead of using a program loop in the result set.

The above is all the contents of the article "what are the MySQL date functions?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.