How to map MySQL column "timestamp" to a field with Doctrine2

Question

I created a Doctrine2 Entity and would like to map a field to timestamp column in MySQL.

/**
 * @ORM\Table(name="biz_order")
 * @ORM\Entity(repositoryClass="Acme\OrderBundle\Repository\OrderRepository")
 */
class Order
{
    /**
     * @ORM\Column(name="order_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    // Lots of other fields ...

    /**
     * @ORM\Column(name="order_timestamp", type="datetime")
     */
    private $createdOn;    
}

With annotated type as "datetime" I get following error:

Doctrine\DBAL\Types\ConversionException: Could not convert database value "1390362851" to Doctrine Type datetime. Expected format: Y-m-d H:i:s

at n/a in /var/www/packer/vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/ConversionException.php line 63

at Doctrine\DBAL\Types\ConversionException::conversionFailedFormat('1390362851', 'datetime', 'Y-m-d H:i:s') in /var/www/packer/vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/DateTimeType.php line 67

However in Doctrine 2.4 documentation I found following

datetime: Type that maps a SQL DATETIME/TIMESTAMP to a PHP DateTime object.

How can I map timestamp DB column to a PHP class field in Doctrine2?

EDIT: So far my workaround is using the type="integer" in ORM mapping and returning it as ValueObject

public function getCreatedOn()
{
    $createdOn = new \DateTime();
    $createdOn->setTimestamp($this->createdOn);
    return $createdOn;
}

I am building an application that is sharing DB with a legacy application and so I have to map existing DB structure to the new Entities. — Tomas Dermisek, Feb 02 '14 at 21:57

score 2 · Answer 1 · edited Jan 25 '19 at 10:48

2

You can just create a custom doctrine type defined timestamp, see the documentation

edited Jan 25 '19 at 10:48

nicolallias

1,055
2
22
51

answered May 05 '16 at 12:17

Raffaello

1,641
15
29

Mick · Answer 2 · 2017-06-05T01:50:28.890

1

You can look at This post: datetime vs timestamp

Since it is a createdAt property, and represents a point in time, you might want to fetch objects that have been created before $createdAt or after $createdAt.

To do that, your best option is to store the datetime exactly the way you've done it but to associate a \Datetime object to that field: $this->createdAt = new \Datetime();

The best way for you would be to use lifecycle callbacks:

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="biz_order")
 * @ORM\Entity(repositoryClass="Acme\OrderBundle\Repository\OrderRepository")
 * @ORM\HasLifecycleCallbacks
 */
class Order
{
    /**
     * @ORM\Column(name="order_timestamp", type="datetime")
     */
    private $createdAt;    

    /**
     * @ORM\PrePersist
     */
    public function doStuffOnPrePersist()
    {
        $this->createdAt= new \DateTime();
    }


}

edited Jun 05 '17 at 01:50

answered Jan 31 '14 at 09:09

Mick

30,759
16
111
130

2

As I mentioned above I am building an application that is sharing DB with a legacy application and so I have to map existing DB structure to the new Entities. Your code sample is not different to what I put here as my problem and I am still getting the same error even with adding the PrePersist callback. The field in DB is a timestamp and returns integer value hence the error "Could not convert database value "1390362851" to Doctrine Type datetime. " – Tomas Dermisek Feb 02 '14 at 22:05
I am using similar construct to what you proposed with PerPersist callback and \Datetime field for newly created tables where the field type in DB is Datetime. In such case it works fine. – Tomas Dermisek Feb 02 '14 at 22:07

How to map MySQL column "timestamp" to a field with Doctrine2

2 Answers2