MySQL Date and Time Functions

MySQL Date and Time Functions
MySQL Date and Time Functions

DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC Standard)
Mysql> select DAYOFWEEK ('2017-02-03 ');
-> 3
WEEKDAY (date)
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');
-> 5
Mysql> select WEEKDAY ('2017-11-05 ');
-> 2
DAYOFMONTH (date)
Returns the day (within the range of 1 to 31) from January 1, January)
Mysql> select DAYOFMONTH ('2017-02-03 ');
-> 3
DAYOFYEAR (date)
Returns the day (within the range of 1 to 366) of a year)
Mysql> select DAYOFYEAR ('2017-02-03 ');
-> 34
MONTH (date)
Returns the month value in date.
Mysql> select MONTH ('2014-02-03 ');
-> 2
DAYNAME (date)
Returns the day of the week by the English name)
Mysql> select DAYNAME ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month of the date value (returned by English name)
Mysql> select MONTHNAME ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of the year for date.
Mysql> select QUARTER ('98-04-01 ');
-> 2
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)
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 (range: 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 ('10: 05: 03 ');
-> 10
MINUTE (time)
Returns the number of minutes of time (ranging from 0 to 59)
Mysql> select MINUTE ('98-02-03 10:05:03 ');
-> 5
SECOND (time)
Returns the number of seconds (ranging from 0 to 59) of time)
Mysql> select SECOND ('10: 05: 03 ');
-> 3
PERIOD_ADD (P, N)
Add N months to period P and return (P format: YYMM or YYYYMM)
Mysql> select PERIOD_ADD (9801,2 );
-> 199803
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)
Mysql> select TO_DAYS (950501 );
-> 728779
Mysql> select TO_DAYS ('2017-10-07 ');
-> 729669
FROM_DAYS (N)
Returns the DATE value for the day from the DATE of the West dollar (not counted before January 1, 1582)
Mysql> select FROM_DAYS (729669 );
-> '2017-10-07'
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)
CURDATE ()
CURRENT_DATE ()
Returns the current date value in 'yyyy-MM-DD 'or YYYYMMDD format (depending on the context in which the returned value is a string or number)
Mysql> select CURDATE ();
-> '2017-12-15'
Mysql> select CURDATE () 0;
-> 19971215
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)
Mysql> select CURTIME ();
-> '23: 50: 26'
Mysql> select CURTIME () 0;
-> 235026
NOW ()
SYSDATE ()
CURRENT_TIMESTAMP ()
Returns the current date time in 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS format (depending on the context in which the returned value is a string or number)
Mysql> select NOW ();
-> '2017-12-15 23:50:26'
Mysql> select NOW () 0;
-> 19971215235026
UNIX_TIMESTAMP ()
UNIX_TIMESTAMP (date)
Returns a Unix timestamp (the number of seconds since '2017-01-01 00:00:00 'GMT, and the default value of date is the current time)
Mysql> select UNIX_TIMESTAMP ();
-> 882226357
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580
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)
Mysql> select FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'
Mysql> select FROM_UNIXTIME (875996580) 0;
-> 19971004222300
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

Mysql calculates the time difference between two dates in the hour-minute format.

If datetime_expr1 and datetime_expr2 are of the datetime type
Select time_format (timediff ('2014: 01: 31 23:59:59 ', '2014: 01: 01 00:00:00'), '% Hh % im ')
May meet your requirements

What is the function for MySQL to get the current time? What is the function for formatting the date?

Use now () to obtain the current time.
Format the time in the database using DATE_FORMA T (date, format ).
Format the date or date and Time Value Based on the format string to return the result string.

You can use DATE_FORMAT () to format the DATE or DATETIME value to obtain the expected format. Format the date value based on the format string:

% S, % s two-digit second (,..., 59)

% I two-digit score (,..., 59)

% H two-digit hour, 24 hours (,..., 23)

% H, % I two-digit hour, 12 hours (,..., 12)

% K, 24 hours (0, 1,..., 23)

% L hour in numeric format (1, 2,..., 12)

% T 24-hour time format (h: m: s)

% R 12-hour time format (hh: mm: ss AM or hh: mm: ss PM)

% P AM or P M

% W name of each day in a week (S u n d a y, Monday,..., Saturday)

% A abbreviation of the name of each day in a week (Sun, Mon,..., Sat)

% D indicates the number of days in a month (00, 01,..., 31)

% E indicates the number of days in a month (1, 2,..., 31)

% D indicates the number of days in a month (1st, 2nd, 3rd ,...)

% W represents the number of days in a week (0 = S u n d a y, 1 = Monday,..., 6 = Saturday)

% J represents the number of days in a year in three digits (001,002,..., 366)

% U Week (0, 1, 52), where Sunday is the first day of the week

% U Week (0, 1, 52), where Monday is the first day of the week

% M month name (J a n u a r y, February,..., December)

% B abbreviated month name (J a n u a r y, February,..., December)

% M double-digit month (01, 02,..., 12)

% C indicates the month (1, 2,..., 12)

The year in the format of % Y.

The year in double-digit % y format.

% Direct value "%"

Select date_format (Date Field, '% Y-% m-% D') as 'date' from test

This is basically the case. It will be a good performance soon ......