Summary of MySql common date and time functions (unfinished)

/*
Date_add (date, interval expr type) and date_sub (date, interval expr type) perform date operations. Date is a datetime or date value, used to specify the start time. Expr is an expression used to specify the time interval value added or subtracted from the start date. Expr is a string. For the time interval of negative values, it can start with. Type indicates the expression interpretation method.
The keywords interva and type are case insensitive.

Date operation. adddate is the synonym of date_add. If no interval is specified for adddate, the number of days is used as interval by default (date_add does not work)
*/
Select date_add ('2017-01-01 ', interval 30 day), date_add ('2017-01-01', interval-1 day), adddate ('2017-01-01 ', interval 30 day), adddate ('2017-01-01 ', 30), date_sub ('2017-01-01', interval 1 day), subdate ('2017-01-01 ', interval 1 day );

-- Time Operation
Select addtime ('2017-01-01 00:00:00 ', '1: 05: 33'), addtime ('00: 00: 00', '1: 05: 33 ');

-- Get the current date. After + 0, the split line will be removed. current_date and curdate are synonyms.
Select curdate (), curdate () + 0, current_date ();

-- Get the current time. curtime and current_time are synonyms.
Select curtime (), current_time (), current_time () + 0;

-- Query the current server time zone and the currently connected time zone. The initial value of the currently connected time zone is the same as the global variable time_zone, but you can reset it using the following statement: set time_zone = timezone;
Select @ global. time_zone, @ session. time_zone;

-- Specify time zone conversion
Select convert_tz ('2017-01-01 12:00:00 ',' + ',' + ');

-- Get the current date and time. current_timestamp and now are synonyms.
Select current_timestamp (), now (), now () + 0, current_timestamp () + 0;

-- Extract the date part of the date or time expression expr.
Select date ('2014-12-31 01:02:03 ');

 
-- Datediff (expr, expr), returns the number of days between expr and expr2. Expr and expr2 are date or date-and-time expressions. Only the date portion of these values is used in the calculation.
Select datediff ('2017-12-31 23:59:59 ', '2017-12-30'), datediff ('2017-11-30 23:59:59 ', '2017-12-31 ');

/*
Mysql allows punctuation separators in any expr format. The recommended Delimiter is displayed in the table. If the date parameter is a date value, and your calculation only includes 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.

If the expression at the other end is a date or datetime value, interval expr type is only allowed at both ends of the + operator. For the-operator, interval expr type is only allowed on the right side, because it is meaningless to extract a date or datetime value from a time interval. (See the example below ).
*/
Select '2014-12-31 '+ interval 1 second, '2014-12-31'-interval 1 second, '2014-12-31 23:59:59 '+ interval 1 second, interval 1 day + '2017-12-31 ', '2017-01-01'-interval 1 second;

-- Interval can also use negative values.
Select '2014-12-31 '+ interval-1 day;

-- Date and date Formatting Function date_format (date, format)
Select date_format (now (), '% y-% m-% d % h: % I: % s ');

-- Commonly used functions to obtain a part of a date
Select day (now (), dayname (now (), dayofweek (now (), dayofyear (now ())

-- Extract (type from date)
-- The description of the time interval type used by the extract () function is the same as that of date_add () or date_sub (), but it extracts its part from the date instead of performing the date operation.
-- Get the month, days, less than 10 will not add 0
Select extract (year from '2014-07-02 '), extract (month from '2014-07-02 ');

 

-- Convert time to timestamp select unix_timestamp ('2017-10-26 6-06-07 ') if the parameter is null, it is processed as the current time -- convert the timestamp to the time

Select from_unixtime (1256540102)