MYSQL Study Notes 3: Date and Time Functions, mysql Study Notes

MYSQL Study Notes 3: Date and Time Functions, mysql Study Notes

MYSQL study Note 3: Date and Time Functions

1. Get the function of the current date and the function of 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 () and CURRENT_DATE () act the same and return the same system current date

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

Select curdate (), CURRENT_DATE (), CURDATE () + 0;

 

/* CURTIME () and CURRENT_TIME () act the same. The current time is returned in the format of 'hh: MM: ss' or 'hhmmss '.

*/

# CURRENT_TIME () + 0 indicates display in the numerical format HHMMSS

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

 

 

2. Get the function of the current date and time

# The results show that the following four functions have the same functions.

SELECT CURRENT_TIMESTAMP (), LOCALTIME (), NOW (), SYSDATE ();

 

 

3. Obtain the month function.

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

# MONTHNAME (date): The function returns the full name of the month corresponding to the date, such as March.

Select month (NOW (), MONTHNAME (NOW ());

 

 

4. Obtain the week Function

# DAYNAME (d): return the English name of the business day corresponding to d, such as Sunday and Monday.

# DAYOFWEEK (d): returns the index (location) in the week corresponding to d ). 1 indicates Sunday, 2 indicates Monday,..., 7 indicates Saturday

# WEEKDAY (d): returns the index of the business day corresponding to d. 0 indicates Monday, 1 indicates Tuesday,... 6 indicates Sunday

Select dayname (NOW (), DAYOFWEEK (NOW (), WEEKDAY (NOW ());

 

 

5. Function for obtaining the number of weeks

# WEEK (d): Calculate the date d as the WEEK of the year.

# WEEK (d, mode): determines whether the mode starts on Sunday or Monday. If mode is omitted, the value of the default_week_format system independent variable is 0.

# Value of the Mode parameter in the WEEK Function

Mode	The first day of a week	Range	Week 1 is the first Week
0	Sunday	0 ~ 53	One Sunday in the year
1	Monday	0 ~ 53	More than 3 days in the current year
2	Sunday	1 ~ 53	One Sunday in the year
3	Monday	1 ~ 53	More than 3 days in the current year
4	Sunday	0 ~ 53	More than 3 days in the current year
5	Monday	0 ~ 53	One Monday in the year
6	Sunday	1 ~ 53	More than 3 days in the current year
7	Monday	1 ~ 53	One Monday in the year

 

# WEEKOFYEAR (d): calculates the number of weeks in a year where d is located, ranging from 1 ~ 53. Equivalent to WEEK (d, 3)

Select week (NOW (), WEEK (NOW (), 0), WEEK (NOW (), 1), WEEKOFYEAR (NOW (), WEEK (NOW (), 3 );

 

 

6. Obtain the number of days function

# DAYOFYEAR (d): returns the day of the year from 1 ~ 366

# DAYOFMONTH (d): returns the day in a month, ranging from 1 ~ 31

Select dayofyear (NOW (), DAYOFMONTH (NOW ());

 

 

 

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

# YEAR (date): returns the YEAR corresponding to date. The value range is 1970 ~ 2069

# QUARTER (date): returns the QUARTER of the year corresponding to date, range: 1 ~ 4

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

# MONTHNAME (date): The function returns the full name of the month corresponding to the date, such as March.

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

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

# SECOND (time): returns the number of seconds corresponding to time, ranging from 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. Obtain the specified date value function EXTRACT (type FROM date)

# Used to extract part of values from date based on type

SELECT

EXTRACT (year from now () as year,

EXTRACT (quarter from now () as quarter,

EXTRACT (month from now () as month,

EXTRACT (day from now () 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 second conversion functions TIME_TO_SEC (time)

# Conversion formula:

# TIME_TO_SEC (time): returns the time parameter converted to seconds.

# SEC_TO_TIME (seconds): returns the seconds parameter value converted to hour, minute, and second.

SELECT

CURTIME () as time,

TIME_TO_SEC (CURTIME () as second,

SEC_TO_TIME (TIME_TO_SEC (CURTIME () as time;

 

... To be continued

This article is not completed yet. It is equivalent to the time relationship and will be continued later. Thank you for your attention!

If you have better suggestions, please mail to my private mailbox: david.louis.tian@outllook.com

Copyright @: This is my original article. Please mark the source for reprinting. Thank you!

 

 

 

 

What is the function for MySQL to get the current time? What is the function for formatting the date?

Use now () to obtain the current time.
Format the time in the database using DATE_FORMA T (date, format ).
Format the date or date and Time Value Based on the format string to return the result string.

You can use DATE_FORMAT () to format the DATE or DATETIME value to obtain the expected format. Format the date value based on the format string:

% S, % s two-digit second (,..., 59)

% I two-digit score (,..., 59)

% H two-digit hour, 24 hours (,..., 23)

% H, % I two-digit hour, 12 hours (,..., 12)

% K, 24 hours (0, 1,..., 23)

% L hour in numeric format (1, 2,..., 12)

% T 24-hour time format (h: m: s)

% R 12-hour time format (hh: mm: ss AM or hh: mm: ss PM)

% P AM or P M

% W name of each day in a week (S u n d a y, Monday,..., Saturday)

% A abbreviation of the name of each day in a week (Sun, Mon,..., Sat)

% D indicates the number of days in a month (00, 01,..., 31)

% E indicates the number of days in a month (1, 2,..., 31)

% D indicates the number of days in a month (1st, 2nd, 3rd ,...)

% W represents the number of days in a week (0 = S u n d a y, 1 = Monday,..., 6 = Saturday)

% J represents the number of days in a year in three digits (001,002,..., 366)

% U Week (0, 1, 52), where Sunday is the first day of the week

% U Week (0, 1, 52), where Monday is the first day of the week

% M month name (J a n u a r y, February,..., December)

% B abbreviated month name (J a n u a r y, February,..., December)

% M double-digit month (01, 02,..., 12)

% C indicates the month (1, 2,..., 12)

The year in the format of % Y.

The year in double-digit % y format.

% Direct value "%"

Select date_format (Date Field, '% Y-% m-% D') as 'date' from test

This is basically the case. It will be a good performance soon ......

Question about mysql date functions?

1. Try to enclose the following with quotation marks (')
2. Check the returned data type of the FROM_UNIXTIME function and convert to the corresponding type.
The addTime field is obviously of the datetime type. In the database, it is actually saved as a second value (it seems that it has been from 1970), FROM_UNIXTIME (addTime, '% Y-% m-% D') The datetime is output in year, month, and day format. However, the data type returned by FROM_UNIXTIME depends on the function definition.