MySQL time and date usage summary

1 Get current date + time (date + times) 1.1 function: Now ()

Related functions: Current_timestamp (), localtime (), Localtimestamp ()

2. Get Current date

Function: Curdate ()

Related functions: Current_date (), current_date

3. Get current time

Function: Curtime ()

Related functions: Current_time (), Current_time

4. MySQL dayof ...

Function: DayOfWeek (), DayOfMonth (), DayOfYear () returns the date parameter, the position in the week, month, and year.

Give an example to explain

Set @dt = ' 2008-08-08 ';

Select DayOfWeek (@dt); --6

Select DayOfMonth (@dt); --8

Select DayOfYear (@dt); --221

Date ' 2008-08-08′ is the 6th day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

The 8th day of the January; the No. 221 Day of the year

MySQL gets the current date time function
1.1 Get Current date + time (date + times) function: Now ()

Mysql> Select Now ();

+---------------------+
| Now () |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+ In addition to the now () function to get the current date time, MySQL also has the following function:

Current_timestamp ()
, Current_timestamp
, LocalTime ()
, localtime
, Localtimestamp--(v4.0.6)
, Localtimestamp ()--(v4.0.6) These date-time functions, are equivalent to now (). Given that the now () function is short and easy to remember, it is always recommended to use now () instead of the functions listed above.

1.2 Get current date + time (date + times) function: Sysdate ()

The Sysdate () Date time function is similar to now (), except that the now () is obtained at the start value, and sysdate () gets the values dynamically when the function is executed. Take a look at the example below to see:

Mysql> Select Now (), Sleep (3), now ();

+---------------------+----------+---------------------+
| Now ()                 | Sleep (3) | Now ()                |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 |         0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+mysql> Select Sysdate (), Sleep (3), sysdate ();

+---------------------+----------+---------------------+
| Sysdate () | Sleep (3) | Sysdate () |
+---------------------+----------+---------------------+
|        2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------------+ you can see that although 3 seconds of sleep Midway, the time value of the now () function is the same for two times; Sysdate () The time value of the function is 3 seconds apart. This description of Sysdate () is described in the MySQL Manual as follows: Return to which the function executes.

Sysdate () Date time functions are rarely used in general.

2. Get the current date function: Curdate ()

Mysql> select Curdate ();

+------------+
| Curdate () |
+------------+
| 2008-08-08 |
+------------+ where the following two date functions are equivalent to Curdate ():

Current_date ()
, Current_date3. Get current time function: Curtime ()

Mysql> select Curtime ();

+-----------+
| Curtime () |
+-----------+
| 22:41:30 |
+-----------+ where the following two time functions are equivalent to Curtime ():

Current_time ()
, Current_time4. Gets the current UTC date-time function: Utc_date (), Utc_time (), Utc_timestamp ()

Mysql> Select Utc_timestamp (), Utc_date (), Utc_time (), now ()

+---------------------+------------+------------+---------------------+
| Utc_timestamp () | Utc_date () | Utc_time () | Now () |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+ because our country is located in the East eight time zone, so local time = UTC time + 8 hours. UTC time is useful when the business involves multiple countries and regions.

Online collection of reprint, convenient access (the best of course is www.mysql.cn function query)

Here is an example of using a date function. The following query selects all records and its date_col value is within the last 30 days:

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.
MySQL Select DayOfWeek (' ' 1998-02-03 ');
-3

Weekday (date)
Returns the week index of date (0= Monday, 1 = Tuesday, ...). 6= Sunday).
MySQL Select Weekday (' 1997-10-04 222300 ');
-5
MySQL Select Weekday (' ' 1997-11-05 ');
-2

DayOfMonth (date)
Returns the date in the month of date, in the range 1 through 31.
MySQL Select DayOfMonth (' ' 1998-02-03 ');
-3

DayOfYear (date)
Returns the number of days in a year, in the range of 1 to 366.
MySQL Select DayOfYear (' ' 1998-02-03 ');
-34

MONTH (date)
Returns the month of date, ranging from 1 to 12.
MySQL Select MONTH (' ' 1998-02-03 ');
-2

Dayname (date)
Returns the name of the week of date.
MySQL select Dayname (1998-02-05);
-"' Thursday '"

MonthName (date)
Returns the month name of the date.
MySQL select MonthName (1998-02-05);
-"' February '"

Quarter (date)
Returns the quarter of date one year, ranging from 1 to 4.
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.
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.
MySQL Select year ("' 98-02-03 '");
-1998

HOUR (Time)
Returns the hour of time, ranging from 0 to 23.
MySQL Select HOUR (' ' 100503 ');
-10

MINUTE (Time)
Returns the minutes of time, ranging from 0 to 59.
MySQL Select MINUTE (' ' 98-02-03 100503 ');
-5

SECOND (Time)
The number of seconds to return time, ranging from 0 to 59.
MySQL Select SECOND (' ' 100503 ');
-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.
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.
MySQL select Period_diff (9802,199703);
-11

Date_add (Date,interval expr type)

date_sub (date,interval expr type)

Adddate (date,interval expr Type)

subdate (date,interval expr type)
These features 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
datetime or date value that specifies the start date, and 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; it can represent a negative interval at the beginning of
One "-". Type is a keyword that indicates how an expression should be interpreted. The EXTRACT (type from date) function returns the "type" interval from the date
. The following table shows how the type and expr parameters are associated: the desired expr format for type value
SECOND second SECONDS
MINUTE minutes MINUTES
HOUR time HOURS
Day
MONTH Month MONTHS
year YEARS
Minute_second minutes and sec minutesseconds
Hour_minute hours and minutes hoursminutes
Day_ HOUR days and hours HOURS
year_month years and months years-months
Hour_second hours, minutes, hoursminutesseconds
Day_minute days, hours, Minutes hoursminutes
Day_second days, hours, minutes, seconds hoursminutesseconds

MySQL allows any punctuation separator in the expr format. Indicates that the suggested separator is displayed. If the date parameter is a date value and your calculation is only
Contains the year, month, and day sections (that is, there is no time part) and the result is a date value. Otherwise, the result is a datetime value.

MySQL SELECT 1997-12-31 235959 + INTERVAL 1 SECOND;
-1998-01-01 000000
MySQL SELECT INTERVAL 1 day + 1997-12-31;
-1998-01-01
MySQL SELECT 1998-01-01-interval 1 SECOND;
-1997-12-31 235959
MySQL SELECT date_add (1997-12-31 235959,
INTERVAL 1 SECOND);
-1998-01-01 000000
MySQL SELECT date_add (1997-12-31 235959,
INTERVAL 1 day);
-1998-01-01 235959
MySQL SELECT date_add (1997-12-31 235959,
INTERVAL Minute_second);
-1998-01-01 000100
MySQL SELECT date_sub (1998-01-01 000000,
INTERVAL 1 Day_second);
-1997-12-30 225859
MySQL SELECT date_add (1998-01-01 000000,
INTERVAL-1 day_hour);
-1997-12-30 140000
MySQL SELECT date_sub (1998-01-02, INTERVAL);
-1997-12-02
MySQL SELECT EXTRACT (year from 1999-07-02);
-1999
MySQL SELECT EXTRACT (year_month from 1999-07-02 010203);
-199907
MySQL SELECT EXTRACT (day_minute from 1999-07-02 010203);
-20102

If you specify an interval value that is too short (excluding the desired interval for the type keyword), MySQL assumes that you skipped the leftmost part of the interval value