Doctrine: Set CURRENT_TIMESTAMP as default value by the database (i.e. not by PHP)

Question

After looking around for a while, I still couldn't find a way to get CURRENT_TIMESTAMP inserted by the database server (as default value on INSERT).

The problem: When you persist an object to the database, missing fields are explicitly set to NULL by Doctrine. So it looks like, setting a default value in the table definition, doesn't have any effect at all :-(

I don't want to set the time through PHP (e.g. $object->setTimestamp(new \DateTime());) cause this might return a different time than what the database server has, as explained here: https://stackoverflow.com/a/3705090/1668200

What I've tried so far:

• Send in NOW literally (e.g. $object->setTimestamp('NOW()');), as explained here: https://stackoverflow.com/a/13850741/1668200
  => Didn't work: Error: Call to a member function format() on string

• Removing the 'timestamp' property from the object just before persisting it (see https://stackoverflow.com/a/3600758/1668200 ) didn't work either: The field was set to NULL by Doctrine anyway.

Any other solution I found (including the Doctrine extension 'Timestampable' https://github.com/Atlantic18/DoctrineExtensions/blob/master/doc/timestampable.md ) uses PHP's time.

Answer 1

/**
 * @ORM\Column(type="datetime", options={"default": "CURRENT_TIMESTAMP"})
 */
protected $created;     

Just remember that this will not allow previous rows to be empty if you update an existing table.

Answer 2

Have a look at this: https://stackoverflow.com/a/29384596/3255540 it should resolve the problem with Error: Call to a member function format() on string and force SQL NOW() to be sent to the database.

Answer 3

UPDATE: This is not compatible with VasekPurchart\DoctrineDateTimeImmutableTypesBundle anymore. And it won't work with Doctrine DBAL 2.6. See https://github.com/VasekPurchart/Doctrine-Date-Time-Immutable-Types-Bundle/issues/17

---

So, wrapping it all up, here's a step-by-step instruction:

1) Create a new Class "DateTimeNow"; a good location is probably AppBundle/Entity/DateTimeNow.php:

<?php
namespace AppBundle\Entity;
class DateTimeNow
{
    public function format() 
    {
        return 'NOW()';
    }
}

2) In your to-be-timestamped entity, add a function to be executed whenever a new entity is saved (i.e. on each INSERT):

public function doPrePersist()
{
    $this->timestamp = new DateTimeNow();
}

3) Register this function as a Lifecycle Callback prePersist. For Annotations, see http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/annotations-reference.html#annref-prepersist. For YAML:

AppBundle\Entity\Whatever:
    # ...
    lifecycleCallbacks:
        prePersist: [ doPrePersist ]

That's it! :-)

Now, when you persist an empty object, this is the resulting query:

INSERT INTO public.whatever(id, text, timestamp) VALUES (?, ?, ?)
Parameters: { 1: null, 2: null, 3: NOW() }