MySql Date函数

本是古典何须时尚 2021-12-13 19:09 406阅读 0赞

1、获取当前时间

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)  
  
2、Unix时间

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)  
  
3、时间前后

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)

4、时间间隔

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)

5、时间转换

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)

转载于:https://www.cnblogs.com/heroking2000/archive/2010/01/18/1651025.html