1

In my application I save data with doctrine to an mysql database. for me it is totally sufficient to save the date and not also the time therefore I have chosen the Date format for my database field.

In the entity of my Symfony 4 app this looks like that:

/**
 * @var DateTime
 *
 * @ORM\Column(name="create_date", type="date", nullable=false, options={"default" = "CURRENT_TIMESTAMP"})
 * @Assert\NotBlank
 */
private $createDate;


/**
 * @param DateTime $createDate
 *
 * @return Asset
 */
public function setCreateDate(DateTime $createDate)
{
    $this->createDate = $createDate;

    return $this;
}

The resulting SQL statement looks as follows:

INSERT INTO asset 
(name, create_date, beide, comment, amount, user_id, category_id) 
VALUES 
("Schmeckerlis","2019-01-19 16:03:53",0,"",7.34,345,345)

As you see there is the time added. Is this how you set up a date field with doctrine?

Do I have to change it to a DateTime or Timestamp field?

Should I remove the default value in the entity?

https://www.doctrine-project.org/projects/doctrine-dbal/en/2.6/reference/types.html#date says the following:

"date: Maps and converts date data without time and timezone information. If you know that the data to be stored always only needs to be a date without time and timezone information, you should consider using this type. Values retrieved from the database are always converted to PHP's \DateTime object or null if no data is present."

Calamity Jane
  • 2,189
  • 5
  • 36
  • 68

1 Answers1

2

CURRENT_TIMESTAMP returns the date and time as YYYY-MM-DD HH-MM-SS. If I understand you correctly you only need the YYYY-MM-DD part.

For this you have to remove the options={"default" = "CURRENT_TIMESTAMP"} and do this in the constructor method of the entity. This one gets called everytime you do new Entity().

public function __construct()
{
    $date = new \DateTime();
    $this->createDate = $date->format('Y-m-d');
}
Fabian Schmick
  • 1,616
  • 3
  • 23
  • 32
  • Why format it with the time? In the Insert statement in the log I still have the time: INSERT INTO asset (name, create_date, beide, comment, amount, user_id, category_id) VALUES (?, ?, ?, ?, ?, ?, ?) {"file":"/var/www/sbudget/vendor/symfony/doctrine-bridge/Logger/DbalLogger.php"} {"1":"Leckerlis","2":"2019-01-19 06:05:30","3":0,"4":"","5":7.34,"6":559,"7":559} So yes the insert works, but it looks unclean to me to try to insert a wrong format. – Calamity Jane May 05 '19 at 06:18
  • Ups, sorry I forgot to remove the `H:i:s` part from the date format – Fabian Schmick May 06 '19 at 06:05
  • I just tried your solution. Although this solution sounds logical I still have in the insert statement the Datetime. The tests are passing, so from that point of view there is no problem and the data is inserted correctly. It is just weird that there seems to be no option to set it up correctly with doctrine. – Calamity Jane May 11 '19 at 15:56