doctrine 2.0 : use SQL timestamp

Question

I'm looking for a way to make doctrine using TIMESTAMP instead of DATETIME for MySql.

Additionaly I need to set ON UPDATE CURRENT_TIMESTAMP and CURRENT_TIMESTAMP as default values.

I would like to have the possibility to have all this code in PHP annotations to have everything in one central place.

How can I do that?

score 28 · Answer 1 · edited Nov 25 '16 at 09:35

28

After hours of searching, I found the answer and I hope this helps anyone else looking for a more satisfactory solution than entity lifecycles and the WRONG column type (because a TIMESTAMP is a particular type with specific behaviours beyond just storing a datetime value)

All you need to do is to add

 * @ORM\Column(type="datetime", nullable=false)
 * @ORM\Version

to your annotation and Doctrine will both create a TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP and then return the actual table value as a valid \DateTime object.

CTOP (Credits to Original Poster)

edited Nov 25 '16 at 09:35

LaurentG

474
4
16

answered Sep 17 '14 at 10:41

David Soussan

2,698
1
16
19

1

Why without Version it creates DateTime but with Version it creates Timestamp? – JorgeeFG Jan 05 '17 at 15:20
2

For that you would have to ask the people who wrote it. – David Soussan Apr 21 '17 at 12:40
2

The `version` attribute is flag to tell Doctrine that it can change the value in this column to keep track of where concurrent writes may impact the row data prior to the end of long running transaction. See [Optimistic Locking](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/transactions-and-concurrency.html#optimistic-locking). It is perhaps not a good idea to adopt this attribute for the sake of tripping Doctrine to use `timestamp` for your column. – Courtney Miles May 03 '17 at 05:37

score 4 · Accepted Answer · edited Nov 25 '16 at 09:35

There is no such thing like using TIMESTAMP in mysql with Doctrine. However, I fixed it myself but have to test it:

Create a file: Doctrine\DBAL\Types\TimestampType.php
Copy the code from TimeType.php into TimestampType.php
Rename 'Time' to 'Timestamp' in the code
Create a new constant, and add timestamp to the typesmap in: Doctrine\DBAL\Types\Type.php
Create function called getTimestampTypeDeclarationSQL in Doctrine\DBAL\Platforms\AbstractPlatform.php
And in that function, return TIMESTAMP

I've used yaml to create my Entities and Proxies, so use in yaml:

type: timestamp

I'm going to test this 'fix'/'workaround' now. I'll let you know.

Guys, check @david-soussan answer too https://stackoverflow.com/a/25888426/1751591 — Zain Saqer, Aug 08 '16 at 11:53

score 4 · Answer 3 · answered May 30 '17 at 17:58

As suggested by Daniel Criconet,

@ORM\Column(type="datetime", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP")

will make that particular column in the corresponding MySQL table become TIMESTAMP instead of DATETIME.

For YAML version, see the original answer.

score 3 · Answer 4 · answered Jul 30 '19 at 23:17

/**
 * @var \DateTime
 * @ORM\Column(type="datetime", columnDefinition="timestamp default current_timestamp")
 */
protected $createdAt;

/**
 * @var \DateTime
 * @ORM\Column(type="datetime", columnDefinition="timestamp default current_timestamp on update current_timestamp")
 */
protected $updatedAt;

score 2 · Answer 5 · answered Jan 04 '15 at 20:23

2

@Polichism provided the right logic, but the patch wasn't accepted. Instead, users were directed to add a custom type. I've implemented that by combining:

The logic in @Polichism's patch
Doctrine's recommended Custom Mapping Types code
Zf2 Application Configuration instructions found here

The final versions can be found on GitHub:

Timestamp Class
See the 'doctrine' part of the Configuration

NOTE: I've linked to specific commits to ensure that the links remain valid if the file is later removed. Use the history to check for newer versions.

answered Jan 04 '15 at 20:23

claytond

1,061
9
22

How did you solved field TIMESTAMP DEFAULT NULL when column is nullable issue? I've created custom type and it looks like working fine, but when I use doctrine:schema:create --dump-sql it generates "fieldName TIMESTAMP DEFAULT NULL" which is not a valid value for TIMESTAMP. Correct syntax is: "filedName TIMESTAMP NULL". Looks like I'll have to override the Platform implementation... any other ideas? – Anton Valqk Apr 15 '15 at 09:42
Frankly, I didn't --dump-sql to create my tables. The syntax also seems to be [valid for pgSQL](http://stackoverflow.com/a/9826915/1978687). For [MySQL](http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html), the syntax `timestamp NULL DEFAULT NULL` seems to be valid as well. To get Doctrine to add that first `NULL`, first try the `nullable` attribute in the [@Column annotation](http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#annref-column). Worst case, use the (non-portable) `columnDefinition` attribute. – claytond Apr 16 '15 at 13:43

score 1 · Answer 6 · answered May 30 '15 at 13:59

1

/** @ORM\Column(type="datetime", nullable=false ) */
    protected $created;

this will create timestamp and return datetime object

 public function __construct()
        {
            $this->created = new \DateTime();

        }

answered May 30 '15 at 13:59

Saurabh Chandra Patel

12,712
6
88
78

score 0 · Answer 7 · answered Mar 25 '17 at 21:07

0

/** * @Column(type="datetime", options={"default": 0}) * @version=true */ private $data;

create sql like this: data TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

tested on version 2.5.4

answered Mar 25 '17 at 21:07

slaszu

1

score 0 · Answer 8 · answered Mar 30 '17 at 15:14

0

@ORM\Version works only for one property per entity so it is not an option if you need to have more than one timestamp field in a table.

answered Mar 30 '17 at 15:14

user3281376

1

doctrine 2.0 : use SQL timestamp

8 Answers8