MySQL self-study (10) -- date functions and mysql self-study date functions

MySQL self-study (10) -- date functions and mysql self-study date functions
Date and Time Functions (1) functions for obtaining the current date and time

CURDATE () and CURRENT_DATE () functions to obtain the current date

Select current_date (), curdate (), curdate () + 0;

 

Curdate () + 0 indicates converting the current time to numeric type

 

CURTIME () and CURRENT_TIME () Get the current time

Select curtime (), current_time (), current_time () + 0;

 

Current_time () + 0 indicates converting the current time to numeric type

 

(2) function for obtaining date + time

The CURRENT_TIMESTAMP (), LOCALTIME (), NOW (), and SYSDATE () functions are used to obtain the current date and time.

Select current_timestamp (), localtime (), now (), sysdate ();

 

 

(3) UNIX timestamp Functions

If UNIX_TIMESTAMP (date) is called without a parameter, a UNIX timestamp is returned as an unsigned integer. If you use date to call UNIX_TIMESTAMP (), it will return the parameter value in the form of the number of seconds after '2017-01-01 00:00:00 'GMT.

Select unix_timestamp (), unix_timestamp (now (), now ();

 

 

(4) functions that return the UTC date and the UTC time

UTC_DATE () returns the current UTC date value in the format of 'yyyy-MM-DD 'or 'yyyymmdd', depending on whether the function is used in a string or numeric context.

Select utc_date (), utc_date () + 0;

 

 

(5) functions of obtaining a MONTH: MONTH (date) and MONTHNAME (date)

Select month (now (), monthname (now ());

 

(6) obtain the DAYNAME (d), DAYOFWEEK (d), and WEEKDAY (d) Functions of the week)

Select dayname (now (), dayofweek (now (), weekday (now ());

 

(7) obtain the WEEK functions WEEK (d) and WEEKOFYEAR (d)

Select week (now (), weekofyear (now ());

 

(8) functions for obtaining the number of days: DAYOFYEAR () and DAYOFMONTH ()

Select dayofyear (now (), dayofweek (now ());

 

 

(9) obtain the year, quarter, hour, minute, and second Functions

Select year (now (), quarter (now (), hour (now (), minute (now (), second (now ());

 

(10) function EXTRACT (type FROM date) for obtaining the specified value of a date)

Select extract (year from now (), extract (year_month from now ());

 

 

(11) time and second conversion functions TIME_TO_SEC (time)

Select time_to_sec ('23: 23: 00 ');

 

 

(12) functions that format the date and time

DATE_FORMATE (date, formate) displays the date value according to the format specified by formate. The main formate format is shown in the following table.

DATE_FORMATE time and date format

Description	Description
%	Abbreviation of workday
% B	Abbreviated month name
% C	Month
% D	Date of the month with English suffix
% D	The date of the month, in the numerical format
% E	The date of the month, in the numerical format
% F	Subtle
% H	24 hours in double digits
% H, % I	12 hours in double digits
% I	Minute, digit format
% J	Days in a year
% K	In 24 hours
% L	In 12 hours
% M	Month name
% M	Month, digit format
% P	AM or PM
% R	Time, in 12-hour format
% S, % s	Second in double digits
% T	Time, in 24-hour format
% U	Week. Sunday is the first day of the week.
% U	Week, Monday is the first day of the week
% V	Week. Sunday is the first day of the week.
% V	Week, Monday is the first day of the week
% W	Workday name
% W	Daily in a week
% X	The year of the week. Sunday indicates the first day of the week.
% X	The year of the week. Monday indicates the first day of the week.
% Y	4-digit format indicates the year
% Y	2-digit year
%	'%' Text character

 

Select date_format ('2017-01-04 ',' % W % M % y') as col1;