This article is participating in the “Java Theme Month – Java Brush questions punch card”, see the activity link for details

Front-end bosses asked me check backend small white last 10 minutes of data, at first I thought is directly between the and or < > is ok, but may be want to recent 10 minutes, then the number is not so good, writing is clear, only this kind of operation, the current time to 10 minutes for some operation also forgot the mysql, So find an article, and their SQL are executed over, understand the new SQL query. I hope this article has helped you with your knowledge of Mysql dates

inductive

  1. To query data within a time range, you can use between and or <> to specify a time range
  2. The time fields in mysql are dateTime, timestamp, data, time, year

  1. Function to get the current system time:
select CURDATE();
select NOW();
Copy the code

​

  1. The function that gets the time difference: period
# Calculation month: where P1 and P2 are in YYMM or YYYYMM period_diff(P1, p2) format # Calculation days: d1 and d2 are in YY format-MM-Dd or YYYY-MM-Dd or YY/MM/Dd or YYYY/MM/Dd datediff(d1, d2) # where t1 and t2 are in yyyy format-MM-Dd HH:mm:ss or HH:mm:ss is returnednull# Note: Because TIMEDIFF returnsTIMEValue, so its result is limited to from- 838.:59:59to838:59:59theTIMEValue range timediff(T1, T2)Copy the code

Convert the TIME value returned by TIMEDIFF to the day, hour format

  1. Date addition and subtraction functions
# dateThe parameter is a valid date expression expr parameter is the time interval you want to add. The # type argument can have the following values: # MICROSECONDSECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR 
# SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND 
# HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND 
# DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR
# YEAR_MONTH
DATE_SUB(date.INTERVALExpr type = DATE_SUB DATE_ADD(date.INTERVALExpr type = DATE_ADDSELECT ADDDATE('the 2021-05-20 13:14:52'.INTERVAL 1 SECOND); ADDDATE() # See test case ADDTIME() # increment N months to period P (format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note: the period parameter P is not the date value period_add(P, N)Copy the code

DATE_SUB test

SELECTDATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL 2 YEAR) AS` date_sub_year ` DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL 2 QUARTER) AS` date_sub_quarter `, #6Months DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL 2 MONTH) AS` date_sub_month ` DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL 2 WEEK) AS` date_sub_week ` DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL '2:2' DAY_SECOND) AS` date_sub_day ` DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL 2 HOUR_SECOND) AS` date_sub_hour ` DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL 2 MINUTE) AS` date_sub_minute ` DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL 2 SECOND) AS` date_sub_second ` DATE_SUB (" the 2021-05-20 13:14:52. 099 ",INTERVAL '1' SECOND_MICROSECOND) AS `date_sub_microsecond`;
Copy the code

SELECT DATE_SUB('the 2021-05-20 13:14:52'.INTERVAL '1' MINUTE_SECOND) AS'date_sub_minute_second', # subtract one minute from DATE_SUB('the 2021-05-20 13:14:52'.INTERVAL ':' HOUR_SECOND) AS`date_sub_hour_second`; Minus one hour, one minute, one secondCopy the code

ADDTIME testPERIOD_ADD(P, N) test

  1. Time format conversion function:
DATE_FORMAT() DATE_FORMAT(dateStr_to_date (STR,format) # from_unixtime() from_unixtime(format)Copy the code

Testing the DATE_FORMAT functionThe formats available are: