mysql 查询语句

查询一段时间到另一段时间的数据

$from=$_POST['from'];
$to=$_POST['to'];
$query_xiangmu ="SELECT * FROM yichang WHERE fabushijian between '".$from."' AND '".$to."'";
$xiangmu = mysql_query($query_xiangmu, $shengchan) or die(mysql_error());
$row_xiangmu = mysql_fetch_assoc($xiangmu);
$totalRows_xiangmu = mysql_num_rows($xiangmu);

如果是时间戳的话可以这样

$d1 = strtotime('2015-01-01');
$d2 = strtotime('2015-02-01');
$sql = "select * from tbl_name where field between $d1 and $d2"

 如果不是时间戳 该如何处理

$sql = "select * from e_user where test_time < '2015-12-1'"; 
$sql = "select * from e_user where test_time between '2015-11-1' and '2015-12-1'";

 

话说有一文章表article,存储文章的添加文章的时间是add_time字段,该字段为int(5)类型的,现需要查询今天添加的文章总数并且按照时间从大到小排序,则查询语句如下:

select * from `article` where date_format(from_UNIXTIME(`add_time`),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d');

或者可以这样

select * from `article` where to_days(date_format(from_UNIXTIME(`add_time`),'%Y-%m-%d')) = to_days(now());

假设以上表的add_time字段的存储类型是DATETIME类型或者TIMESTAMP类型,则查询语句也可按如下写法:

查询今天的信息记录:

 

select * from `article` where to_days(`add_time`) = to_days(now());

 

查询昨天的信息记录:

select * from `article` where to_days(now()) – to_days(`add_time`) <= 1;

查询近7天的信息记录:

select * from `article` where date_sub(curdate(), INTERVAL 7 DAY) <= date(`add_time`);

查询近30天的信息记录:

select * from `article` where date_sub(curdate(), INTERVAL 30 DAY) <= date(`add_time`);

查询本月的信息记录:

select * from `article` where date_format(`add_time`, ‘%Y%m') = date_format(curdate() , ‘%Y%m');

查询上一月的信息记录:

select * from `article` where period_diff(date_format(now() , ‘%Y%m') , date_format(`add_time`, ‘%Y%m')) =1;

 

 

 

查询某到时间到现在的数据

posted on 2015-11-25 12:03  小V_chen  阅读(172)  评论(0编辑  收藏  举报

导航