Let's talk about commonly used date-related functions in MySQL

Free learning recommendation: mysql video tutorial

##Table of contents

Part 1: Time difference functions timestampdiff, datediff, timediff
• ##1. Time difference function: timestampdiff
  • 2 , Time difference function: datediff
  • 3. Time difference function: timediff
  • 4. Other date functions
  • Other date functions
  Two parts of date, timestamp, and string conversion
• Others
##Part 1: Time difference functions timestampdiff, datediff , timediff

1. Time difference function: timestampdiff

Syntax: timestampdiff(interval, datetime1,datetime2)

Result: Returns the time difference of (time 2 - time 1), the result unit is given by the interval parameter.

interval

frac_second milliseconds (lower versions do not support it, use second, and then divide by 1000) second second minute minute
hour hour
day day
week week
month month
quarter quarter
year year


Note: Millisecond recording and calculation are only supported after MySQL 5.6. If it is before For the version, it is best to create an int field for storing milliseconds in the fields other than the datetime type in the database, and then perform the conversion calculation yourself. # All formats

SELECT TIMESTAMPDIFF(FRAC_SECOND,'2012-10-01','2013-01-13'); # 暂不支持SELECT TIMESTAMPDIFF(SECOND,'2012-10-01','2013-01-13'); # 8985600SELECT TIMESTAMPDIFF(MINUTE,'2012-10-01','2013-01-13'); # 149760SELECT TIMESTAMPDIFF(HOUR,'2012-10-01','2013-01-13'); # 2496SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13'); # 104SELECT TIMESTAMPDIFF(WEEK,'2012-10-01','2013-01-13'); # 14SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); # 3SELECT TIMESTAMPDIFF(QUARTER,'2012-10-01','2013-01-13'); # 1SELECT TIMESTAMPDIFF(YEAR,'2012-10-01','2013-01-13'); # 0

2. Time difference function: datediff

Syntax: SELECT DATEDIFF(date 1, date 2)

Result: The difference in days between date 1 - date 2

SLECT DATEDIFF('2013-01-13','2012-10-01'); # 104

3. Time difference function: timediff

Syntax: timediff(time1,time2)

Result: Return the difference between time1-time2

SELECT TIMEDIFF('2018-05-21 14:51:43','2018-05-19 12:54:43');#49:57:00

Note: The two parameters of this method must be in date format

4. Other date functions

The now() function returns the year, month, day, hour, minute and second of the current time.

The curdate() function returns the year, month, day information.
• curtime( ) function returns the hour, minute, and second information of the current time.
• To format a date containing the year, month, day, hour, minute, and second into a month, day, and date, you can use the DATE(time) function.
• Other date functions

SELECT NOW(); # 2018-05-21 14:41:00SELECT CURDATE(); # 2018-05-21SELECT CURTIME(); # 14:41:38SELECT DATE(NOW()); # 2018-05-21SELECT SYSDATE(); # 2018-05-21 14:47:11SELECT CURRENT_TIME(); # 14:51:30SELECT CURRENT_TIMESTAMP; # 2018-05-21 14:51:37SELECT CURRENT_TIMESTAMP(); # 2018-05-21 14:51:43

Note: now() is similar to sysdate(), except now() is obtained at the beginning of execution, while sysdate() can be obtained when the function is executed Dynamically obtained.

Part 2 Date, timestamp, string conversion

#时间日期转字符串  相当与Oracle中的to_char函数select date_format(now(), '%Y-%m-%d'); #结果：2017-02-27#时间转时间戳select unix_timestamp(now()); #结果：1488160428#字符串转时间select str_to_date('2017-02-27', '%Y-%m-%d %H'); #结果：2017-02-27 00:00:00select str_to_date('2017-10-16 15:30:28','%Y-%m-%d %H:%i:%s'); #结果 2017-10-16 15:30:28#注意 年是大写‘Y’，小时也必须是大写‘H’    (如果其他为大写，则得到结果为null)#字符串转时间戳select unix_timestamp('2017-02-27'); #结果：1488124800#时间戳转时间select from_unixtime(1488160428); #结果：2017-02-27 09:53:48#时间戳转字符串select from_unixtime(1488160428,'%Y-%m-%d %T'); #结果：2017-02-27 09:53:48

 SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'); -- 获取当天零点select curdate();                       --获取当前日期select last_day(curdate());                    --获取当月最后一天。select DATE_ADD(curdate(),interval -day(curdate())+1 day);   --获取本月第一天select date_add(curdate()-day(curdate())+1,interval 1 month); -- 获取下个月的第一天select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual; --获取当前月的天数

Others

SELECT DAY('2021-2-17') -- 当前月的第几天(17)SELECT MONTH('2021-2-17') -- 当前为几月(2)

More related free learning recommendations:

mysql tutorial(Video)

The above is the detailed content of Let's talk about commonly used date-related functions in MySQL. For more information, please follow other related articles on the PHP Chinese website!