Correct usage of the MySQL Date function

The following articles mainly introduce the practical application of the MySQL Date function, including how to obtain the specific operation of the current time and the specific application of the Unix time, the following describes the actual content before and after the time, the time interval, and the time conversion.

MySQL Date function 1. Get the current time

 
 

  
  
MySQL> select current_timestamp();  
  
  
+---------------------+  
  
  
| current_timestamp() |  
  
  
+---------------------+  
  
  
| 2010-01-18 21:24:37 |  
  
  
+---------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select current_date();  
  
  
+----------------+  
  
  
| current_date() |  
  
  
+----------------+  
  
  
| 2010-01-18 |  
  
  
+----------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select current_time();  
  
  
+----------------+  
  
  
| current_time() |  
  
  
+----------------+  
  
  
| 21:24:46 |  
  
  
+----------------+  
  
  
1 row in set (0.00 sec)  
 
 

MySQL Date function 2. Unix time

 
 

  
  
MySQL> select unix_timestamp();  
  
  
+------------------+  
  
  
| unix_timestamp() |  
  
  
+------------------+  
  
  
| 1263821184 |  
  
  
+------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select from_unixtime(1263821182);  
  
  
+---------------------------+  
  
  
| from_unixtime(1263821182) |  
  
  
+---------------------------+  
  
  
| 2010-01-18 21:26:22 |  
  
  
+---------------------------+  
  
  
1 row in set (0.00 sec)  
 
 

MySQL Date function 3. Before and after the time

 
 

  
  
MySQL> select date_add(current_timestamp, interval 1 day);  
  
  
+---------------------------------------------+  
  
  
| date_add(current_timestamp, interval 1 day) |  
  
  
+---------------------------------------------+  
  
  
| 2010-01-19 21:27:53 |  
  
  
+---------------------------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select date_add(current_time, interval 1 day);  
  
  
+----------------------------------------+  
  
  
| date_add(current_time, interval 1 day) |  
  
  
+----------------------------------------+  
  
  
| NULL |  
  
  
+----------------------------------------+  
  
  
1 row in set, 1 warning (0.00 sec)  
  
  
MySQL> select date_add(current_date, interval 1 day);  
  
  
+----------------------------------------+  
  
  
| date_add(current_date, interval 1 day) |  
  
  
+----------------------------------------+  
  
  
| 2010-01-19 |  
  
  
+----------------------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select date_sub(current_timestamp, interval 1 day);  
  
  
+---------------------------------------------+  
  
  
| date_sub(current_timestamp, interval 1 day) |  
  
  
+---------------------------------------------+  
  
  
| 2010-01-17 21:28:41 |  
  
  
+---------------------------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select date_sub(current_date, interval 1 day);  
  
  
+----------------------------------------+  
  
  
| date_sub(current_date, interval 1 day) |  
  
  
+----------------------------------------+  
  
  
| 2010-01-17 |  
  
  
+----------------------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select date_sub(current_time, interval 1 day);  
  
  
+----------------------------------------+  
  
  
| date_sub(current_time, interval 1 day) |  
  
  
+----------------------------------------+  
  
  
| NULL |  
  
  
+----------------------------------------+  
  
  
1 row in set, 1 warning (0.00 sec)  
 
 
 

MySQL Date function 4. Time Interval

 
 

  
  
MySQL> select datediff('2010-01-18','2010-01-17');  
  
  
+-------------------------------------+  
  
  
| datediff('2010-01-18','2010-01-17') |  
  
  
+-------------------------------------+  
  
  
| 1 |  
  
  
+-------------------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select timediff('2010-01-18 12:00','2010-01-17 11:00');  
  
  
+-------------------------------------------------+  
  
  
| timediff('2010-01-18 12:00','2010-01-17 11:00') |  
  
  
+-------------------------------------------------+  
  
  
| 25:00:00 |  
  
  
+-------------------------------------------------+  
  
  
1 row in set (0.00 sec)  
 
 

MySQL Date function 5. Time Conversion

 
 

  
  
MySQL> select time_to_sec('25:00:00');  
  
  
+-------------------------+  
  
  
| time_to_sec('25:00:00') |  
  
  
+-------------------------+  
  
  
| 90000 |  
  
  
+-------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select sec_to_time(90000);  
  
  
+--------------------+  
  
  
| sec_to_time(90000) |  
  
  
+--------------------+  
  
  
| 25:00:00 |  
  
  
+--------------------+  
  
  
1 row in set (0.00 sec)   
 
 

The above content is an introduction to the MySQL Date function. I hope you will get some benefits.