假设有个表,怎样通过mysql查询过去N个星期,上个月,上个季度的记录?
网上搜索到的一个方法,主要就是先得到查询时间段的【开始时间,结束时间】,然后通过sql的条件找出这个时间段内的记录
表格:tbfee
id | fee_amount | fee_time
//调用方法
if($xid==1)
{
//上星期
list($start, $end) = lastNWeek(time(), 1);
$sql= "select * from tbfee where fee_time > '$start' and fee_time < '$end'";
}
else if ($xid==2)
{
//1个月
list($start, $end) = lastMonth(time());
$sql= "select * from tbfee where fee_time > '$start' and fee_time < '$end'";
}
else if ($xid==3)
{
//1个季度
list($start, $end) = lastQuarter(time());
$sql= "select * from tbfee where fee_time > '$start' and fee_time < '$end'";
}
需要调用的方法
<?php
/**
* 上XXX时间函数,用于计算上一周,上n周,上个月,上个季度的时间点。
*/
/**
* 获取上个季度的开始和结束日期
* @param int $ts 时间戳
* @return array 第一个元素为开始日期,第二个元素为结束日期
*/
function lastQuarter($ts) {
$ts = intval($ts);
$threeMonthAgo = mktime(0, 0, 0, date('n', $ts) - 3, 1, date('Y', $ts));
$year = date('Y', $threeMonthAgo);
$month = date('n', $threeMonthAgo);
$startMonth = intval(($month - 1)/3)*3 + 1; // 上季度开始月份
$endMonth = $startMonth + 2; // 上季度结束月份
return array(
date('Y-m-1', strtotime($year . "-{$startMonth}-1")),
date('Y-m-t', strtotime($year . "-{$endMonth}-1"))
);
}
/**
* 获取上个月的开始和结束
* @param int $ts 时间戳
* @return array 第一个元素为开始日期,第二个元素为结束日期
*/
function lastMonth($ts) {
$ts = intval($ts);
$oneMonthAgo = mktime(0, 0, 0, date('n', $ts) - 1, 1, date('Y', $ts));
$year = date('Y', $oneMonthAgo);
$month = date('n', $oneMonthAgo);
return array(
date('Y-m-1', strtotime($year . "-{$month}-1")),
date('Y-m-t', strtotime($year . "-{$month}-1"))
);
}
/**
* 获取上n周的开始和结束,每周从周一开始,周日结束日期
* @param int $ts 时间戳
* @param int $n 你懂的(前多少周)
* @param string $format 默认为'%Y-%m-%d',比如"2012-12-18"
* @return array 第一个元素为开始日期,第二个元素为结束日期
*/
function lastNWeek($ts, $n, $format = '%Y-%m-%d') {
$ts = intval($ts);
$n = abs(intval($n));
// 周一到周日分别为1-7
$dayOfWeek = date('w', $ts);
if (0 == $dayOfWeek)
{
$dayOfWeek = 7;
}
$lastNMonday = 7 * $n + $dayOfWeek - 1;
$lastNSunday = 7 * ($n - 1) + $dayOfWeek;
return array(
strftime($format, strtotime("-{$lastNMonday} day", $ts)),
strftime($format, strtotime("-{$lastNSunday} day", $ts))
);
}
//---------------------demo---------------------
/*
$now = strftime('%Y-%m-%d', time());
echo "Today: {$now}<br/>";
list($start, $end) = lastNWeek(time(), 1);
echo "Last 1 week: {$start} ~ {$end}<br/>";
list($start, $end) = lastNWeek(time(), 2);
echo "Last 2 week: {$start} ~ {$end}<br/>";
list($start, $end) = lastNWeek(time(), 3);
echo "Last 3 week: {$start} ~ {$end}<br/>";
list($start, $end) = lastMonth(time());
echo "Last month: {$start} ~ {$end}<br/>";
list($start, $end) = lastQuarter(time());
echo "Last quarter: {$start} ~ {$end}<br/>";
*/
?>