MySQL date functions and date conversion formatting functions, mysql Functions

MySQL date functions and date conversion formatting functions, mysql Functions

Mysql is a free relational database with a huge user base. This article lists MYSQL common date functions and date conversion formatting functions.

1. DAYOFWEEK (date)

SELECT DAYOFWEEK(‘2016-01-16') SELECT DAYOFWEEK(‘2016-01-16 00:00:00')

-> 7 (indicates, remember: Sunday = 1, Monday = 2,... Saturday = 7)

2. WEEKDAY (date)

SELECT WEEKDAY(‘2016-01-16') SELECT WEEKDAY(‘2016-01-16 00:00:00')

 

-> 5 (indicates that the return date is the sequence number in a week. In Western calendars, the first day of a week is Sunday and the count starts with 0. Therefore, remember: 0 = Monday, 1 = Tuesday ,... 5 = Saturday)

3. DAYOFMONTH (date)

SELECT DAYOFMONTH(‘2016-01-16') SELECT DAYOFMONTH(‘2016-01-16 00:00:00')

-> 16 (indicates that the return date is the day of the month, and 1,..., 31 returns 31)

4. DAYOFYEAR (date)

SELECT DAYOFYEAR(‘2016-03-31') SELECT DAYOFYEAR(‘2016-03-31 00:00:00')

-> 91 (return date is the day of the current year, 01.01 returns 1,..., 12.31 returns 365)

5. MONTH (date)

SELECT MONTH(‘2016-01-16') SELECT MONTH(‘2016-01-16 00:00:00')

-> 1 (indicates that the return date is the month of the current year, and 1,... is returned on January 1, January, and 12 is returned on January 1, December)

6. DAYNAME (date)

SELECT DAYNAME(‘2016-01-16') SELECT DAYNAME(‘2016-01-16 00:00:00')

-> Saturday (return date is the full name of the week)

7. MONTHNAME (date)

SELECT MONTHNAME(‘2016-01-16') SELECT MONTHNAME(‘2016-01-16 00:00:00')

-> January (the return date is the English name of the month of the current year)

8. QUARTER (date)

SELECT QUARTER(‘2016-01-16') SELECT QUARTER(‘2016-01-16 00:00:00')

-> 1 (returns the quarter Number of the current year, returns)

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 number of the date in the year, and date (01.03) is Sunday. The default value is Sunday as the first day of the week, the function returns 1 here. There are two possible understandings: 1. The first week returns 0, and the second week returns 1 ,...., 2. Counting starts from the complete week of the current year. 1 is returned for the first week, 2 is returned for the second week ,..., in the last week, return 53)-> 1 (week (). The default index is 0. so the result is the same as above)-> 0 (when index is 1, it indicates that the first day of a week is Monday. Therefore, Monday 4 is the start day of the second week)

10, YEAR (date)

SELECT YEAR(‘70-01-16') SELECT YEAR(‘2070-01-16') SELECT YEAR(‘69-01-16 00:00:00')

-> 1970 (indicating the 4-digit year of date)-> 2070-> 1969

Note: If the year has only two digits, the auto-completion mechanism is based on the default time 1970.01.01,> = 70 completion 19, <70 completion 20

11. HOUR (time)

SELECT HOUR(‘11:22:33') SELECT HOUR(‘2016-01-16 11:22:33')

-> 11-> 11

Returns the hour value of the date or time. The value range is (0-23)

12. MINUTE (time)

SELECT MINUTE(‘11:22:33') SELECT MINUTE(‘2016-01-16 11:44:33')

-> 22-> 44

Returns the minute value of the time. The value range is (0-59)

13. SECOND (time)

SELECT SECOND(‘11:22:33') SELECT SECOND(‘2016-01-16 11:44:22')

-> 33-> 22

Returns the minute value of the time. The value range is (0-59)

14. PERIOD_ADD (month, add)

SELECT PERIOD_ADD(1601,2) SELECT PERIOD_ADD(191602,3) SELECT PERIOD_ADD(191602,-3)

-> 201603-> 191605-> 191511

This function returns the result of increasing or decreasing the month. The format of month is yyMM or yyyyMM, and all results are returned 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 and 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 00:00:00-> 2016-01-01 23:59:59-> 2016-01-01 00:01:00-> 2015-12-30 14:00:00

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

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

2. type format:
SECOND SECONDS
MINUTE MINUTES
HOUR time HOURS
DAY DAYS
MONTH-MONTH MONTHS
YEAR YEARS
MINUTE_SECOND MINUTES and SECONDS "MINUTES: SECONDS"
HOUR_MINUTE hour and minute "HOURS: MINUTES"
DAY_HOUR and hour "days hours"
YEAR_MONTH and month "YEARS-MONTHS"
HOUR_SECOND hour, minute, "HOURS: MINUTES: SECONDS"
DAY_MINUTE day, hour, minute "days hours: MINUTES"
DAY_SECOND day, hour, minute, second "days hours: MINUTES: SECONDS"

3. If you do not need a function, you can also use the operators "+" and "-". The example is as follows:

SELECT “2016-01-01” - INTERVAL 1 SECOND SELECT “2016-01-01” - INTERVAL 1 DAY SELECT ‘2016-12-31 23:59:59' + INTERVAL 1 SECOND SELECT ‘2016-12-31 23:59:59' + INTERVAL “1:1” MINUTE_SECOND

Returned results:

-> 2015-12-31 23:59:59-> 2015-12-31-> 2017-01-01 00:00:00-> 2017-01-01 00:01:00

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

The usage is similar to that of DATE_ADD () and ADDDATE (). For addition and subtraction, refer to 16 points. For specific usage, see DATE_ADD () and ADDDATE ().

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 the date to the date of the West dollar.

19. FROM_DAYS (date)

SELECT FROM_DAYS(367)

-> 0001-01-02

Returns the DATE value of the number of days since the DATE of the West dollar 0 year.

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

SELECT DATE_FORMAT(‘2016-01-16 22:23:00','%W %M %Y') SELECT DATE_FORMAT(‘2016-01-16 22:23:00','%D %y %a %d %m %b %j') SELECT DATE_FORMAT(‘2016-01-16 22:23:00','%H %k %I %r %T %S %w') SELECT DATE_FORMAT(‘2016-01-16 22:23:00','%Y-%m-%d %H:%i:%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

Format:

% M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, 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 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% Characters %)

TIME_FORMAT (time, format ):
The specific usage is similar to DATE_FORMAT (), but TIME_FORMAT only processes hours, minutes, And seconds (other symbols generate a NULL value or 0)

21. Obtain the current system date

SELECT CURDATE() SELECT CURRENT_DATE()

-> 2016-01-16-> 2016-01-16

22. Obtain the current system time

SELECT CURTIME() SELECT CURRENT_TIME()

-> 17:44:22-> 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:44:41-> 2016-01-16 17:44:41-> 2016-01-16 17:44:41-> 2016-01-16 17:44:41-> 2016-01-16 17:44:41-> 2016-01-16 17:44:41

24. UNIX_TIMESTAMP (date): Obtain the timestamp.

SELECT UNIX_TIMESTAMP() SELECT UNIX_TIMESTAMP(‘2016-01-16') SELECT UNIX_TIMESTAMP(‘2016-01-16 23:59:59')

-> 1452937627-> 1452873600-> 1452959999

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

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

-> 2016-01-16 23:59:59-> 2016-01-16 23:59:59

26. SEC_TO_TIME (seconds): converts seconds to time.

SELECT SEC_TO_TIME(2378)

-> 00:39:38

27. TIME_TO_SEC (time): converts time to seconds.

SELECT TIME_TO_SEC(‘22:23:00')

-> 2378

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

SELECT ADDTIME(“2015-12-31 23:59:59”,'01:01:01')

-> 2016-01-01 01:01:00

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

SELECT CONVERT_TZ(‘2004-01-01 12:00:00','+00:00','+10:00')

-> 2004-01-01 22:00:00

30. STR_TO_DATE (date, format): Convert the string to the date and time in format.

SELECT STR_TO_DATE(‘2015-01-01', ‘%Y-%m-%d')

-> 2015-01-01

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

SELECT LAST_DAY(SYSDATE()) SELECT LAST_DAY(‘2015-02-02') SELECT LAST_DAY(‘2015-02-02 00:22:33')

-> 2016-01-31-> 2015-02-28-> 2015-02-28

32. MAKEDATE (year, dayofyear): Obtain the date based on the parameter (year, the number of days ).

SELECT MAKEDATE(2015 ,32)

-> 2015-02-01

33. MAKETIME (hour, minute, second): Obtain the time based on the parameter (hour, minute, second ).

SELECT MAKETIME(12 ,23 ,34 )

-> 12:23:34

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

SELECT YEARWEEK(SYSDATE()) SELECT YEARWEEK(‘2015-01-10') SELECT YEARWEEK(‘2015-01-10',1)

-> 201602-> 201501-> 201502

35. WEEKOFYEAR (date): Obtain the week number of the current year.

SELECT WEEKOFYEAR(SYSDATE()) SELECT WEEKOFYEAR(‘2015-01-10')

-> 2-> 2

-> 2
-> 2

The following are some commonly used time format conversion functions in mysql:

1, from_unixtime (timestamp, format ):

Timestamp is int-type time, such as 14290450779; format is the conversion format, including the following:

% M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, 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 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.

2, unix_timestamp (date ):

In contrast to from_unixtime (), the former converts a unix timestamp to a readable time, while the unix_timestamp () converts a readable time to a unix timestamp, this is used to sort the datetime storage time. For example, unix_timestamp ('2017-08-06 10:10:40 '), 2009 is returned.

If unix_timestamp () does not pass the parameter, the now () function is called to automatically obtain the current time.

3, date_format (date, format ):

Date_format () converts a value of the date or datetime type to any time format. For example, in common application scenarios, a field in a table stores the Update Time and datetime type, but only the year, month, and day (xxxx-xx) are displayed on the foreground ), at this time, you can use date_format (date, '% Y-% m-% D') for processing, instead of loop processing in the result set.