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

This article mainly introduces the date-related functions commonly used in MySQL, which have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Catalogue

Part one: time difference function timestampdiff, datediff, timediff

1. Time difference function: timestampdiff

Second, time difference function: datediff

3. Time difference function: timediff

4. Other date functions

Other date functions

Part II: date, time stamp and string are converted to each other.

Other

Part one: time difference function timestampdiff, datediff, timediff

1. Time difference function: timestampdiff

Syntax: timestampdiff (interval, datetime1,datetime2)

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

Interval

Frac_second milliseconds (not supported in lower versions, use second, divided by 1000)

Second second

Minute minutes

Hour hour

Day days

Week week

Month month

Quarter quarter

Year year

Note: millisecond recording and calculation is not supported until after MySQL 5.6. if it is a previous version, it is best to create an int field to store milliseconds in a field other than the datetime type in the database, and then do the conversion calculation yourself. # all formats

SELECT TIMESTAMPDIFF (FRAC_SECOND,'2012-10-01); # does not support SELECT TIMESTAMPDIFF (SECOND,'2012-10-01); # 8985600SELECT TIMESTAMPDIFF (MINUTE,'2012-10-01); # 149760SELECT TIMESTAMPDIFF (HOUR,'2012-10-01); # 2496SELECT TIMESTAMPDIFF (DAY,'2012-10-01) # 104SELECT TIMESTAMPDIFF (WEEK,'2012-10-01); # 14SELECT TIMESTAMPDIFF (MONTH,'2012-10-01); # 3SELECT TIMESTAMPDIFF (QUARTER,'2012-10-01); # 1SELECT TIMESTAMPDIFF (YEAR,'2012-10-01); # 0

Second, time difference function: datediff

Syntax: SELECT DATEDIFF (Date1, Date2)

Result: the difference in the number of days from date 1 to date 2

SLECT DATEDIFF ('2013-01-13);

3. Time difference function: timediff

Syntax: timediff (time1,time2)

Result: returns the difference of time1-time2

SELECT TIMEDIFF ('2018-05-21 14-14-51-15-43-12-12-12-14-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 and second of the current time.

The curdate () function returns the information of year, month and day.

The curtime () function returns the hour, minute and second information of the current time.

For a date that contains a year, month, day, hour, minute and second, you can use the DATE (time) function to format it into a year, month, day, date.

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 that now () is obtained at the beginning of execution, while sysdate () can be obtained dynamically when the function is executed.

Part II: date, time stamp and string are converted to each other.

# the time-date conversion string is equivalent to the to_char function select date_format (now (),'% Ymai% mmi% d') in Oracle; # result: 2017-02-27# time conversion timestamp select unix_timestamp (now ()); # result: 1488160428# string conversion time select str_to_date ('2017-02-27mm,'% Y-%m-%d% H') # result: 2017-02-27 00:00:00select str_to_date ('2017-10-16 1515 select unix_timestamp 30mm 00:00:00select str_to_date% Ymuri% mmi% d% HRV% iRV% s'); # result 2017-10-161515RV 30RV 28# Note year is uppercase 'YQing', hour must also be uppercase'H' (if the other is uppercase, the result is null) # string timestamp select unix_timestamp ('2017-02-27') # result: 148812480 timestamp turning time select from_unixtime (1488160428); # result: 2017-02-2709V 5353 select from_unixtime conversion timestamp to string select from_unixtime (1488160428 minus% Ymuri% m color% d% T'); # result: 2017-02-27 09:53:48 SELECT DATE_FORMAT (CURDATE (),'% Y-%m-%d% HV% iRV% s');-- get zero select curdate () of the day -- get the current date select last_day (curdate ());-- get the last day of the month. Select DATE_ADD (curdate (), interval-day (curdate ()) + 1 day);-- get the first day of this month (select date_add (curdate ()-day (curdate ()) + 1 Magi interval 1 month);-- get the first day of next month select DATEDIFF (date_add (curdate ()-day (curdate ()) + 1 Magi interval 1 month), DATE_ADD (curdate (), interval-day (curdate ()) + 1 day) from dual;-get the number of days of the current month

Other

SELECT DAY ('2021-2-17')-the day of the current month (17) SELECT MONTH ('2021-2-17')-the current month (2) Thank you for reading this article carefully. I hope the article "what are the date-related functions commonly used in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us, pay attention to the industry information channel, and more related knowledge is waiting for you to learn!

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.