Time function

Function to get some information in date and time data

• To get the value of hours , We need to use EXTRACT() function .EXTRACT（type FROM date） Express From date time data “date” Mid extraction “type” The designated part
  
• The query process is like this ：

1. Extract hourly information from trading time ：EXTRACT(HOUR FROM b.transdate); 2. Grouped by hourly information of transactions ;
2. Count the sum of sales quantity and sales amount by group ;4. Sort by hourly information of transactions .

• HOUR（time） Indicates from date time “time” in , Get some information about hours .

• YEAR（date）： obtain date In the middle of the year .
• MONTH（date）： obtain date Month in .
• DAY（date）： obtain date Chinese day .
• HOUR（date）： obtain date The hours in the game .
• MINUTE（date）： obtain date Points in .
• SECOND（date）： obtain date Seconds in

Function for calculating date and time

• DATE_ADD（date, INTERVAL expression type）： Indicates that the calculation starts at a point in time “date” Start , forward Or a backward interval of time .“ expression ” The value of is the number of time intervals , A positive number means backward , Negative table Show forward ,“type” Unit of time interval （ Such as in 、 month 、 Day, etc. ）.
• LAST_DAY（date）： Indicates the acquisition date and time “date” The date of the last day of the month .
  Calculate the first day of a date And the last day ：

select date_add(last_day(date_add(date_add('2020-12-10', interval - 1 year), interval - 1 month)),interval + 1 day);

select last_day('2020-12-10'); --  Calculate the last day 

Other date functions

• CURDATE（）： Get the current date . The date format is “YYYY-MM-DD”, That is, the grid of year, month and day type .
• DAYOFWEEK（date）： Get date “date” What day is it .1 Means Sunday ,2 It means Monday , In this way PUSH , until 7 It means Saturday .
• 
• CASE function , We can use DAYOFWEEK() The value returned by the function processes each return value , So as to match the fields in the promotion information table weekday Corresponding . Review and supplement
• DATE_FORMAT(), It means the date time “date” Display in the specified format
  
• DATEDIFF（date1,date2) Indicates the date “date1” And Japan period “date2” It's a few days away
  
  Pay attention to the problem of time calibration
• The first way is , You can use Windows The way of network synchronization provided by the system , To calibrate the system time .
• Another way is , Stores are unified from the headquarters MySQL Server get time . Due to the configuration and The operation and maintenance status is generally better than that of stores , Therefore, the possibility of system time error is also small . If you use cloud servers , The reliability of system time will be higher .

Mathematical functions

• Rounding up CEIL(X) and CEILING(X)： Return greater than or equal to X Minimum INT Type integer .
• Rounding down FLOOR(X)： Return less than or equal to X Maximum INT Type integer .
• Rounding function ROUND(X,D)：X Represents the number of to process ,D Represents the number of decimal places reserved , The way to deal with it is to round .ROUND(X) Express reservation 0 Decimal place .
• Small pit ROUND（X） If X It's a negative number Then it will be rounded in the small direction .
  

• MySQL It also supports absolute value functions ABS（） Sum and complement function MOD（）
• ABS（X） Said to get X The absolute value of ;MOD（X,Y） Said to get X By Y The remainder after division .

String function

• CONCAT（s1,s2,...）： Represents a string s1、s2…… Splice up , Make a string .
• CAST（ expression AS CHAR）： Represents converting the value of an expression into a string .
• CHAR_LENGTH（ character string ）： Indicates the length of the obtained string .
• SPACE（n）： Means to get a by n String of spaces .
• Calculate the length of the string , Here we need to use CHAR_LENGTH（）
  

concat() Use

Conditional judgment function

• Determine whether you need to add a space
• IFNULL（V1,V2）： Said if V1 The value of is not null , Then return to V1, Otherwise return to V2.
• IF（ expression ,V1,V2）： If the expression is true （TRUE）, Then return to V1, Otherwise return to V2.
• IFNULL（V1,V2） : Determine whether it is null