Summary of mysql daytime date functions (1/3)

Mysql provides a large number of time and date functions, as well as various operations of the date and time functions, such as addition and subtraction operations. If you need them, you can refer to them.

1.1 obtain the current date + time (date + time) function: now ()

Mysql> select now ();

+ --------------------- +
| Now () |
+ --------------------- +
| 22:20:46 |
+ --------------------- + In addition to the now () function, MySQL also has the following functions:

Current_timestamp ()
, Current_timestamp
, Localtime ()
, Localtime
, Localtimestamp -- (v4.0.6)
, Localtimestamp () -- (v4.0.6) These date and time functions are equivalent to now (). Considering that the now () function is short and easy to remember, we recommend that you always use now () to replace the functions listed above.

1.2 obtain the current date + time (date + time) function: sysdate ()

The sysdate () Date and time function is similar to now (). The difference is that now () is obtained at the execution start time, And sysdate () dynamically obtains the value during function execution. You can see the following example:

Mysql> select now (), sleep (3), now ();

+ --------------------- + ---------- + --------------------- +
| Now () | sleep (3) | now () |
+ --------------------- + ---------- + --------------------- +
| 22:28:21 | 0 | 22:28:21 |
+ --------------------- + ---------- + ------------------- + Mysql> select sysdate (), sleep (3), sysdate ();

+ --------------------- + ---------- + --------------------- +
| Sysdate () | sleep (3) | sysdate () |
+ --------------------- + ---------- + --------------------- +
| 22:28:41 | 0 | 22:28:44 |
+ --------------------- + ---------- + --------------------- + You can see that although sleep is 3 seconds in the middle, the time value of the now () function is the same for two times; sysdate () the time difference between the two functions is 3 seconds. In MySQL Manual, sysdate () is described as follows: Return the time at which the function executes.

Sysdate () Date and time functions are rarely used.

2. Obtain the current date function: curdate ()

Mysql> select curdate ();

+ ------------ +
| Curdate () |
+ ------------ +
| 2008-08-08 |
+ ------------ + The following two date functions are equivalent to curdate ():

Current_date ()
, Current_date3. Get the current time (time) function: curtime ()

Mysql> select curtime ();

+ ----------- +
| Curtime () |
+ ----------- +
| 22:41:30 |
+ ----------- + The following two time functions are equivalent to curtime ():

Current_time ()
, Current_time4. Obtain the current UTC date and time functions: utc_date (), utc_time (), utc_timestamp ()

Mysql> select utc_timestamp (), utc_date (), utc_time (), now ()

+ --------------------- + ------------ + --------------------- +
| Utc_timestamp () | utc_date () | utc_time () | now () |
+ --------------------- + ------------ + --------------------- +
| 14:47:11 | 14:47:11 | 22:47:11 |
+ --------------------- + ------------ + --------------------- + Because our country is located in the east eight time zone, the local time = UTC time + 8 hours. UTC time is very useful when your business involves multiple countries and regions.

Ii. MySQL Date and Time Extract (select) function.
1. Select each part of the date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond

Set @ dt = '2017-09-10 07:15:30. 123 ';

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); -- 1234562. MySQL Extract () function, which can implement similar functions:

Set @ dt = '2017-09-10 07:15:30. 123 ';

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 apart from date () and time () functions, the MySQL Extract () function must be fully functional. You can also select 'day _ microsecond. Note that we will not only select day and microsecond, but choose from day of the date to microsecond. Powerful enough!

The only bad thing about the MySQL Extract () function is that you need to press the keyboard several more times.

3. MySQL dayof... function: dayofweek (), dayofmonth (), dayofyear ()

Return the date parameters in the range of one week, one month, and one year.

Set @ dt = '2017-08-08 ';

Select dayofweek (@ dt); -- 6
Select dayofmonth (@ dt); -- 8
Select dayofyear (@ dt); -- the 221 date '2017-08-08 'is the 2008 day of the week (1 = Sunday, 2 = Monday ,..., 7 = Saturday); January days in 8th; 221st days in a year.

1 2 3