Mysql time and date functions _ MySQL

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)