How to define current timestamp in yaml with doctrine?

Question

I tried the following yaml code:

columns:
  created_time:
    type: timestamp
    notnull: true
    default: default CURRENT_TIMESTAMP

In the outputted sql statement, the field is treated as datetime instead of timestamp, which I cannot define the current timestamp in it...

If I insist to use timestamp to store current time, how to do so in yaml?

score 18 · Answer 1 · edited Nov 14 '12 at 13:11

18

If you are willing to sacrifice some portability (see description of columnDefinition attribute) for the ability to use MySQL's automatic initialization TIMESTAMP (see MySQL timestamp initialization), then you can use the following:

Yaml:

  created_time:
    type: datetime
    columnDefinition: TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Annotation:

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

edited Nov 14 '12 at 13:11

j0k

22,600
28
79
90

answered Nov 14 '12 at 13:04

Daniel Criconet

189
1
2

Hi j0k, thank you for your answer. Made a new topic since I couldnt get it to work with your solution: http://stackoverflow.com/questions/17208388/symfony-1-4-doctrine-1-yalm-cannot-define-default-timestamp-value – xtrm Jun 20 '13 at 08:07

score 15 · Answer 2 · edited Jun 20 '16 at 12:49

Notice that DEFAULT CURRENT_TIMESTAMP does not work the same as Timestampable, and thus you cannot blindly exchange one for the other.

First and foremost, the former uses the date/time of the DB server, while the latter uses a Doctrine magic that calls PHP's date() function on your webserver. In other words, they are two distinct ways of getting the date/time, from two entirely different clock sources. You may be on big trouble if you use Timestampable, your web server runs on a different machine than your DB server, and you don't keep your clocks in sync using e.g. NTP.
Also, the DEFAULT CURRENT_TIMESTAMP being on the table definition makes for a much more consistent database model IMHO, as no matter how you insert the data (for instance, running INSERTs on the DB engine command line), you'll always get the current date/time on the column.

BTW, I'm also looking for an answer to the CURRENT_TIMESTAMP problem mentioned in the initial question, as this is (due to the reasons outlined above) my preferred way of keeping "timestamp" columns.

score 11 · Accepted Answer · answered Jun 02 '10 at 12:19

11

You could use the 'Timestampable' functionality in doctrine, eg:

actAs:
  Timestampable:
    created:
      name: created_time
    updated:
      disabled: true
columns:
  created_time:
    type: timestamp
   notnull: true

answered Jun 02 '10 at 12:19

Twelve47

3,924
3
22
29

score 0 · Answer 4 · edited Dec 16 '15 at 14:50

0

/**
 * @var int
 * @ORM\Column(type="datetime", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
 */
protected $created;

after run ./vendor/bin/doctrine-module orm:schema-tool:update --force

Updating database schema... Database schema updated successfully! "1" queries were executed

and run ./vendor/bin/doctrine-module orm:validate-schema

[Mapping] OK - The mapping files are correct. [Database] FAIL - The database schema is not in sync with the current mapping file.

But FAIL for sync appear

edited Dec 16 '15 at 14:50

ssuperczynski

3,190
3
44
61

answered Jan 24 '14 at 15:07

wmd

11

WARNING! it's good answer, when you won't update your database from PHP, but do want update structure with migrations or so! – Serge Velikan Mar 30 '15 at 11:51

score 0 · Answer 5 · answered Mar 24 '16 at 17:03

Sorry for necroposting. But i have encoutered the same problem. There is solution for doctrine 2 and postgreSql. I have used Gemdo extension and added following strings:

 $evm = new \Doctrine\Common\EventManager();

        $timestampableListener = new \Gedmo\Timestampable\TimestampableListener;
        $timestampableListener->setAnnotationReader($cachedAnnotationReader);
        $evm->addEventSubscriber($timestampableListener);

YAML:

created:
    type: date
    options:
        default: 0
        nullable: true
    gedmo:
        timestampable:
            on: create
updated:
    type: datetime
    options:
         default: 0
         nullable: true
    gedmo:
         timestampable:
             on: update

dump-sql:

ALTER TABLE users ADD created DATE DEFAULT CURRENT_DATE NOT NULL;
ALTER TABLE users ADD updated TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL;

score 0 · Answer 6 · edited Mar 24 '16 at 18:56

I suggest not to use "default" for timestamp at all.

It will bring unpredictable state in yaml in your application. This video (PHP UK Conference 2016 - Marco Pivetta - Doctrine ORM Good Practices and Tricks) provides some more information about this topic. I suggest you to to go through it and create a named constructor.

public function createTimestamp(string $priority, int $priorityNormalized)
{
    $this->priority = $priority;
    $this->priorityNormalized = $priorityNormalized;
}

I suggest to be stateless, good luck!

score -13 · Answer 7 · answered Dec 21 '11 at 23:56

-13

You can use:

default: '<?php echo date('Y-m-d H:i:s') ?>'

answered Dec 21 '11 at 23:56

Marcos

1

How to define current timestamp in yaml with doctrine?

7 Answers7

Linked