MySQL Day Date function summary (1/3)

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

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:

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:

Mysql> Select Now (), Sleep (3), now ();

+---------------------+----------+---------------------+
| Now ()                 | Sleep (3) | Now ()                |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 |         0 | 2008-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 |
+---------------------+----------+---------------------+ you can see that although 3 seconds of sleep Midway, the time value of the now () function is the same for two times; Sysdate () The time value of the function is 3 seconds apart. 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 ()

Mysql> select Curdate ();

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

Current_date ()
, Current_date3. Get current time function: Curtime ()

Mysql> select Curtime ();

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

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

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 |
+---------------------+------------+------------+---------------------+ because our country is located in the East eight time zone, so 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

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); --1234562. The MySQL Extract () function allows you to implement similar functions above:

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); --123456select 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); --30123456MySQL Extract () function except that there is no date (), time (), other functions reprehensible with full. 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.

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.

Home 1 2 3 last