Returns the date-related Now () | CURRENT_TIMESTAMP (); returns the current time to_days (date). returns the date value of the date value from the date of the year to date (not counted before January 1, 1582) convert to days date is the DATE type FROM_DAYS (N), which gives the number of days since the date value returned by the West dollar 0 (returns the DATE value related to the date)
Now () | CURRENT_TIMESTAMP (); returns the current time
To_days (date) return date is the number of days since the date of the West yuan 0 year (not calculated before January 1, 1582) to convert to days date is the date type
FROM_DAYS (N) indicates the number of days since 0 to DATE returned by the western yuan (not counted before January 1, 1582)
CURDATE () | CURRENT_DATE () | SYSDATE () current date // uppercase
WeekDay (date) returns the day of the week of the specified date // The parameter is of the date type and cannot be in the timestamp format
DAYOFWEEK (date) returns the day of the week of the specified date // The parameter is of the date type and cannot be in the timestamp format
DAYNAME (date) returns the day of the week of the specified date, specifically to the English word // The parameter is a date type, cannot be a timestamp format
WEEK (date, first) // The parameter is of the date type, and cannot be in the timestamp format. the return date is the WEEK of the year (the default value of first is 0, 1 indicates that Monday is the beginning of the week, and 0 starts from Sunday)
DAYOFMONTH (date) returns the number of the specified date in the current month. // The parameter is of the date type and cannot be in the timestamp format.
MONTH (date) returns the MONTH of the specified date // The parameter is of the date type and cannot be in the timestamp format
MONTHNAME returns the date value (returned by English name) // The parameter is of the date type and cannot be in the timestamp format.
QUARTER (date) returns the QUARTER of a year. // The parameter is of the date type and cannot be in the timestamp format.
YEAR (date) returns the date YEAR // The parameter is of the date type and cannot be in the timestamp format
DATE_FORMAT (Date, format); format the date value based on the format string
Select DATE_FORMAT (CURDATE (), '% W % M % Y ');
% W indicates the day of the week in English
% M indicates the English month
% Y indicates the year in English
Select DATE_FORMAT (Now (), '% H: % I: % S ');
% H returned hours
% I returns the number of minutes
% S returned seconds
Select DATE_FORMAT (Now (), '% D % y % a % d % m % B % J ')
% J days in a year (001 ...... 366)
% B abbreviated month name (Jan ...... Dec)
% M month, number (01 ...... 12)
% D number of days in the month (00 ...... 31)
% A abbreviated name of the week (Sun ...... Sat)
% Y year, number, 2 digits
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
Select DATE_FORMAT (Now (), '% H % k % I % r % T % S % W ');
% K hour (0 ...... 23)
% L hour (1 ...... 12)
% R time, 12 hours (hh: mm: ss [AP] M)
% T time, 24 hours (hh: mm: ss)
% S seconds (00 ...... 59)
% W days in a week (0 = Sunday ...... 6 = Saturday)
% E number of days in the month (0 ...... 31)
% B abbreviated month name (Jan ...... Dec)
% I hour (01 ...... 12)
% S seconds (00 ...... 59)
% P AM or PM
% 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 %
Returns time-related
CURTIME () returns the current time
HOUR (); returned hours // The parameter is of the date type and cannot be in the timestamp format
MINUTE (time) returns the number of minutes // The parameter is of the date type and cannot be in the timestamp format
SECOND (time) returns the number of seconds // The parameter is of the date type and cannot be in the timestamp format
Addition and subtraction of dates
DATE_ADD (date, INTERVAL expr type) addition operation
DATE_SUB (date, INTERVAL expr type) Subtraction
ADDDATE (date, INTERVAL expr type) addition
SUBDATE (date, INTERVAL expr type) Subtraction
Addition and subtraction of date and time
Expr type
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"
Select adddate (NOW (), INTERVAL 5 DAY );
SELECT DATE_ADD (NOW (), INTERVAL 5 DAY );
SELECT DATE_SUB (NOW (), INTERVAL 5 DAY );
Select subdate (NOW (), INTERVAL 5 DAY );
UNIX_TIMESTAMP () UNIX_TIMESTAMP (Date); returns a Unix timestamp (the number of seconds since '2017-01-01 00:00:00 'GMT, that is, the number of seconds
FROM_UNIXTIME (UNIX_TIMESTAMP (); returns the string of the specified date based on the number of seconds
FROM_UNIXTIME (unix_timestamp, format) format by yourself, return the string of the specified date
TIME_TO_SEC (time) returns the number of seconds of the time value
SEC_TO_TIME (seconds) returns the TIME value converted from seconds in 'hh: MM: SS' or HHMMSS format (depending on the context in which the returned value is a string or number)