如何在两个字符串值之间绑定datetime字段 - How to bind datetime field between two string value

How to bind datetime field between two string value

我有这样的方法:

public function getCustomDateOrders(string $startDay,string $endDay,string $food) :array
{
    $result = $this->_em->createQueryBuilder()
        ->select
        (
            'OrderEntity.name'
            'OrderEntity.created'
        )
        ->from($this->entityClass , 'OrderEntity')
        ->leftJoin(
            'Directory'Food',
            'Food',
            'with',
            'Food.id = OrderEntity.FoodId '
        )
        ->where("Food.id =:food")
        ->andWhere("OrderEntity.status =:active")
        ->andWhere("startDate<:OrderEntity.orderCreated >:endDay")
        ->getQuery()
        ->setParameters([
            "food"    =>       $food,
            "active"        =>  1,
            "startDate"     =>  $startDay,
            "endDay"        =>  $endDay
        ])
        ->getScalerResult();
        ->getScalarResult();
}

我有两个值:

美元startDay = "2016-010-17 00:00:00" (string)

美元endDay = "2016-10-03 00:00:00" (string)

， order.created (order的字段之一)为datetime。

我想从已经创建的字段在$startDay和$endDay之间的订单实体中获取订单。如何解决?

使用between:

public function getCustomDateOrders(string $startDay,string $endDay,string $food) :array
{
    $result = $this->_em->createQueryBuilder()
        ->select
        (
            'OrderEntity.name'
            'OrderEntity.created'
        )
        ->from($this->entityClass , 'OrderEntity')
        ->leftJoin(
            'Directory'Food',
            'Food',
            'with',
            'Food.id = OrderEntity.FoodId '
        )
        ->where("Food.id =:food")
        ->andWhere("OrderEntity.status =:active")
        ->andWhere("OrderEntity.orderCreated between :startDay and :endDay")
        ->setParameters([
            "food"    =>       $food,
            "active"        =>  1,
            "startDay"     =>  $startDay,
            "endDay"        =>  $endDay
        ])
       ->getQuery()
       ->getScalerResult();
}

这就是为什么我们不将日期存储为字符串。您仍然可以在查询中转换字符串，所以不要直接关闭计算机。

使用str_to_date()函数:

翻译成SQL:

select * from yourtable
where created > str_to_date(start_day, '%Y-%m-%d %H:%i:%s')
and created < str_to_date(end_day, '%Y-%m-%d %H:%i:%s')

好了。给我在start_date之后和结束日期之前创建的所有行。

更好的方法可能是将字符串转换为日期，并像建议的那样在之间使用