One, time lag
DateDiff: To put it bluntly is to use the first time to reduce the second time, the order can not forget
The code is as follows |
Copy Code |
Select DateDiff (' 2012-08-08 ', ' 2012-08-13 '); ---5 |
Second, get the current time
The code is as follows |
Copy Code |
Now Current_timestamp () , Current_timestamp , LocalTime () , localtime , Localtimestamp--(v4.0.6) , Localtimestamp ()--(v4.0.6)
|
Sysdate (); Dynamic acquisition of system time. This is to distinguish it from the now function, which is like having defined a variable, sysdate equals variable is determined after running to, so they have a time difference
Dynamic acquisition of system time. This is to distinguish it from the now function, which is like having defined a variable, sysdate equals variable is determined after running to, so they have a time difference
Third, get the current date function
Curdate () current_date (), current_date
Iv. getting the current time
The code is as follows |
Copy Code |
Curtime () Current_time (), Current_time |
Get the current UTC date-time function
The code is as follows |
Copy Code |
Select Utc_timestamp (), Utc_date (), Utc_time (), now () |
Six, MySQL date time function
The code is as follows |
Copy Code |
Set @dt = ' 2008-09-10 07:15:30.123456 ';
Select Date (@dt); --2008-09-10 Select time (@dt); --07:15:30.123456 Select year (@dt); --2008 Select quarter (@dt); --3 Select Month (@dt); --9 Select Week (@dt); --36 Select Day (@dt); --10 Select Hour (@dt); --7 Select minute (@dt); --15 Select second (@dt); --30 Select Microsecond (@dt); --123456 |
The following query selects all records and its date_col value is within the last 30 days:
The code is as follows |
Copy Code |
Mysql> SELECT something from table WHERE To_days (now ())-To_days (date_col) Select DayOfWeek (' 1998-02-03 '); -> 3 |
Weekday (date)
Returns the week index of date (0= Monday, 1 = Tuesday, ...). 6= Sunday).
The code is as follows |
Copy Code |
Mysql> Select Weekday (' 1997-10-04 22:23:00 '); -> 5 Mysql> Select Weekday (' 1997-11-05 '); -> 2 |
DayOfMonth (date)
Returns the date in the month of date, in the range 1 through 31.
The code is as follows |
Copy Code |
Mysql> Select DayOfMonth (' 1998-02-03 '); -> 3 |
DayOfYear (date)
Returns the number of days in a year, in the range of 1 to 366.
The code is as follows |
Copy Code |
Mysql> Select DayOfYear (' 1998-02-03 '); -> 34 |
MONTH (date)
Returns the month of date, ranging from 1 to 12.
The code is as follows |
Copy Code |
Mysql> Select MONTH (' 1998-02-03 '); -> 2 |
Dayname (date)
Returns the name of the week of date.
The code is as follows |
Copy Code |
Mysql> Select Dayname ("1998-02-05"); -> ' Thursday ' |
MonthName (date)
Returns the month name of the date.
The code is as follows |
Copy Code |
Mysql> Select MonthName ("1998-02-05"); -> ' February ' |
Quarter (date)
Returns the quarter of date one year, ranging from 1 to 4.
The code is as follows |
Copy Code |
Mysql> Select quarter (' 98-04-01 '); -> 2 WEEK (date) WEEK (Date,first) |
For Sunday is the first day of the week, there is a single parameter that returns the week number of date, ranging from 0 to 52. 2 Parameter Form week () allow
Do you specify whether the week starts in Sunday or Monday. If the second argument is 0, the week begins in Sunday, and if the second argument is 1,
Starting from Monday.
The code is as follows |
Copy Code |
Mysql> Select WEEK (' 1998-02-20 '); -> 7 Mysql> Select WEEK (' 1998-02-20 ', 0); -> 7 Mysql> Select WEEK (' 1998-02-20 ', 1); -> 8 |
Year (date)
Returns the year of date, ranging from 1000 to 9999.
The code is as follows |
Copy Code |
Mysql> Select year (' 98-02-03 '); -> 1998 |
HOUR (Time)
Returns the hour of time, ranging from 0 to 23.
The code is as follows |
Copy Code |
Mysql> Select HOUR (' 10:05:03 '); -> 10 |
MINUTE (Time)
Returns the minutes of time, ranging from 0 to 59.
The code is as follows |
Copy Code |
Mysql> Select MINUTE (' 98-02-03 10:05:03 '); -> 5 |
SECOND (Time)
The number of seconds to return time, ranging from 0 to 59.
The code is as follows |
Copy Code |
Mysql> Select SECOND (' 10:05:03 '); -> 3 |
Period_add (P,n)
Increase n months to phase p (in format Yymm or yyyymm). Returns a value in YYYYMM format. Note that the phase parameter p is not a date value.
The code is as follows |
Copy Code |
Mysql> Select Period_add (9801,2); -> 199803 |
Period_diff (P1,P2)
Returns the number of months between periods P1 and P2, P1 and P2 should be in a format yymm or yyyymm. Note that the time parameter P1 and P2 are not date values.
code is as follows |
copy code |
mysql> Select Period_diff (9802,199703); -> Date_add (Date,interval expr type) date_sub (date,interval expr type) Adddate (date,interval expr Type) |
Subdate (Date,interval expr type)
These functions perform date operations. For MySQL 3.22, they are new. Adddate () and subdate () are synonyms for Date_add () and Date_sub ().
In MySQL 3.23, you can use + and-instead of Date_add () and Date_sub (). (see example) date is a specified start date
datetime or Date value, expr is an expression that specifies an interval value that is added to the start date or subtracted from the start date, and expr is a string;
A "-" start represents a negative interval. Type is a keyword that indicates how an expression should be interpreted. EXTRACT (type from date) function from date
Returns the "type" interval in the The following table shows how type and expr parameters are associated: the desired expr format for type value meaning
SECOND seconds SECONDS
MINUTE minutes MINUTES
HOUR Time HOURS
Day days
MONTH Month MONTHS
Year YEARS
Minute_second minute and second "Minutes:seconds"
Hour_minute hour and Minute "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"