MySQL date functions and date conversion and formatting functions

MySQL is a free relational database with a huge user base. This article lists the commonly used date functions and date conversion and formatting functions in MySQL.

1. DAY OF WEEK (date)

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

-> 7 (returns the day of the week of the date, 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 returned date is the serial number of the week. In the Western calendar, the week usually starts 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:00:00')

-> 16 (indicates the day of the month to be returned, 1 for the 1st, ..., 31 for the 31st)

4. DAY OF YEAR (date)

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

-> 91 (indicates the day of the year to be returned, 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 the month of the year in which the returned date is. If it is January, it returns 1, ..., and if it is December, it returns 12)

6. DAYNAME(date)

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

-> Saturday (indicates that the returned date is the full English name of the day of the week)

7. MONTHNAME(date)

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

-> January (indicates that the returned date is the English name of the month of the year)

8. QUARTER(date)

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

-> 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 number of date in a year. date(01.03) is Sunday. By default, Sunday is the first day of the week. The function returns 1 here in two ways: 1. Return 0 for the first week, 1 for the second week, .... 2. Start counting from the full week of the year, return 1 for the first week, 2 for the second week, ..., and 53 for the last week)
-> 1 (the default index of week() is 0. So the result is the same as above)
-> 0 (When index is 1, it means the first day of the week is Monday, so Monday the 4th 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 (represents the 4-digit year of the returned date)
-> 2070 
-> 1969

Note that if the year is only two digits long, the automatic completion mechanism uses the default date 1970.01.01 as the boundary, and will complete 19 for years >= 70 and 20 for years < 70.

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, value range (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, value range (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, value range (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 adding or subtracting month. The format of month is yyMM or yyyyMM. The result returned is 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 result of date operations

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 in date format is returned. If the parameter date is in datetime format, the result in datetime format is returned.

2. Type format:
SECOND seconds
MINUTE minutesMINUTES
HOUR TIME HOURS
DAY DAYS
MONTH MONTHS
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 functions, you can also consider using operators "+" and "-". The examples are 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

Returns:

-> 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 DATE_ADD() and ADDDATE(), one is addition and the other is subtraction. Refer to 16 o'clock when using. For specific usage, please refer to 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 0 AD to date date

19. FROM_DAYS(date)

SELECT FROM_DAYS(367)

-> 0001-01-02

Returns the DATE value of the number of days from the year 0 AD to the present

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

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

The formats of format are listed below:

%M Month name (January...December)
%W day of the week (Sunday...Saturday)
%D Day of the month with English prefix (1st, 2nd, 3rd, etc.)
%Y Year, number, 4 digits
%y year, number, 2 digits
%a Abbreviated day of the week (Sun...Sat)
%d Day of the month, number (00...31)
%e Day of the month, number (0...31)
%m Month, number (01...12)
%c Month, number (1...12)
%b abbreviated month name (Jan...Dec)
%j Day of the year (001...366)
%H Hour (00...23)
%k hour (0...23)
%h hour (01...12)
%I Hour (01...12)
%l hours (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 Day of the week (0=Sunday...6=Saturday)
%U Week (0...52), where Sunday is the first day of the week
%u Week (0...52), where Monday is the first day of the week
%% character % )

TIME_FORMAT(time,format):
The specific usage is similar to DATE_FORMAT(), but TIME_FORMAT only processes hours, minutes, and seconds (the remaining symbols produce a NULL value or 0)

21. Get the current system date

SELECT CURDATE() 
SELECT CURRENT_DATE()

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

22. Get 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): Get 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): Convert timestamp to date and time

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): Convert seconds to time

SELECT SEC_TO_TIME(2378)

-> 00:39:38

27. TIME_TO_SEC(time): Convert 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 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 a string to a date and time in format

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

-> 2015-01-01

31. LAST_DAY(date): Get the last day of the 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): Get the date based on the parameters (year, day)

SELECT MAKEDATE(2015 ,32)

-> 2015-02-01

33. MAKETIME(hour, minute, second): Get the time according to the parameters (hour, minute, second)

SELECT MAKETIME(12 ,23 ,34 )

-> 12:23:34

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

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

-> 201602
-> 201501
-> 201502

35. WEEKOFYEAR(date): Get the week number of the year in which the current day is located

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

-> 2
-> 2

-> 2
-> 2

Several commonly used time format conversion functions in MySQL are summarized as follows

1. from_unixtime(timestamp, format):

timestamp is an int time, such as 14290450779; format is the conversion format, including the following formats:

%M Month name (January...December)
%W day of the week (Sunday...Saturday)
%D Day of the month with English prefix (1st, 2nd, 3rd, etc.)
%Y Year, number, 4 digits
%y year, number, 2 digits
%a Abbreviated day of the week (Sun...Sat)
%d Day of the month, number (00...31)
%e Day of the month, number (0...31)
%m Month, number (01...12)
%c Month, number (1...12)
%b abbreviated month name (Jan...Dec)
%j Day of the year (001...366)
%H Hour (00...23)
%k hour (0...23)
%h hour (01...12)
%I Hour (01...12)
%l hours (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 Day of the week (0=Sunday...6=Saturday)
%U Week (0...52), where Sunday is the first day of the week
%u Week (0...52), where Monday is the first day of the week

2. unix_timestamp(date):

The function is just the opposite of from_unixtime(). The former converts the Unix timestamp into a readable time, while unix_timestamp() converts the readable time into a Unix timestamp, which is used when sorting the time stored in datetime. For example, unix_timestamp('2009-08-06 10:10:40') yields 1249524739.

If unix_timestamp() is called without passing any parameters, the now() function will be called to automatically get the current time.

3. date_format(date, format):

date_format() converts a date or datetime type value to an arbitrary time format. For example, in a common application scenario, a table has a field for update time, which is stored in the datetime type. However, when displayed on the front end, only the year, month, and day (xxxx-xx-xx) need to be displayed. In this case, date_format(date,'%Y-%m-%d ') can be used without the need for a program loop in the result set.