mysql查询最近12月日期_mysql查询今天、昨天、本周、本月、上一月 、今年、某年、当年12月每月时间、当年每月每周时间...

今天

select * from 表名 where to_days(时间字段名) = to_days(now());

昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

本周

SELECT * FROM  表名 WHERE YEARWEEK( date_format(  时间字段名,'%Y-%m-%d' ) ) = YEARWEEK( now( ) ) ;

本月

SELECT * FROM  表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

SELECT * FROM  表名 WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m') =1

今年

SELECT * FROM 表名 WHERE YEAR(  时间字段名 ) = YEAR( NOW( ) )

某年

SELECT * FROM 表名 WHERE YEAR(  时间字段名 ) = YEAR( 2008-12-29 16:25:46 )

某年某月

SELECT * FROM 表名 WHERE date_format(FROM_UNIXTIME(time), '%Y%m ')= '200705’

当年12月每月时间

for($k=1;$k<13;$k++){

$cur_y = date('Y',time());//当天年份

$cur_f = $cur_y."-".$k. '-1';//本月首日

$datetime=$cur_y."年".$k."月";

$first = strtotime($cur_f);//时间戳最小值，本月第一天时间戳

//下月首日

if($k>=12){

$cur_n = ($cur_y+1) . '-1-1';

}else{

$cur_n = $cur_y . '-' . ($k+1) . '-1';

}

$last = strtotime($cur_n);//时间戳最大值，下个月第一天时间戳

$between ="时间字段 between ".$first." and ".$last;

}

今年某月每周具体时间--------------------------------------------------------------------------------------------

global $month_list;

$month_list = array(

1 => "January ",

2 => "February",

3 => "Marcy",

4 => "April",

5 => "May",

6 => "June",

7 => "July",

8 => "August",

9 => "September",

10 => "October",

11 => " November",

12 => "December"

);

function getdaysbymonth($month = 1) {

$days_31 = array(1, 3, 5, 7, 8, 10, 12);

//闰年的判断定义能被4整除且又能不能被100整除 是闰年能直接被400整除也是闰年

if ($month == 2) {

//判断当前是不是闰年

$year = date("Y");

$last = substr($month, -2);

if ($last == "00") { //是整百的倍数就看能否被400整除

if ($year % 400 == 0) {

return 29;

}

}

//看能不能被4整除并且又不能被100整除

if ($year % 4 == 0 && $year % 100 != 0) {

return 29;

}

return 28;

}

if (in_array($month, $days_31)) {

return 31;

}

return 30;

}

function gettimelist($tongjitype = 'month', $month = 1) {

global $month_list;

$time_begin_last = " 00:00:00"; //开始时间后缀

$time_end_last = " 23:59:59"; //结束时间后缀

$year = date("Y");

$time_list = array();

switch ($tongjitype) {

case "month":

for ($i = 1; $i <= 12; $i++) {

$days = getdaysbymonth($i);

$istr = $i < 10 ? "0" . $i : $i;

$time_list[] = array("begin" => $year . "-" . $istr . "-01" . $time_begin_last, "end" => $year . "-" . $istr . "-" . $days . $time_end_last, "period" => strtotime($year . "-" . $istr . "-" . $days . $time_end_last) - strtotime($year . "-" .

$istr . "-01" . $time_begin_last));

}

return $time_list;

break;

case "week":

$days = getdaysbymonth($month);

$monthstr = $month < 10 ? "0" . $month : $month;

//先查询当月第一天是周几

$begin_week = date("w", strtotime($year . "-" . $monthstr . "-" . "01"));

$last_week = date("w", strtotime($year . "-" . $monthstr . "-" . "$days"));

$month_en = $month_list[$month];

$last_monday_date = $last_sunday_date = NUll;

if ($begin_week == 1) { //当月1号是周一

$first_monday = $year . "-" . $monthstr . "-" . "01";

$last_monday_date = $first_monday;

} else {

//获取上周一

$last_monday_date = date("Y-m-d", strtotime("+1 day  1 $month_en  $year  last Sunday"));

$first_monday = date("Y-m-d", strtotime("+8 day  1 $month_en  $year  last Sunday"));

};

if ($last_week == 0) {

$last_sunday_date = $year . "-" . $monthstr . "-" . $days;

} else {

$add_day = 7 - $last_week;

$last_sunday_date = date("Y-m-d", strtotime("+$add_day day $days $month_en  $year last Sunday"));

}

//计算最后一个周末和第一个周一之间有几个周末

$strchazhi = strtotime($last_sunday_date) - strtotime($last_monday_date);

$weekcha = ceil($strchazhi / (3600 * 24 * 7));

for ($i = 1; $i <= $weekcha; $i++) {

$begin = date("Y-m-d", strtotime($last_monday_date) + ($i - 1) * 3600 * 24 * 7) . $time_begin_last;

$end = date("Y-m-d", strtotime($begin) + 3600 * 24 * 6) . $time_end_last;

$time_list[] = array("begin" => $begin, "end" => $end, "period" => strtotime($end) - strtotime($begin));

}

return $time_list;

break;

default:

$days = getdaysbymonth($month);

$monthstr = $month < 10 ? "0" . $month : $month;

for ($i = 1; $i <= $days; $i++) {

$istr = $i < 10 ? "0" . $i : $i;

$time_list[] = array("begin" => $year . "-" . $monthstr . "-" . $istr . $time_begin_last, "end" => $year . "-" . $monthstr . "-" . $istr . $time_end_last, "period" => strtotime($year . "-" . $monthstr . "-" . $istr . $time_end_last) - strtotime($year

. "-" . $monthstr . "-" . $istr . $time_begin_last));

}

return $time_list;

break;

}

}

$month=gettimelist('week',$Data["month"]);//传入今年几月

for($p=0;$p

$datetime=date("Y-m-d",  strtotime($month[$p]['begin']))."->".date("Y-m-d",  strtotime($month[$p]['end']));

array_push($date, $datetime);//每周时间数组

$between ="clock between ".strtotime($month[$p]['begin'])." and ".strtotime($month[$p]['end']);

$row=DBfetch(DBselect("SELEC * FROM 表名 WHERE ".$between." order by clock DESC"));每一周数据查询

}