MySQL Learning Note III: Date and Time functions

MYSQL Learning Note III: Date and Time Functions

1. Get the function of the current date and the function to get the current time

/* Get the current date function and get the current time function, return the date in ' Yyyy-mm-dd ' or ' YYYYMMDD ' format

*/

#CURDATE () same as current_date () , returns the same system current date

#CURDATE () +0 converts the current date value to a numeric type

SELECT curdate (), Current_date (), curdate () +0;

/* Curtime () and current_time () have the same effect, returning the current time in ' HH:MM:SS ' or ' HHMMSS ' format

*/

#CURRENT_TIME () +0 indicates that the HHMMSS format is displayed in numerical form

SELECT curtime (), Current_time (), Current_time () +0;

2. Get the current date and time function

# as you can see from the results, the following four functions are functionally identical

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

3. Get the Month function

#MONTH (date): The function returns The month of the date, ranging from 1~12

#MONTHNAME (date): The function returns The English full name of the month for the date, such as March

SELECT MONTH (now ()), MONTHNAME (now ());

4. Get the function of the week

#DAYNAME (d): returns the English name of the working day corresponding to D, such as Sunday, Monday, etc.

#DAYOFWEEK (d): returns the Index (position) of the week corresponding to D. 1 means Sunday,2 means Monday,..., 7 means Saturday

#WEEKDAY (d): returns the index of the working day corresponding to D. 0 means Monday,1 means Tuesday,... 6 = Sunday

SELECT Dayname (now ()), DAYOFWEEK (now ()), WEEKDAY (now ());

5. function to get the number of weeks

#WEEK (d): The calculated date D is the week ordinal of the year.

#WEEK (D,mode): Specifies whether mode starts in Sunday or Monday , and if mode is omitted, use Default_week_format value of the system argument 0

Value of Mode parameter in #WEEK function

Mode	The first day of the week	Range	Week 1 for the first week
0	Sunday	0~53	There's a Sunday in the year.
1	Monday	0~53	more than 3 days in the year
2	Sunday	1~53	There's a Sunday in the year.
3	Monday	1~53	more than 3 days in the year
4	Sunday	0~53	more than 3 days in the year
5	Monday	0~53	There's a Monday in the year.
6	Sunday	1~53	more than 3 days in the year
7	Monday	1~53	There's a Monday in the year.

#WEEKOFYEAR (d): calculates the day of the week in which D is located, in the range of 1~53. Equivalent to WEEK (d,3)

SELECT WEEK (now ()), WEEK (now (), 0), WEEK (now (), 1), WeekOfYear (now ()), WEEK (now (), 3);

6. Get the days function

#DAYOFYEAR (d): return D is the day ordinal of the year, ranging from 1~366

#DAYOFMONTH (d): return D is the day ordinal of one months, the range is 1~31

SELECT DayOfYear (now ()), DayOfMonth (now ());

7. Get the year, quarter, month, hour, minute, and second functions

#YEAR (date): returns the year in which the date corresponds, the range is 1970~2069

#QUARTER (date): returns the quarter of the year in which the date corresponds, the range 1~4

#MONTH (date): The function returns The month of the date, ranging from 1~12

#MONTHNAME (date): The function returns The English full name of the month for the date, such as March

#HOUR (time): returns the time corresponding to the number of hours, the range is 0~24

#MINUTE (time): returns the number of minutes corresponding to the time, ranging from 0~59

#SECOND (time): returns the time corresponding to the number of seconds, the range is 0~59

SELECT

Year (Curdate ()) as Yr,

QUARTER (Curdate ()) as QT,

MONTH (Curdate ()) as Mon,

MONTHNAME (Curdate ()) as Mon_name,

HOUR (Curtime ()) as Hrs,

MINUTE (Curtime ()) as Min,

SECOND (Curtime ()) as snd;

8. Get date specified value function EXTRACT (type from date)

# to extract part of a value from date by type

SELECT

EXTRACT (year from today ()) as year,

EXTRACT (QUARTER from Now ()) as QUARTER,

EXTRACT (month from now ()) as MONTH,

EXTRACT (Day from Today ()) as Day,

EXTRACT (HOUR from Now ()) as HOUR,

EXTRACT (MINUTE from Now ()) as MINUTE,

EXTRACT (SECOND from Now ()) as SECOND,

EXTRACT (Year_month from Now ()) as Year_mon

;

9. Time and seconds conversion function time_to_sec

#转换公式:< hours *3600+ minutes *60+ sec >

#TIME_TO_SEC (time): returns the time parameter that has been converted to seconds

#SEC_TO_TIME (seconds): Returns the seconds parameter value converted to hours, minutes, and seconds

SELECT

Curtime () as time,

Time_to_sec (Curtime ()) as second,

Sec_to_time (Time_to_sec (Curtime ())) as time;

..... Cond

This article has not been completed, with the time relationship, the following will continue, thank you for your attention!

If you have better suggestions, please email to my personal email address: [Email protected]

Copyright @: This article is my original, reproduced please indicate the source, thank you!

MySQL Learning Note III: Date and Time functions