MySQL database Date and Time Functions

The following is a summary of the Practical Application Experience of MySQL database date functions and time functions MySQL 5.x, mySQL date functions and time functions appear frequently in practical applications. The following article describes these two functions in detail.

MySQL database obtains the current date and time function:

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

 
 

  
  
mysql> select now();  
  
  
+---------------------+  
  
  
| now() |  
  
  
+---------------------+  
  
  
| 2008-08-08 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 datetime 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) 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() |  
  
  
+---------------------+----------+---------------------+  
  
  
| 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 |  
  
  
+---------------------+----------+---------------------+  
 
 

We can see that although sleep lasts for 3 seconds, the time values of the now () function are the same. The time values obtained by the sysdate () function differ by 3 seconds. In the MySQL database Manual, sysdate () is described as follows: Return the time at which the function executes.

Sysdate () Date and time functions are rarely used.

2. Get 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) function: curtime ()

 
 

  
  
mysql> select curtime();  
  
  
+-----------+  
  
  
| curtime() |  
  
  
+-----------+  
  
  
| 22:41:30 |  
 
 

+ ----------- + The following two time functions are equivalent to curtime ():

Current_time ()

, Current_time4. Get the current UTC date and 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 China is located in the eight eastern time zone, the local time = UTC time + 8 hours. UTC time is very useful when your business involves multiple countries and regions.

Ii. MySQL database Date and Time Extract selection) function.

1. Select each part of the date and time: 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. MySQL Extract () function, which can implement similar functions:

 
 

  
  
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);  
 
 

30123456 apart from date () and time (), 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 above content is an introduction to the MySQL database's date function and time function MySQL 5.X). I hope you will have some gains.