如何在下一年的周结束时获取上周的年份记录例如,我必须记录下接下来的四周,第53周将于2016年结束。
week_no week_start - week_end
==================================
week-53 (2015-12-28 to 2016-01-03)
week-52 (2015-12-21 to 2015-12-27)
week-51 (2015-12-14 to 2015-12-20)
week-50 (2015-12-07 to 2015-12-13)
下面是我的查询
function getLastFourWeekUser($table=FALSE){
if($table==TRUE){
$this->db->from($table);
}
else{
$this->db->from('users');
}
$endDate=date('Y-m-d', strtotime('-3 week'));
$dto = new DateTime();
$year=date('Y',strtotime($endDate));
$week=date('W',strtotime($endDate));
$week_start = $dto->setISODate($year,$week)->format('Y-m-d');
$to = date("Y-m-d", strtotime(date('o')-date('W')-7));
$query = $this->db->select('count(*) as count,DATE_FORMAT(created_at,"%u-%Y") as week',false)
->where('created_at >=',$week_start)
->where('created_at <=',$to)
->group_by('week')
->order_by('week')
->get();
}
我写了一个查询,只会给我今年的记录,即直到2015-12-31只有但我也想要1月1日到3日的记录。因为第53周将在2016-01-03结束。
有人能帮我解决这个问题吗。
试试这个:)
SELECT
DATE_FORMAT(DATE_FORMAT(STR_TO_DATE(CONCAT(DATE_FORMAT(created_at, '%x%v'), 'Monday'), '%x%v%W'), '%Y-%m-%d'), "%u-%Y") AS week,
DATE_FORMAT(STR_TO_DATE(CONCAT(DATE_FORMAT(created_at, '%x%v'), 'Monday'), '%x%v%W'), '%Y-%m-%d') AS week_start,
DATE_FORMAT(STR_TO_DATE(CONCAT(DATE_FORMAT(created_at, '%x%v'), 'Sunday'), '%x%v%W'), '%Y-%m-%d') AS week_end,
COUNT(*) as count
WHERE created_at >='$week_start' AND created_at <='$to'
GROUP BY week
ORDER BY week