我使用Symfony2进行了以下查询,我想将i.fechaInicio
(即date
字段Y-m-d
(2013-08-31))转换为当前周编号,以便从当前周获取结果。。。
$now = new 'DateTime();
$semana = $now->format('W'); // Week Number
$query = $this->getEntityManager()
->createQuery("
SELECT i
FROM PgeIncidenciasBundle:Incidencia i
WHERE i.fechaInicio = :semana // This is wrong because compares
// Y-m-d with W so no results found...
")
->setParameters(array(
'semana' => $semana
))
;
try {
return $query->getResult();
}
catch ('Doctrine'ORM'NoResultException $e)
{
return null;
}
使用BETWEEN运算符:
$from = new DateTime;
$from->setIsoDate($from->format('o'), $from->format('W'));
$to = clone $from;
$to->modify('+6 day');
$query = $this->getEntityManager()
->createQuery("
SELECT i
FROM PgeIncidenciasBundle:Incidencia i
WHERE i.fechaInicio BETWEEN :from AND :to
")
->setParameters(array(
'from' => $from->format('Y-m-d'),
'to' => $to->format('Y-m-d'),
));
找到了一个使用WHERE IN()
并生成当前周日期数组的解决方案。
public function findCounter()
{
$now = new 'DateTime();
$semana = $now->format('Y-m-d');
$query = $this->getEntityManager()
->createQuery("
SELECT i
FROM PgeIncidenciasBundle:Incidencia i
WHERE i.fechaInicio IN (:semana)
")
->setParameters(array(
'semana' => $this->week_from_monday($semana)
))
;
try {
return $query->getResult();
}
catch ('Doctrine'ORM'NoResultException $e)
{
return null;
}
}
private function week_from_monday($date) {
list($year, $month, $day) = explode("-", $date);
$wkday = date('l',mktime('0','0','0', $month, $day, $year));
switch($wkday) {
case 'Monday': $numDaysToMon = 0; break;
case 'Tuesday': $numDaysToMon = 1; break;
case 'Wednesday': $numDaysToMon = 2; break;
case 'Thursday': $numDaysToMon = 3; break;
case 'Friday': $numDaysToMon = 4; break;
case 'Saturday': $numDaysToMon = 5; break;
case 'Sunday': $numDaysToMon = 6; break;
}
$monday = mktime('0','0','0', $month, $day-$numDaysToMon, $year);
$seconds_in_a_day = 86400;
for($i=0; $i<7; $i++)
{
$dates[$i] = date('Y-m-d',$monday+($seconds_in_a_day*$i));
}
return $dates;
}