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<count($month);$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"));每一周数据查询
}