使用mysql日期与时间函数轻易搞定日期与时间逻辑

mysql查询当天的数据:

    select* from procurement where date(createDate)=curdate()  order by refreshDatetime   desc ;

mysql查询过去几天的数据:

    select * from procurement 
        where DATEDIFF(NOW(),createDate)<6 and DATEDIFF(NOW(),createDate)>=0  where
        order by refreshDatetime   desc

mysql函数无非是三类：获得时间。设置时间。格式化时间。

curdate函数用来返回当前的日期：

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2014-09-11 |
+------------+
1 row in set

mysql>

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2014-09-11 |
+------------+
1 row in set

mysql> select curdate()+0;
+-------------+
| curdate()+0 |
+-------------+
|    20140911 |
+-------------+
1 row in set

mysql> 
curtime函数返回当前时间。


mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:26:59  |
+-----------+
1 row in set

mysql> select curtime()+0;
+-------------+
| curtime()+0 |
+-------------+
|      162721 |
+-------------+
1 row in set

mysql>

类似的，

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2014-09-11 16:28:49 |
+---------------------+
1 row in set

mysql> select current_timestamp()+0;
+-----------------------+
| current_timestamp()+0 |
+-----------------------+
|        20140911162915 |
+-----------------------+
1 row in set

mysql>

其中：
CURRENT_TIMESTAMP和CURRENT_TIMESTAMP()是NOW()的同义词

date()用于从一个时间表达式中间提取出日期，它会判断表达式是否正确，如果不正确，将会得到空：

mysql> select date('2010-11-10');
+--------------------+
| date('2010-11-10') |
+--------------------+
| 2010-11-10         |
+--------------------+
1 row in set

mysql> select date('2010-11-01 12:03:07');
+-----------------------------+
| date('2010-11-01 12:03:07') |
+-----------------------------+
| 2010-11-01                  |
+-----------------------------+
1 row in set

mysql> select date('2010-11-71 12:03:07');
+-----------------------------+
| date('2010-11-71 12:03:07') |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set

mysql> select date('2010-11-71 12:93:07');
+-----------------------------+
| date('2010-11-71 12:93:07') |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set

mysql> 

date函数获得日期与分隔符无关
mysql> select date('20101101');
+------------------+
| date('20101101') |
+------------------+
| 2010-11-01       |
+------------------+
1 row in set

mysql> select date('2010111111');
+--------------------+
| date('2010111111') |
+--------------------+
| 2020-10-11         |
+--------------------+
1 row in set

mysql> select date('2010x11x1111'); //出错的原因是因为日是1111，与分隔符无关
+----------------------+
| date('2010x11x1111') |
+----------------------+
| NULL                 |
+----------------------+
1 row in set

mysql> select date('2010*11*11 11');
+-----------------------+
| date('2010*11*11 11') |
+-----------------------+
| 2010-11-11            |
+-----------------------+
1 row in set

mysql> 

得到时间差的函数：

mysql> select datediff('1995-11-09',now());
+------------------------------+
| datediff('1995-11-09',now()) |
+------------------------------+
|                        -6881 |
+------------------------------+
1 row in set

mysql> select datediff(now(),'1995-11-09');
+------------------------------+
| datediff(now(),'1995-11-09') |
+------------------------------+
|                         6881 |
+------------------------------+
1 row in set

mysql>

现在有一个问题，比如需要知道最近三十天登录的用户，该怎么做：
一种方法是与PHP结合

<?php
$time=date("Y-m-d",strtotime("-30 day"));
echo $time;
?>

如果精确到秒的话

$time=date("Y-m-d H:i:s",strtotime("-30 day"));

30天前就是-30.
注意：日期是可以直接比较大小的：

select userId from travel_plan where startDate>'2010-10-10';

date_sub函数，返回一个过去的时间差。

mysql> select date_sub(now(),interval 30 day);
+---------------------------------+
| date_sub(now(),interval 30 day) |
+---------------------------------+
| 2014-08-12 17:17:29             |
+---------------------------------+
1 row in set

mysql> select date_sub('2010-11-01',interval 30 day);
+----------------------------------------+
| date_sub('2010-11-01',interval 30 day) |
+----------------------------------------+
| 2010-10-02                             |
+----------------------------------------+
1 row in set

mysql> 

date_add返回一个未来的时间差：
mysql> select date_add('2010-11-01',interval 30 day);
+----------------------------------------+
| date_add('2010-11-01',interval 30 day) |
+----------------------------------------+
| 2010-12-01                             |
+----------------------------------------+
1 row in set

mysql> select date_add(now(),interval 30 day);
+---------------------------------+
| date_add(now(),interval 30 day) |
+---------------------------------+
| 2014-10-11 17:22:46             |
+---------------------------------+
1 row in set

mysql>

我是如何运用这些的
mysql有一个问题，就是比如select now 得到的是系统时间，是根据操作系统当前时间来确定的。
但是使用utc函数的时候，就差8个小时，是根据utc0来确定的，就不再依据系统当前的时区了。