Home > database >  Date can't be formatted into String from Querybuilder
Date can't be formatted into String from Querybuilder

Time:03-07

I use the Querybuilder in Symfony 5 and have two date columns. In the JSON String the date would be not only one string. And I had tryied to add DATE_FORMAT to the select, but it would not work.

My current code is:

public function getTutoriallist(ManagerRegistry $doctrine, SerializerInterface $serializer): Response
{
    $response = new JsonResponse();
    $response->setCharset('UTF-8');
    $response->setEncodingOptions(JSON_PRETTY_PRINT);
    
    $query = $this->getDoctrine()
        ->getRepository('App:GrindingappTutorials')
        ->createQueryBuilder('t')
        ->select(['t.id', 't.headline', 't.descriptiontext'])
        ->addSelect(['t.adddate'])
        ->addSelect(['t.changedate'])
        ->getQuery();
    $response->setData($query->getArrayResult());
    return $response;
}

and the JSON-Response looks like

[
{
    "id": 1,
    "headline": "How to messure the T8",
    "descriptiontext": "Learn how to messure the T8",
    "adddate": {
        "date": "2022-03-02 00:00:00.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    "changedate": {
        "date": "2022-03-02 00:00:00.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    }
}
]

CodePudding user response:

DATE_FORMAT is not natively available in Doctrine. However you could write a custom dql function that does the job or simply use an existing available Doctrine extension such as this one as shown in the following answer. Be careful that the author use Mysql in his example.

CodePudding user response:

To get a RFC3339 formatted date you need to use the Serializer component DateTimeNormalizer. This is usually loaded automatically.

However, by instancing a JsonResponse directly and using setData, you are using php's json_encode and not the Symfony Serializer. Use AbstractController::json instead and it should work as expected.

$query = $this->getDoctrine()
// Conditions ommited
;

return $this->json($query->getResult());
  • Related