MySQL basic 03 (date function)

The processing of dates in the work is often encountered, the requirements may be varied, so the focus is introduced.

1. Get the current date

Select Now () -- results: 2015-10-28 22:41:11 Select Now (), SLEEP (3), now ()--  results 2015-10-28 22:43:34  0  2015-10-28 22:43:34 SELECT Sysdate (), SLEEP (3), sysdate ()--  results 2015-10-28 22:46:48  0  2015-10-28 22:46:52-  Now () is the result of SQL execution, and sysdate () is the function that is executed, and generally it is enough, so more often

2. Date formatting

Date_format (Date,format)   Note: the function returns a string type
Formats the DATE value from the format string
(available in the format string:
%M month name (January ... December)  
%W week name (Sunday ... Saturday)  
%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). )  
%Y, number, 4-bit  
%Y year, number, 2-bit  
%a abbreviated weekday name (Sun ... Sat)   number of days in the
%d month, number (00 ...  
number of days in%e month, number (0 ...  
%m Month, number (01 ...  
%c Month, number (1 ...  
%b abbreviated month name (Jan ... DEC)  
%j number of days in the year (001 ... 366)  
%H hours (00 ... )  
%k hours (0 ... )  
%h hours (01 ...  
%I Hours (01 ... &NBSP,
%l hours (1 ...  
%i minutes, numbers (00 ...  
%r time, 12 hours (Hh:mm:ss [ap]m)  
%T time, 24 hours (HH:MM:SS)  
%s seconds (00 ...).  
%s seconds (00 ...).  
%p am or pm 
%w days in one weeks (0=sunday ... 6=saturday)  
%u week (0 ...) 52), here Sunday is the first day of the week  
%u week (0 ... 52), here Monday is the first day of the week  
percent of characters%)

 --  According to the above parameters, we can get a variety of date formats, Much more powerful than the convert function of MSSQL, the following is a common standard date format  select  Date_format (now (),  '  ) --  2015-10-28  select  Date_ FORMAT (now (),  %y-%m-%d   ' ) --  15-10-28  select  Date_format (now (),  " %y-%m-%d%H :%m:%s  ) --  2015-10-28 22:10:56  

3. Date interception

--gets the date on the day of the week (1= Sunday, 2 = Monday .... 7= Saturday)SELECTDAYOFWEEK (now ())--Gets the date the daySELECTDayOfMonth ('2015-10-28')--Results--The day of the year that gets the dateSELECTDayOfYear ('2015-10-28')--result 301--gets the year of the dateSELECT  Year('2015-10-28')--Results--get the first quarter of a dateSELECTQUARTER ('2015-10-28')--Results 4--gets the month of the dateSELECT MONTH('2015-10-28')--Results Ten--get date is the week ordinalSELECTWEEK ('2015-10-28')--result 43 (default Sunday is the first day of the week)SELECTWEEK ('2015-10-28',0)--Result 43 (Sunday is the first day of the week)SELECTWEEK ('2015-10-28',1)--Result 44 (Monday is the first day of the week)--get the hour of the daySELECTHOUR ('2015-10-28 23:11:56')--Results--gets the minute of the dateSELECTMINUTE ('2015-10-28 23:11:56')--Results--gets the second of the dateSELECTSECOND ('2015-10-28 23:11:56')--Results 56
--Get Date part
SELECT DATE (' 2015-10-28 23:11:56 ')--result ' 2015-10-28 '
--Get the time section
SELECT time (' 2015-10-28 23:11:56 ')--Results '23:11:56'

4. Date arithmetic

Date_add (Date,interval expr type)

[Type value meaning expected expr format]:
SECOND sec SECONDS
MINUTE min MINUTES
HOUR Time HOURS
Day days
Month MONTHS
Year years
Minute_second minutes and seconds "Minutes:seconds"
Hour_minute hours and minutes "hours:minutes"
Day_hour Day and Hour "days HOURS"
Year_month year and month "Years-months"
Hour_second hours, minutes, "HOURS:MINUTES:SECONDS"
Day_minute day, hour, minute "Days Hours:minutes"
Day_second day, hour, minute, second "days HOURS:MINUTES:SECONDS"
Any punctuation in expr is allowed to be delimited, and if all is a date value the result is a date value, otherwise the result is a datetime value)
If the type keyword is incomplete, then MySQL takes the value from the right end, day_second because the missing hour minute equals Minute_second)
If you increase month, year_month, or year, the maximum number of days is the maximum number of days greater than the result month)

--indicates that the plus sign is addition, minus is subtraction--Added 4 daysSELECTDate_add ('2015-10-28 23:11:56', INTERVAL4  Day)--2015-11-01 23:11:56--3 days ReducedSELECTDate_add ('2015-10-28 23:11:56', INTERVAL-3  Day)--2015-10-25 23:11:56--increased by 1 yearsSELECTDate_add ('2015-10-28 23:11:56', INTERVAL1  Year)--  .-Ten- -  at: One: About--added 1 days 3 hoursSELECTDate_add ('2015-10-28 23:11:56', INTERVAL'1 3'Day_hour)--2015-10-27 20:11:56--minus 1 days, 1 hours, 1 minutes, 1 seconds .SELECTDate_add ('2015-10-28 23:11:56', INTERVAL'-1 1:1:1'Day_second)--2015-10-27 22:10:55

5. Date Comparison

--calculate the number of days for 2 date intervalsSELECT DATEDIFF('2015-10-28','2015-10-26')--Results 2--calculates the time division seconds of 2 date intervalsSELECTTimediff ('2015-10-28 23:11:56','2015-10-28 20:11:56')--result 03:00:00

6. Date Conversion

--Convert dates to days (00 to present)SELECTTo_days ('2015-10-28 23:11:56')--result 736264SELECTTo_days ('0000-00-00 00:00:00')--Results 0--Convert date to seconds (number of seconds from ' 1970-01-01 00:00:00 ' GMT)SelectUnix_timestamp ('2015-10-28 23:11:56')--result 1446045116--Convert time to secondsSELECTTime_to_sec ('01:00:05');--result 3605--Converts a number of seconds to a dateSELECTSec_to_time (3605);--' 01:00:05 '

Above, reference: http://www.cnblogs.com/zeroone/archive/2010/05/05/1727659.html

Attention issues

1. Date_format

 --  error example   *  from  mytable where  create_date>=  Date_format (now (),   %y-%m-%d    )   description: Date_format returns a string, so it can't be compared directly to a date field, which I've already encountered in my work.  --  workaround use Date to return the date type  select  *  Span style= "color: #0000ff;" >from  mytable >=  DATE (now ()); 

function implementation

--get the beginning of the month and the end of the month by dateSET @DATE_B:=date (Date_format ('2015-10-28','%y-%m-1'));SET @DATE_E:=Date_add (Date_add (@DATE_B, INTERVAL1 MONTH), INTERVAL-1  Day);SELECT @DATE_B,@DATE_E

MySQL basic 03 (date function)