MySQL Date time common function summary

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

Third, get the current date function

Curdate ()

Returns the Today date value in ' Yyyy-mm-dd ' or YYYYMMDD format, depending on whether the function is used in a string or a numeric context.

The code is as follows	Copy Code
Mysql> select Curdate (); +------------+ | Curdate () | +------------+ | 2001-02-20 | +------------+

Current_date ()

Iv. getting the current time

Curtime ()

Curtime () returns the current time

Returns the current time value in ' HH:MM:SS ' or HHMMSS format, depending on whether the function is used in a string or in the context of a number.

The code is as follows	Copy Code
Mysql> select Curtime (); +-----------+ | Curtime () | +-----------+ | 10:42:38 | +-----------+

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

Let's separate some of the month and day functions

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) <= 30;

DayOfWeek (date)

Returns the week index of date (1= Sunday, 2 = Monday, ...). 7= Saturday). These index values correspond to ODBC standards.

The code is as follows	Copy Code
Mysql> Select DayOfWeek (' 1998-02-03 '/res/images/smilies/wink.gif; -> 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 '/res/images/smilies/wink.gif; -> 5 Mysql> Select Weekday (' 1997-11-05 '/res/images/smilies/wink.gif; -> 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 '/res/images/smilies/wink.gif; -> 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 '/res/images/smilies/wink.gif; -> 34 MONTH (date)

Returns the month of date, ranging from 1 to 12.

code is as follows	copy code
mysql> Select MONTH (' 1998-02-03 '/res/images/smilies/wink.gif; -> 2