MySQL Self-study chapter (10)--Date function

Date and Time Functions (1a function that gets the function and time of the current date

Curdate () and current_date () function to get the current date

Select Current_date (), Curdate (), curdate () +0;

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

Curtime () and current_time () get the current time

Select Curtime (), Current_time (), Current_time () +0;

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

（2) Gets the date+the function of time

Current_timestamp (), localtime (), now (), Sysdate () 4 functions are the same, are used to get the current date and time

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

（3)UNIXtime stamp function

Unix_timestamp (date) If no parameter is called, returns a UNIX timestamp as an unsigned integer. If you call unix_timestamp ()with date , it will take the parameter value '1970-01-01: XX: Returns the number of seconds after the GMT.

Select Unix_timestamp (), Unix_timestamp (now ()), now ();

(4) return UTCDate function and returnUTCthe function of time

Utc_date () returns the current UTC Date value in the format 'yyyy-mm-dd' or 'YYYYMMDD', The exact format depends on whether the function is used in a string or a numeric context.

Select Utc_date (), utc_date () +0;

(5) Get the month function month (date)and theMONTHNAME (date)

Select Month (now ()), MonthName (now ());

（6gets the function of the weekDayname (d),DAYOFWEEK (d)and theWEEKDAY (d)

Select Dayname (now ()), DayOfWeek (now ()), Weekday (now ());

（7gets the function of the weekWEEK (d)and theWeekOfYear (d)

Select Week (now ()), WeekOfYear (now ());

(8) Get the number of days function dayofyear ()and theDayOfMonth ()

Select DayOfYear (now ()), DayOfWeek (now ());

(9) function to get year, quarter, hour, minute, and second numbers

Select year (now ()), Quarter (today ()), Hour (now ()), Minute (now ()), second (now ());

(10) A function extract that gets the specified value of the date (type from date)

Select Extract (Year from now ()), extract (Year_month from now ());

(11) Time and number of seconds conversion function time_to_sec

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

（ A) A function that formats 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 Date format

Specifiers	Description
%a	Abbreviated name of the weekday
%b	Abbreviated name of the month
%c	Month
%d	Date of the month with English suffix
%d	Date of the month, in digital form
%e	Date of the month, in digital form
%f	Subtle
%H	The two-digit number represents the hour
%h,%I	A double-digit hour
%i	Minutes, digital form
%j	Number of days in a year
%k	In hours
%l	In hours
%M	Month Name
%m	month, digital form
%p	am or pm
%r	Time, Hour system
%s,%s	Two-digit seconds
%T	Time, Hour system
%u	Week, Sunday for the first day of the week
%u	Week, Monday for the first day of the week
%V	Week, Sunday for the first day of the week
%v	Week, Monday for the first day of the week
%W	Weekday name
%w	Daily in the Week
%x	The year of the week, where Sunday is the first day of the week
%x	The year of the week, where Monday is the first day of the week
%Y	4 -digit representation of the year
%y	2 -digit representation of the year
%%	'%' literal characters

Select Date_format (' 2015-01-04 ', '%w%m%y ') as col1;

MySQL Self-study chapter (10)--Date function