MySQL (10)-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;