Doctrine 2.1 - datetime column default value

Question

Could someone tell me how to add the default value on a DateTime column? I can't do it like this:

protected $registration_date = date("Y-m-d H:i:s", time());

So how can I handle it?

sanis · Answer 1 · 2018-11-13T15:24:50.023

100

For default value CURRENT_TIMESTAMP:

     @ORM\Column(name="created_at", type="datetime", options={"default": "CURRENT_TIMESTAMP"})

Or for older Symfony versions:

     @ORM\Column(name="created_at", type="datetime", options={"default": 0})

Worked for me... However this works only with MySQL.

edited Nov 13 '18 at 15:24

answered Dec 14 '15 at 20:21

sanis

1,329
1
10
11

11

This should be the top answer now IMO, especially if you want the table schema itself to assume CURRENT_TIMESTAMP – skrilled Apr 22 '16 at 18:21
1

Thanks. This helped me a lot. I've used `options={"default": "CURRENT_TIMESTAMP"})` before which generally worked aswell. But with my postgres db behind it it got transformed to DEFAULT NOW() which is the postgres way. But then a doctrine:migrations:diff would misinterpret the now() as a deviation from CURRENT_TIMESTAMP and create a diff file each time I run it. But with this solution doctrine gets that everything is in sync. – luba Apr 11 '17 at 11:43
2

Great answer and, IMO, the cleanest way to do this. If it's of any interest this was translated as `ADD date_added TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL` for me. – crmpicco Jul 14 '17 at 10:57
By the way it's worth to mention that this will not work if NO_ZERO_DATE https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date is enabled on the server. See note from https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html – sanis Jul 16 '17 at 07:07
2

It is to mention, that this only works with MySQL >= 5.6.5 on below MySQL does not support setting CURRENT_TIMESTAMP as a default value for DATETIME columns. See https://stackoverflow.com/a/168832/4735939 – denkweite Jul 24 '17 at 09:01
1

This not work with mysql:5.7 ``SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'date_create'`` – Thomas Decaux Mar 08 '18 at 16:18
1

Does not work with PostgreSQL (9.6.6) as it throws: `SQLSTATE[22008]: Datetime field overflow: 7 ERROR: date/time field value out of range: "0"` – conceptdeluxe Apr 05 '18 at 00:21
1

This was indeed not a bad idea in 2014, but in 2018, LifecycleCallbacks is the way to go. Plus this does not work on PostgreSQL anymore. – scyrma Oct 31 '18 at 07:10
Does not work with _Oracle DB 12.1_ and _"doctrine/orm" 2.6.1_, creates a migration with the code: `DEFAULT '0'`. An error occurs: ORA-01840: input value is not long enough for date format. For me, the answer was workable @D.Samchuk `* @ORM \ Column (type = "datetime", options = {"default" = "CURRENT_TIMESTAMP"})` – Maxim Mandrik Nov 12 '18 at 15:13
1

It doesn't either work with Doctrine 2.6 anymore with MariaDB and I assume MySQL. https://github.com/doctrine/orm/issues/6346 – user1032531 Mar 29 '19 at 11:41
1

prop `name` is redundant! don't use it in that case. you must define it in case `@ORM\Table|Column(name="``order``")` or similar (when name is reserved word) – Vasilii Suricov Sep 01 '19 at 11:55

Max · Accepted Answer · 2012-02-02T06:42:34.207

63

You map your property as DateTime type then set the value in the constructor using a new DateTime instance:

/**
 * @Entity
 * @Table(name="...")
 */
class MyEntity
{
    /** @Column(type="datetime") */
    protected $registration_date;

    public function __construct()
    {
        $this->registration_date = new DateTime(); 
    }
}

This works as the constructor of a persisted class is not called upon hydration.

edited Feb 02 '12 at 06:42

answered Oct 08 '11 at 17:55

Max

15,693
14
81
131

1

I vote up for this answer because it is the pure PHP solution. Other answers may: 1) need special knowledge `onPrePersistSetRegistrationDate` which add `HasLifecycleCallbacks`, `PrePersist` annotations 2) I want to set a default value needs me to add 3rd library `@Gedmo\Timestampable`?!! 3) CURRENT_TIMESTAMP is sometimes not good. Ex.: when your script server in China but database server in America. ( of cause, it is not very common, but there are such cases ) – Yarco Apr 07 '17 at 07:01
@Yarco It depends on a case. When you have a table without a datetime column and then decides to add it - you need a default value to be populated for existing table rows. In this case PHP solution will not work. – FreeLightman Jul 16 '20 at 09:15

score 60 · Answer 3 · edited Jan 11 '18 at 01:05

60

You can also use lifecycle callbacks if you want to be very precise:

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\HasLifecycleCallbacks
 * ...
 */
class MyEntity
{
    /**
     * @ORM\PrePersist
     */
    public function onPrePersistSetRegistrationDate()
    {
        $this->registration_date = new \DateTime();
    }
}

edited Jan 11 '18 at 01:05

t j

7,026
12
46
66

answered Oct 08 '11 at 20:36

Alessandro Desantis

14,098
1
26
32

5

I prefer this answer as it makes it much more clear how Doctrine will handle it. – Bala Clark May 30 '12 at 10:36
3

+1 It will put latest date in the record. If the record have two dates modified_datetime and created_datetime. If you put the modified_datetime in constructor, the created_time may be newer than modified_datetime. – Mohammed H Feb 28 '13 at 07:55
For reference on all of the capabilities of lifecycle callbacks: http://doctrine-orm.readthedocs.org/en/latest/reference/events.html#reference-events-lifecycle-events – Will B. Jan 20 '15 at 15:29

score 23 · Answer 4 · answered Sep 22 '13 at 17:45

23

There is an extension for this automating this...

https://github.com/l3pp4rd/DoctrineExtensions/blob/master/doc/timestampable.md

/**
     * @var \DateTime
     *
     * @ORM\Column(name="date_added", type="datetime")
     * @Gedmo\Timestampable(on="create")
     */
    private $date_added;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date_modified", type="datetime")
     * @Gedmo\Timestampable(on="update")
     */
    private $date_modified;

answered Sep 22 '13 at 17:45

rat4m3n

1,201
2
16
23

2

This needs upvoting as it is certainly the best way to do this. – Sc0ttyD Jun 24 '14 at 21:44
4

@Sc0ttyD Why use an extension if you can reach exactly the same goal with the basic features? – luba Apr 11 '17 at 11:45
1

No idea - don't use PHP any more, this was 3 years ago :-) – Sc0ttyD Apr 12 '17 at 10:15
1

@luba To ensure database compatibility would be one thought. What if one database doesn't handle setting the default to 0 and mapping it to current_timestamp or doesn't understand current_timestamp? – Hayden Mar 16 '18 at 16:58

score 23 · Answer 5 · answered Jul 05 '16 at 11:40

23

I think, the best way to accomplish autofill for datetime is to make like that:

* @ORM\Column(type="datetime", options={"default"="CURRENT_TIMESTAMP"})

Putting logic to constructor isn't right solution, because setting default values are SQL client responsibility. If you decide no longer use ORM - you will lost business logic. Plus, if using constructor you won't be able to add default timestamps to datetime attributes for existing rows.

answered Jul 05 '16 at 11:40

D.Samchuk

1,219
9
9

1

As mentioned below `"default": 0` works better with doctrine migrations. – luba Apr 11 '17 at 11:48

score 7 · Answer 6 · answered Mar 13 '18 at 13:00

7

@var string @ORM\Column(name="login_at", type="datetime", options={"default" = "CURRENT_TIMESTAMP"})

This will work. Just posting for future ref.

answered Mar 13 '18 at 13:00

nikhil_

179
2
6

5

This does not work: Integrity constraint violation: 1048 Column 'login_at' cannot be null – cwhisperer Jun 20 '18 at 10:24
@cwhisperer The above will work if you dont have any data for that table. Means if you have the column already, you have to add some values to this column for every row. Or else, please add the allow default : nullable=true In my case, i was creating a new table, so i didn't had any data in that new table. – nikhil_ Jun 20 '18 at 15:35
correct, but the question is to add default value and not null – cwhisperer Jun 21 '18 at 10:29
1

@cwhisperer, as i said in the first answer, it will add default value as current timestamp. But IF you're trying to apply this to an already existing table with data & column, then, this won't work, as if there's any row with empty value, then when the alter query runs its won't know which timestamp to put to those column's. won't they? I hope this clears the confusion you might had – nikhil_ Jul 14 '18 at 18:32

score 0 · Answer 7 · answered Jan 24 '19 at 14:12

0

Work for me with MySql and Symfony 3.4.

...
fields:
    start_date:
        type: date
        nullable: false
        options:
            default: '1910-01-01'
            comment: 'The default date is 1910-01-01'
...

answered Jan 24 '19 at 14:12

sdespont

13,915
9
56
97

Doctrine 2.1 - datetime column default value

7 Answers7

Linked

Related