MySQL in time and date function detailed explanation (1/3)

First, MySQL get the current date time function

1.1 Get Current date + time (date + times) function: Now ()

The code is as follows	Copy Code
Mysql> Select Now (); +– ——————-+ | Now () | +– ——————-+ | 2008-08-08 22:20:46 | +– ——————-+

In addition to the now () function to get the current date time, MySQL also has the following function:

The code is as follows	Copy Code
Current_timestamp () , Current_timestamp , LocalTime () , localtime , localtimestamp– (v4.0.6) , Localtimestamp () – (v4.0.6)

These date-time functions are equivalent to now (). Given that the now () function is short and easy to remember, it is always recommended to use now () instead of the functions listed above.

1.2 Get current date + time (date + times) function: Sysdate ()

The Sysdate () Date time function is similar to now (), except that the now () is obtained at the start value, and sysdate () gets the values dynamically when the function is executed. Take a look at the example below to see:

code is as follows

copy code

mysql> Select Now (), Sleep (3), now (), +– ——————-+ ———-+ ——————— + | Now () | sleep (3) | Now () | +– ——————-+ ———-+ ——————— + | 2008-08-08 22:28:21 | 0 | 20 08-08-08 22:28:21 | +– ——————-+ ———-+ ——————— + Mysql> Select Sysdate (), Sleep (3), sysdate (); +– ——————-+ ———-+ ——————— + | sysdate () | sleep (3) | sysdate () | +– ——————-+ ———-+ ——————— + | 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 | +– ——————-+ ———-+ ——————— +

As you can see, the time value of the now () function two times is the same, although it is 3 seconds Midway, the sysdate () function gets a difference of 3 seconds. This description of Sysdate () is described in the MySQL Manual as follows: Return to which the function executes.

Sysdate () Date time functions are rarely used in general.

2. Get the current date function: Curdate ()

The code is as follows	Copy Code
Mysql> select Curdate (); +– ———-+ | Curdate () | +– ———-+ | 2008-08-08 | +– ———-+

Of these, the following two date functions are equivalent to Curdate ():

Current_date ()
, current_date
3. Get current Time function: Curtime ()

The code is as follows	Copy Code
Mysql> select Curtime (); +– ——— + | Curtime () | +– ——— + | 22:41:30 | +– ——— +

Where the following two time functions are equivalent to Curtime ():

Current_time ()
, Current_time
4. Obtain the current UTC date-time function: Utc_date (), Utc_time (), Utc_timestamp ()

The code is as follows	Copy Code
Mysql> Select Utc_timestamp (), Utc_date (), Utc_time (), now () +– ——————-+ ———— + ———— + ——————— + | Utc_timestamp () | Utc_date () | Utc_time () | Now () | +– ——————-+ ———— + ———— + ——————— + | 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 | +– ——————-+ ———— + ———— + ——————— +

Since our country is located in the East Eight time zone, local time = UTC time + 8 hours. UTC time is useful when the business involves multiple countries and regions.
Second, MySQL date time Extract (selection) function.

1. Select various parts of datetime: Date, time, year, quarter, month, day, hour, minute, second, microsecond

The code is as follows	Copy Code
Set @dt = ' 2008-09-10 07:15:30.123456 '; Select Date (@dt); –2008-09-10 Select time (@dt); –07:15:30.123456 Select year (@dt); –2008 Select quarter (@dt); –3 Select Month (@dt); –9 Select Week (@dt); –36 Select Day (@dt); –10 Select Hour (@dt); –7 Select minute (@dt); –15 Select second (@dt); –30 Select Microsecond (@dt); –123456

2. MySQL Extract () function, which can achieve similar functions above:

The code is as follows

Copy Code

Set @dt = ' 2008-09-10 07:15:30.123456 '; Select Extract (year from @dt); –2008 Select Extract (quarter from @dt); –3 Select Extract (month from @dt); –9 Select Extract (week from @dt); –36 Select Extract (Day from @dt); –10 Select Extract (hour from @dt); –7 Select extract (minute from @dt); –15 Select Extract (second from @dt); –30 Select Extract (microsecond from @dt); –123456 Select Extract (Year_month from @dt); –200809 Select Extract (Day_hour from @dt); –1007 Select Extract (Day_minute from @dt); –100715 Select Extract (Day_second from @dt); –10071530 Select Extract (Day_microsecond from @dt); –10071530123456 Select Extract (Hour_minute from @dt); –715 Select Extract (Hour_second from @dt); –71530 Select Extract (Hour_microsecond from @dt); –71530123456 Select Extract (Minute_second from @dt); –1530 Select Extract (Minute_microsecond from @dt); –1530123456 Select Extract (Second_microsecond from @dt); –30123456

The MySQL Extract () function has a full reprehensible function except for the functionality of date () and time (). And also has the choice ' day_microsecond ' and so on function. Note that this is not a selection of day and microsecond, but rather a selection of the days section of the date to the Microsecond section. It's tough enough!

The only bad thing about the MySQL Extract () function is that you need to knock on the keyboard a few more times.

3. MySQL dayof ... Functions: DayOfWeek (), DayOfMonth (), DayOfYear ()

Returns the position of the date parameter, in a week, month, and year, respectively.

The code is as follows	Copy Code
Set @dt = ' 2008-08-08 '; Select DayOfWeek (@dt); –6 Select DayOfMonth (@dt); –8 Select DayOfYear (@dt); –221

Date ' 2008-08-08′ is the 6th day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday); 8th day of January; No. 221 Day of the year.

4. MySQL Week ... Functions: Week (), WeekOfYear (), DayOfWeek (), Weekday (), Yearweek ()

The code is as follows	Copy Code
Set @dt = ' 2008-08-08 '; Select Week (@dt); –31 Select Week (@dt, 3); –32 Select WeekOfYear (@dt); –32 Select DayOfWeek (@dt); –6 Select Weekday (@dt); –4 Select Yearweek (@dt); –200831

MySQL week () function, can have two parameters, specific to see the manual. The WeekOfYear () and week () are calculated as "one day" in the week ordinal of the year. WeekOfYear (@dt) is equivalent to week (@dt, 3).

The MySQL Weekday () function is similar to DayOfWeek () and returns the location of the "Day" in the week. The difference is in reference to the standard, weekday: (0 = Monday, 1 = Tuesday, ..., 6 = Sunday); DayOfWeek: (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

The MySQL Yearweek () function returns year (2008) + Week position (31).

5. MySQL returns the week and month name function: Dayname (), MonthName ()

The code is as follows	Copy Code
Set @dt = ' 2008-08-08 '; Select Dayname (@dt); –friday Select MonthName (@dt); –august

Think, how to return the name of Chinese?

6. MySQL Last_day () function: Returns the last day of the month.

The code is as follows	Copy Code
Select Last_day (' 2008-02-01 '); –2008-02-29 Select Last_day (' 2008-08-08 '); –2008-08-31

The MySQL Last_day () function is useful, such as I want to get the number of days in the current month, which can be calculated as follows:

The code is as follows	Copy Code
Mysql> Select Now (), Day (Last_day (today)) as days; +– ——————-+--+ | Now () | Days | +– ——————-+--+ | 2008-08-09 11:45:45 | 31 | +– ——————-+--+

Home 1 2 3 last