Mysql time operation function usage summary

Mysql time and date functions are one of the most important things in mysql databases. let me introduce you to mysql time and date usage. if you need them, refer to them.

Mysql time and date functions are one of the most important things in mysql databases. let me introduce you to mysql time and date usage. if you need them, refer to them.

I. time difference

Datediff: to put it bluntly, the first time is used to subtract the second time, and the order cannot be forgotten.

The code is as follows:
Select datediff ('2017-08-08 ', '2017-08-13'); ---5

II. obtain the current time

The code is as follows:
Now Current_timestamp () , Current_timestamp , Localtime () , Localtime , Localtimestamp -- (v4.0.6) , Localtimestamp () -- (v4.0.6)

Sysdate (); // Obtain the system time dynamically. This should be distinguished from the now function. now is like defining a variable. sysdate is equal to the variable determined after running, so they have a time difference.

// Obtain the system time dynamically. This should be distinguished from the now function. now is like defining a variable. sysdate is equal to the variable determined after running, so they have a time difference.
3. obtain the current date function

Curdate () current_date (), current_date

4. obtain the current time

The code is as follows:
Curtime () current_time (), current_time

5. obtain the current UTC date and time function

The code is as follows:
Select utc_timestamp (), utc_date (), utc_time (), now ()

VI. Mysql date and time functions

The code is as follows:
Set @ dt = '2017-09-10 07:15:30. 123 '; 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. The value of date_col is within the last 30 days:

The code is as follows:
Mysql> SELECT something FROM table WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) select DAYOFWEEK ('2017-02-03 '); -> 3

WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).

The code is as follows:
Mysql> select WEEKDAY ('2017-10-04 22:23:00 '); -> 5 Mysql> select WEEKDAY ('2017-11-05 '); -> 2

DAYOFMONTH (date)
Returns the date of a month in the range of 1 to 31.

The code is as follows:
Mysql> select DAYOFMONTH ('2017-02-03 '); -> 3

DAYOFYEAR (date)
Returns the number of days in a year from 1 to 366.

The code is as follows:
Mysql> select DAYOFYEAR ('2017-02-03 '); -> 34

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

The code is as follows:
Mysql> select MONTH ('2014-02-03 '); -> 2

DAYNAME (date)
Returns the week name of date.

The code is as follows:
Mysql> select DAYNAME ("1998-02-05 "); -> 'Thursday'

MONTHNAME (date)
Returns the month name of date.

The code is as follows:
Mysql> select MONTHNAME ("1998-02-05 "); -> 'February'

QUARTER (date)

Returns the quarter of a year from date, ranging from 1 to 4.

The code is as follows:
Mysql> select QUARTER ('98-04-01 '); -> 2 WEEK (date) 　 WEEK (date, first)

If Sunday is the first day of a week, there is a single parameter that returns the number of weeks of the date, ranging from 0 to 52. Two parameter formats: WEEK ()
Specify whether the week starts on Sunday or Monday. If the second parameter is 0 and the week starts from Sunday, if the second parameter is 1,
Starting from Monday.

The code is as follows:
Mysql> select WEEK ('2017-02-20 '); -> 7 Mysql> select WEEK ('2017-02-20 ', 0 ); -> 7 Mysql> select WEEK ('2017-02-20 ', 1 ); -> 8

YEAR (date)
Returns the year of date, ranging from 1000 to 9999.

The code is as follows:
Mysql> select YEAR ('98-02-03 '); -> 1998

HOUR (time)
Returns the hour of time, ranging from 0 to 23.

The code is as follows:
Mysql> select HOUR ('10: 05: 03 '); -> 10

MINUTE (time)

Returns the minute of time, ranging from 0 to 59.

The code is as follows:
Mysql> select MINUTE ('98-02-03 10:05:03 '); -> 5

SECOND (time)
The number of seconds for the return time, ranging from 0 to 59.

The code is as follows:
Mysql> select SECOND ('10: 05: 03 '); -> 3

PERIOD_ADD (P, N)
Add N months to phase P (in the format of YYMM or YYYYMM ). Return value in the format of YYYYMM. Note that the phase parameter P is not a date value.

The code is as follows:
Mysql> select PERIOD_ADD (9801,2 ); -> 199803

PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2. P1 and P2 should be in the format of YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.

The code is as follows:
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 functions perform date operations. For MySQL 3.22, they are new. ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB.
In MySQL 3.23, you can use + and-instead of DATE_ADD () and DATE_SUB (). (See the example) date is a specified start date.
DATETIME or DATE value. expr is an expression that specifies the value to be added to the start DATE or the value to be subtracted from the start DATE. expr is a string.
A "-" indicates the negative interval. Type is a keyword that specifies how the expression should be interpreted. EXTRACT (type FROM date) function FROM date
Returns the "type" interval. The following table shows how the type and expr parameters are associated: the type value indicates the expected expr format.

SECOND SECONDS
MINUTE MINUTES
HOUR time HOURS
DAY DAYS
MONTH-MONTH MONTHS
YEAR YEARS
MINUTE_SECOND MINUTES and SECONDS "MINUTES: SECONDS"
HOUR_MINUTE hour and minute "HOURS: MINUTES"
DAY_HOUR and hour "days hours"
YEAR_MONTH and month "YEARS-MONTHS"
HOUR_SECOND hour, minute, "HOURS: MINUTES: SECONDS"
DAY_MINUTE day, hour, minute "days hours: MINUTES"
DAY_SECOND day, hour, minute, second "days hours: MINUTES: SECONDS"