mysql中日期操作

1 获取当前时间 now()

 select now();
+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

 

2 日期转换函数、时间转换函数

2.1 MySQL Date/Time to Str，函数：date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');
+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301 |
+----------------------------------------------------+

2.2 MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

 

3 MySQL 为日期增加一个时间间隔：date_add()

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- add 1 day
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); -- add 1 hour
SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE); -- ...
SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND);
SELECT DATE_ADD(NOW(), INTERVAL 1 MICROSECOND);
SELECT DATE_ADD(NOW(), INTERVAL 1 WEEK);
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);
SELECT DATE_ADD(NOW(), INTERVAL 1 QUARTER);
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

 

4 date_add() 实现 addtime() 功能示例：

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

 

5 MySQL 为日期减去一个时间间隔：date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+----------------------------------------------------------------+