MySQL (basic) (time and date functions)

I. date and Time Functions DAYOFWEEK (date) return the date of the Week (1 Sunday, 2 Monday ,...... 7 Saturday, ODBC standard) WEEKDAY (date) returns the date of the Week (0 Monday, 1 Tuesday ,...... 6 Sunday ). MONTH (date) returns the number of months in date. DAYNAME (date) returns the day of the week (returned by English name ).

I. date and Time Functions DAYOFWEEK (date) return the date of the Week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC standard) WEEKDAY (date) returns the date of the Week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ). MONTH (date) returns the number of months in date. DAYNAME (date) returns the day of the week (returned by English name ).

Zookeeper

I. Date and Time Functions

DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC Standard)

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

MONTH (date)
Returns the month value in date.

DAYNAME (date)
Returns the day of the week by the English name)

MONTHNAME (date)
Returns the month of the date value (returned by English name)

QUARTER (date)
Returns the quarter of the year for date.

WEEK (date, first)
Returns the week number Of The Year for date. (the default value of first is 0. The value of first is 1, indicating that Monday is the start of the week. 0 starts from Sunday)

YEAR (date)
Returns the year of date (range: 1000 to 9999)

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

MINUTE (time)
Returns the number of minutes of time (ranging from 0 to 59)

SECOND (time)
Returns the number of seconds (ranging from 0 to 59) of time)

PERIOD_ADD (P, N)
Add N months to period P and return (P format: YYMM or YYYYMM

PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2 (in the format of YYMM or YYYYMM for P1 and P2

PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2 (in the format of YYMM or YYYYMM for P1 and P2)
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)
Addition and subtraction of date and time
(ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB (). You can also use operators and-instead of functions.
Date is a DATETIME or DATE value. An expression string type used for the addition and subtraction of date by expr indicates how the expression expr should be interpreted.
[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"
Expr allows any punctuation to be used as a separator. If all values are DATE values, the result is a DATE value; otherwise, the result is a DATETIME value)
If the type keyword is incomplete, MySQL will take the value from the right end. DAY_SECOND is equal to MINUTE_SECOND because the hour is missing)
If you increase MONTH, YEAR_MONTH, or YEAR, the number of days is greater than the maximum number of days in the result MONTH)
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> SELECT DATE_ADD ("23:59:59", INTERVAL 1 SECOND );
-> 00:00:00
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL 1 DAY );
-> 23:59:59
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL "" MINUTE_SECOND );
-> 00:01:00
Mysql> SELECT DATE_SUB ("00:00:00", INTERVAL "1" DAY_SECOND );
-> 1997-12-30 22:58:59
Mysql> SELECT DATE_ADD ("00:00:00", INTERVAL "-1 10" DAY_HOUR );
-> 1997-12-30 14:00:00
Mysql> SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY );
-> 1997-12-02
Mysql> select extract (year from "maid ");
-> 1999
Mysql> select extract (YEAR_MONTH FROM "01:02:03 ");
-> 199907
Mysql> select extract (DAY_MINUTE FROM "01:02:03 ");
-> 20102

TO_DAYS (date)
Returns the number of days since the date of the date (not calculated before January 1, 1582)

FROM_DAYS (N)
Returns the DATE value for the day from the DATE of the West dollar (not counted before January 1, 1582)

DATE_FORMAT (date, format)
Format the date value based on the format string
(The flag is available 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.
% Characters %)
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % y ');
-> 'Saturday October 1997'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % s ');
-> '22: 23: 00'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % D % y % a % d % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> select DATE_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'
TIME_FORMAT (time, format)
Similar to DATE_FORMAT (), but TIME_FORMAT only processes hours, minutes, And seconds (other symbols generate a NULL value or 0)

CURTIME ()
CURRENT_TIME ()
Returns the current time value in 'hh: MM: ss' or HHMMSS format (based on the context in which the returned value is a string or number)

FROM_UNIXTIME (unix_timestamp)
Returns the timestamp value in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format (depending on the context in which the returned value is a string or number)

FROM_UNIXTIME (unix_timestamp, format)
Returns the timestamp value in format.
Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % s % x ');
-> '2014 23rd December 03:43:30 x'
SEC_TO_TIME (seconds)
Returns the TIME value in the format of 'hh: MM: ss' or HHMMSS in seconds (depending on the context of the returned value, it is a string or number)
Mysql> select SEC_TO_TIME (2378 );
-> '00: 39: 38'
Mysql> select SEC_TO_TIME (2378) 0;
-> 3938
TIME_TO_SEC (time)
How many seconds does the returned time Value have?
Mysql> select TIME_TO_SEC ('22: 23: 00 ');
-> 80580
Mysql> select TIME_TO_SEC ('00: 39: 38 ');
-> 2378