mysql 格林兰时间_使用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结合

$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来确定的，就不再依据系统当前的时区了。