Mysql date, mysql time, mysql date calculation, mysql time calculation, mysql date

2. DAYOFWEEK (date) 3. WEEKDAY (date) 4. DAYOFMONTH (date) 5. DAYOFYEAR (date) 6. MONTH (date) 7. DAYNAME (date) 8. MONTHNAME (date) 9. QUARTER (date) 10. WEEK (date) WEEK (date, first) 11. YEAR (date) 12. HOUR (time) 13. MINUTE (time) 14.S

2. DAYOFWEEK (date) 3. WEEKDAY (date) 4. DAYOFMONTH (date) 5. DAYOFYEAR (date) 6. MONTH (date) 7. DAYNAME (date) 8. MONTHNAME (date) 9. QUARTER (date) 10. WEEK (date) WEEK (date, first) 11. YEAR (date) 12. HOUR (time) 13. MINUTE (time) 14. S

2.DAYOFWEEK (date)
3.WEEKDAY(Date)
4.DAYOFMONTH(Date)
5.DAYOFYEAR(Date)
6.MONTH(Date)
7.DAYNAME(Date)
8.MONTHNAME(Date)
9.QUARTER(Date)
10.WEEK(Date)
WEEK(Date, first)
11.YEAR(Date)
12.HOUR(Time)
13.MINUTE(Time)
14.SECOND(Time)
15.PERIOD_ADD(P, N)
16.PERIOD_DIFF(P1, P2)
17.DATE_ADD(Date, INTERVAL expr type)
DATE_SUB(Date, INTERVAL expr type)
ADDDATE(Date, INTERVAL expr type)
SUBDATE(Date, INTERVAL expr type)
EXTRACT(Type FROM date)
18.TO_DAYS(Date)
19.FROM_DAYS(N)
20.DATE_FORMAT(Date, format)
21.TIME_FORMAT(Time, format)
22.CURDATE()
23.CURTIME()
24.SYSDATE()
25.UNIX_TIMESTAMP()
UNIX_TIMESTAMP(Date)
26.FROM_UNIXTIME(Unix_timestamp)
FROM_UNIXTIME(Unix_timestamp, format)
27.SEC_TO_TIME(Seconds)
28.TIME_TO_SEC(Time)

######################################## ######################################## #

1. Here is an example of using the date function. The following query Selects all records. The value of date_col is within the last 30 days:

Mysql> SELECT something FROM table

WHERETO_DAYS(NOW ())-TO_DAYS(Date_col) <= 30;

2.DAYOFWEEK(Date)

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

Mysql> selectDAYOFWEEK('1970-02-03 ');

-> 3

3.WEEKDAY(Date)

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

Mysql> selectWEEKDAY('2017-10-04 22:23:00 ');

-> 5

Mysql> selectWEEKDAY('1970-1997 ');

-> 2

4.DAYOFMONTH(Date)

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

Mysql> selectDAYOFMONTH('1970-02-03 ');

-> 3

5.DAYOFYEAR(Date)

Returns the number of days in a year from 1 to 366.

Mysql> selectDAYOFYEAR('1970-02-03 ');

-> 34

6.MONTH(Date)

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

Mysql> selectMONTH('1970-02-03 ');

-> 2

7.DAYNAME(Date)

Returns the week name of date.

Mysql> selectDAYNAME("1998-02-05 ");

-> 'Thursday'

8.MONTHNAME(Date)

Returns the month name of date.

Mysql> selectMONTHNAME("1998-02-05 ");

-> 'February'

9.QUARTER(Date)

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

Mysql> selectQUARTER('98-04-01 ');

-> 2

10.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 () allows you to specify whether the WEEK starts on Sunday or Monday. If the second parameter is 0, the week starts from Sunday, and if the second parameter is 1, it starts from Monday.

Mysql> selectWEEK('1970-02-20 ');

-> 7

Mysql> selectWEEK('1970-02-20 ', 0 );

-> 7

Mysql> selectWEEK('1970-02-20 ', 1 );

-> 8

11.YEAR(Date)

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

Mysql> selectYEAR('98-02-03 ');

-> 1998

12.HOUR(Time)

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

Mysql> selectHOUR('10: 05: 03 ');

-> 10

13.MINUTE(Time)

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

Mysql> selectMINUTE('98-02-03 10:05:03 ');

-> 5

14.SECOND(Time)

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

Mysql> selectSECOND('10: 05: 03 ');

-> 3

15.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.

Mysql> selectPERIOD_ADD(9801, 2 );

-> 199803

16.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.

Mysql> selectPERIOD_DIFF(9802,199703 );

-> 11

17.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 DATETIME or DATE value that specifies the start date. expr is an expression that specifies the interval value added to the start DATE or subtracted from the start date. expr is a string; it can start with a "-" to indicate the negative interval. Type is a keyword that specifies how the expression should be interpreted.

EXTRACT(Type FROM date) The function returns the "type" interval FROM the date.

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"

MySQL allows any punctuation Separator in the expr format. It indicates that the recommended Delimiter is displayed. If the date parameter is a DATE value and your calculation only contains the YEAR, MONTH, and DAY sections (that is, there is no time section), the result is a DATE value. Otherwise, the result is a DATETIME value.

Mysql> SELECT "23:59:59" + INTERVAL 1 SECOND;

-> 00:00:00

Mysql> select interval 1 DAY + "1997-12-31 ";

-> 1998-01-01

Mysql> SELECT "1998-01-01"-INTERVAL 1 SECOND;

-> 1997-12-31 23:59:59

Mysql> SELECTDATE_ADD("23:59:59", INTERVAL 1 SECOND );

-> 00:00:00

Mysql> SELECTDATE_ADD("23:59:59", INTERVAL 1 DAY );

-> 23:59:59

Mysql> SELECTDATE_ADD("23:59:59", INTERVAL "" MINUTE_SECOND );

-> 00:01:00

Mysql> SELECTDATE_SUB("00:00:00", INTERVAL "1" DAY_SECOND );

-> 1997-12-30 22:58:59

Mysql> SELECTDATE_ADD("00:00:00", INTERVAL "-1 10" DAY_HOUR );

-> 1997-12-30 14:00:00

Mysql> SELECTDATE_SUB("1998-01-02", INTERVAL 31 DAY );

-> 1997-12-02

Mysql> SELECTEXTRACT(Year from "maid ");

-> 1999

Mysql> SELECTEXTRACT(YEAR_MONTH FROM "01:02:03 ");

-> 199907

Mysql> SELECTEXTRACT(DAY_MINUTE FROM "01:02:03 ");

-> 20102

If you specify an interval value that is too short (excluding the expected interval of the type keyword), MySQL assumes that you have saved the leftmost portion of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to be one day, hour, minute, and second. If you specify a value like "", MySQL assumes that the days and hours are lost and the value represents minutes and seconds. In other words, "" DAY_SECOND is interpreted as it is equivalent to "" MINUTE_SECOND, this interpretation of MySQL's TIME value indicates that the elapsed TIME is not as a one-day TIME. If you use an incorrect date, the result is NULL. If you increase MONTH, YEAR_MONTH, or YEAR and the result date is greater than the maximum number of days of the new MONTH, the day is adjusted by the maximum number of days of the new moon.

Mysql> selectDATE_ADD('2014-01-30 ', Interval 1 month );

-> 1998-02-28

Note: In the previous example, the INTERVAL and type keywords are not case sensitive.

18.TO_DAYS(Date)

Returns the number of days (from 0 years ).

Mysql> selectTO_DAYS(950501 );

-> 728779

Mysql> selectTO_DAYS('2017-10-07 ');

-> 729669

　　TO_DAYS ()It is not intended to be used to use the value before the occurrence of the glipro calendars (1582.

19.FROM_DAYS(N)

Returns a DATE value for the number of days N.

Mysql> select FROM_DAYS (729669 );

-> '2017-10-07'

20.DATE_FORMAT(Date, format)

Format the date value based on the format string. The following modifier can be used in the format string: % M month name (January ...... December)

% W name of the Week (Sunday ...... Saturday)

% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)

% Y year, number, 4 digits

% Y year, number, 2 digits

% A abbreviated name of the Week (Sun ...... Sat)

% D number of days in the month (00 ...... 31)

% E number of days in the month (0 ...... 31)

% M month, number (01 ...... 12)

% C month, number (1 ...... 12)

% B abbreviated month name (Jan ...... Dec)

% J days in a year (001 ...... 366)

% H hour (00 ...... 23)

% K hour (0 ...... 23)

% H hour (01 ...... 12)

% I hour (01 ...... 12)

% L hour (1 ...... 12)

% I minute, number (00 ...... 59)

% R time, 12 hours (hh: mm: ss [AP] M)

% T time, 24 hours (hh: mm: ss)

% S seconds (00 ...... 59)

% S seconds (00 ...... 59)

% P AM or PM

% W days in a week (0 = Sunday ...... 6 = Saturday)

% U Week (0 ...... 52). Sunday is the first day of the week.

% U Week (0 ...... 52) Monday is the first day of the week.

% A text "% ".

All other characters are not interpreted and copied to the result.

Mysql> selectDATE_FORMAT('2017-10-04 22:23:00 ',' % W % M % y ');

-> 'Saturday October 1997'

Mysql> selectDATE_FORMAT('2017-10-04 22:23:00 ',' % H: % I: % s ');

-> '22: 23: 00'

Mysql> selectDATE_FORMAT('2017-10-04 22:23:00 ',

'% D % y % a % d % m % B % J ');

-> '4th 97 Sat 04 10 Oct 123'

Mysql> selectDATE_FORMAT('2017-10-04 22:23:00 ',

'% H % k % I % r % T % S % W ');

-> '22 22 10 10:23:00 PM 22:23:00 6 ';

In MySQL3.23, % is required before the format modifier. In earlier MySQL versions, % is optional.

21.TIME_FORMAT(Time, format)

This is used like the preceding DATE_FORMAT () function, but the format string can only contain format modifiers for processing hours, minutes, and seconds.

Other modifiers generate a NULL value or 0.

22.CURDATE()

CURRENT_DATE

Returns today's date value in 'yyyy-MM-DD 'or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

Mysql> selectCURDATE();

-> '2017-12-15'

Mysql> selectCURDATE() + 0;

-> 19971215

23.CURTIME()

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.

Mysql> selectCURTIME();

-> '23: 50: 26'

Mysql> selectCURTIME() + 0;

-> 235026

NOW ()

24.SYSDATE()

CURRENT_TIMESTAMP

Returns the current date and time in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context.

Mysql> select NOW ();

-> '2017-12-15 23:50:26'

Mysql> select NOW () + 0;

-> 19971215235026

25.UNIX_TIMESTAMP()

　　UNIX_TIMESTAMP(Date)

If no parameter is called, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned ). If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a number of a DATE string, a datetime string, a TIMESTAMP, or a local time in YYMMDD or YYYYMMDD format.

Mysql> selectUNIX_TIMESTAMP();

-> 882226357

Mysql> selectUNIX_TIMESTAMP('2017-10-04 22:23:00 ');

-> 875996580

When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function accepts the value directly without the implicit "string-to-unix-timestamp" transformation.

26.FROM_UNIXTIME(Unix_timestamp)

Returns the value represented by the unix_timestamp parameter in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format, depending on the function being in a string

Or a digital context.

Mysql> selectFROM_UNIXTIME(875996580 );

-> '2017-10-04 22:23:00'

Mysql> selectFROM_UNIXTIME(875996580) + 0;

-> 19971004222300

　　FROM_UNIXTIME(Unix_timestamp, format)

Returns a string representing the Unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the DATE_FORMAT () function.

Mysql> selectFROM_UNIXTIME(UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % s % x ');

-> '2014 23rd December 03:43:30 x'

27.SEC_TO_TIME(Seconds)

Returns the seconds parameter, which is converted to hour, minute, and second. The value is formatted in 'hh: MM: ss' or HHMMSS, depending on whether the function is used in a string or a number context.

Mysql> selectSEC_TO_TIME(2378 );

-> '00: 39: 38'

Mysql> selectSEC_TO_TIME(2378) + 0;

-> 3938

28.TIME_TO_SEC(Time)

Returns the time parameter, which is converted to seconds.

Mysql> select TIME_TO_SEC ('22: 23: 00 ');

-> 80580

Mysql> select TIME_TO_SEC ('00: 39: 38 ');

-> 2378