Free learning recommendation: mysql video tutorial
##Table of contents
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.
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 formatsSELECT 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 2SLECT 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.
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:43Note: 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!