Doctrine 的日期字段 - 如何编写查询 [英] Doctrine's date field - how to write query

问题描述

我正在使用 Symfony2 和 Doctrine.

I'm using Symfony2 and Doctrine.

我有一个日期字段，在这里:

I have a date field, here it is:

/**
  * @ORMColumn(type="date")
  */
 protected $date;

在我的表单中，我使用文本字段来避免 Chrome 默认的日期选择器，但我在数据库中插入了 DateTime 对象:

In my form I use a text field to avoid Chrome default datepicker, but I insert DateTime objects in the database:

if ($request->isMethod('POST')) {
        $form->bind($request);

        //Convert string date to DateTime object and send it to database as object
        $dateObj = DateTime::createfromformat('d-m-Y', $expense->getDate());
        $expense->setDate($dateObj);
        // ...

然后我想查找具有特定日期的所有项目:

and then I want to find all items with a specific date:

public function findExpensesForDate($user, $date)
{
    $q = $this
        ->createQueryBuilder('e')
        ->where('e.date = :date')
        ->andWhere('e.user = :user')
        ->setParameter('date', $date)
        ->setParameter('user', $user)
         ->getQuery();

    return $q->getResult();
}

并这样称呼它:

$expenses_for_today = $this->repository->findExpensesForDate($this->user, $today);

$today = new /DateTime();

并返回结果

$today_obj = new /DateTime();
$today = $today_obj->format('Y-m-d'); 

那么为什么当我将日期作为对象时这不起作用?使用 date 归档的原因不是利用 DateTime 对象的查询吗?我想我错过了一些琐碎而重要的东西，但我就是看不到什么，或者我不太了解情况.我的理解是这样的:该字段是日期类型，所以我应该在其中插入 DateTime 对象，并且在查询时我也应该使用 DateTime 对象.你能帮我解决这个问题吗?

So why when I give the date as object this doesn't work? Isn't the reason to use date filed is to take advantage of quering with DateTime objects? I guess I'm missing something trivial and important, but I just can't see what, or I'm not understanding the situation quite well. My understanding is like this: the field is of type date, so I should insert DateTime objects in it and when quering I should also you DateTime objects. Can you please help me to fix this?

P.S.:我尝试将字段更改为日期时间:

P.S.: I tried changing the field to datetime:

 /**
  * @ORMColumn(type="datetime")
  */
 protected $date;

但没有变化.

用字符串查询是否可以?以这种方式查询时，我会获得使用对象的优势吗?

And at all is it OK and good to query with string? Will I get the advantage of using objects when querying that way?

推荐答案

我认为这是因为 DateTime 对象太具体了，而且还有小时、分钟和秒，所以它不能等于在数据库！

I think this is because a DateTime object is too much specific and have also hours, minutes and seconds, so it can't be equal to a registered date in database !

如果要使用DateTime对象，需要获取当天开始的日期和结束日期，才能得到当天的所有结果！您必须比较一个日期间隔才能获得它之间的所有日期.

If you want to use DateTime objects, you need to get the date of the beginning of the day and the end date to get all results of the day ! You must compare an interval of dates to get all dates between it.

首先，获取当天的开始日期和结束日期(为简化起见，我们将结束日期基于第二天的开始，我们将在请求中排除它):

First, get the start and the end dates of the current day (to simplify, we will base the end date on the beginning of the next day, and we will exclude it in the request) :

$fromDate = new DateTime('now'); // Have for example 2013-06-10 09:53:21
$fromDate->setTime(0, 0, 0); // Modify to 2013-06-10 00:00:00, beginning of the day

$toDate = clone $fromDate;
$toDate->modify('+1 day'); // Have 2013-06-11 00:00:00

并修改您的方法:

public function findExpensesForDate($user, $fromDate, $toDate)
{
    $q = $this
        ->createQueryBuilder('e')
        ->where('e.date >= :fromDate')
        ->andWhere('e.date < :toDate')
        ->andWhere('e.user = :user')
        ->setParameter('fromDate', $fromDate)
        ->setParameter('toDate', $toDate)
        ->setParameter('user', $user)
         ->getQuery();

    return $q->getResult();
}

就是这样，它应该可以工作，这里是脚本:

That's all, it should work, here the script :

$expenses_for_today = $this->repository->findExpensesForDate($this->user, $fromDate, $toDate);

因此您将获得 2013-06-10 00:00:00 和 2013-06-11 00:00:00 之间的所有日期(不包括)，所以当天的结果！

So you will get all the dates between the 2013-06-10 00:00:00 and the 2013-06-11 00:00:00 (excluded), so the results of the day !

这篇关于Doctrine 的日期字段 - 如何编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！