Orm: DDC-3040: doctrine:schema:update datetimetz field type not null

[doctrinebot]

Jira issue originally created by user ruscon:

I have some fields like

    /****
     * Adding date in format ISO-8601 YYYY-MM-DDThh:mm:ss±hhmm
     *
     * @var \DateTime
     *
     * @ORM\Column(name="added_at", type="datetimetz", nullable=false)
     */
    private $addedAt;

    /****
     * Expire date in format ISO-8601 YYYY-MM-DDThh:mm:ss±hhmm
     *
     * @var \DateTime
     *
     * @ORM\Column(name="expired_at", type="datetimetz", nullable=false)
     */
    private $expiredAt;

@ORM\Column -> nullable=false

In database this field already not null
But when i execute in console:
{quote}
./app/console doctrine:schema:update --dump-sql --env=dev
{quote}
answer:
{quote}
ALTER TABLE test CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
{quote}
If I change datetimetz to datetime type fot $expiredAt
and execute:
{quote}
./app/console doctrine:schema:update --dump-sql --env=dev
{quote}
answer:
{quote}
ALTER TABLE test CHANGE added_at added_at DATETIME NOT NULL;
{quote}

[doctrinebot]

Comment created by ruscon:

have some news about this problem ?

[doctrinebot]

Comment created by @ocramius:

[~ruscon] does the DDL update statement persist in the diffs even after running it?

[doctrinebot]

Comment created by ruscon:

$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE device*audit CHANGE added_at added*at DATETIME DEFAULT NULL;
ALTER TABLE package*activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire*at DATETIME NOT NULL;
ALTER TABLE package*activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire*at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created*at created*at DATETIME NOT NULL;
ALTER TABLE package*audit CHANGE created_at created*at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added*at added_at DATETIME NOT NULL, CHANGE expired_at expired*at DATETIME NOT NULL;
ALTER TABLE coupon*audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired*at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE company*audit CHANGE added_at added*at DATETIME DEFAULT NULL;

$ ./app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "10" queries were executed

$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE device*audit CHANGE added_at added*at DATETIME DEFAULT NULL;
ALTER TABLE package*activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire*at DATETIME NOT NULL;
ALTER TABLE package*activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire*at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created*at created*at DATETIME NOT NULL;
ALTER TABLE package*audit CHANGE created_at created*at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added*at added_at DATETIME NOT NULL, CHANGE expired_at expired*at DATETIME NOT NULL;
ALTER TABLE coupon*audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired*at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE company*audit CHANGE added_at added*at DATETIME DEFAULT NULL;

or if i use additional bundle

$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE device*audit CHANGE added_at added*at DATETIME DEFAULT NULL;
ALTER TABLE package*activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire*at DATETIME NOT NULL;
ALTER TABLE package*activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire*at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created*at created*at DATETIME NOT NULL;
ALTER TABLE package*audit CHANGE created_at created*at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added*at added_at DATETIME NOT NULL, CHANGE expired_at expired*at DATETIME NOT NULL;
ALTER TABLE coupon*audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired*at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE company*audit CHANGE added_at added*at DATETIME DEFAULT NULL;

$ ./app/console doctrine:migrations:diff
Generated new migration class to "/Users/ruscon/projects/xxx/app/DoctrineMigrations/Version20140407004542.php" from schema differences.

$ ./app/console doctrine:migrations:migrate

                    Application Migrations


WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y
Migrating up to 20140407004542 from 20140405144932

  <ins></ins> migrating 20140407004542

     -> ALTER TABLE device CHANGE added*at added*at DATETIME NOT NULL
     -> ALTER TABLE device*audit CHANGE added_at added*at DATETIME DEFAULT NULL
     -> ALTER TABLE package*activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire*at DATETIME NOT NULL
     -> ALTER TABLE package*activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire*at DATETIME DEFAULT NULL
     -> ALTER TABLE package CHANGE created*at created*at DATETIME NOT NULL
     -> ALTER TABLE package*audit CHANGE created_at created*at DATETIME DEFAULT NULL
     -> ALTER TABLE coupon CHANGE added*at added_at DATETIME NOT NULL, CHANGE expired_at expired*at DATETIME NOT NULL
     -> ALTER TABLE coupon*audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired*at DATETIME DEFAULT NULL
     -> ALTER TABLE company CHANGE added*at added*at DATETIME NOT NULL
     -> ALTER TABLE company*audit CHANGE added_at added*at DATETIME DEFAULT NULL

  <ins></ins> migrated (3.46s)

  ------------------------

  <ins></ins> finished in 3.46
  <ins></ins> 1 migrations executed
  <ins></ins> 10 sql queries

$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE device*audit CHANGE added_at added*at DATETIME DEFAULT NULL;
ALTER TABLE package*activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire*at DATETIME NOT NULL;
ALTER TABLE package*activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire*at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created*at created*at DATETIME NOT NULL;
ALTER TABLE package*audit CHANGE created_at created*at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added*at added_at DATETIME NOT NULL, CHANGE expired_at expired*at DATETIME NOT NULL;
ALTER TABLE coupon*audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired*at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added*at added*at DATETIME NOT NULL;
ALTER TABLE company*audit CHANGE added_at added*at DATETIME DEFAULT NULL;

[doctrinebot]

Comment created by @deeky666:

I assume from the syntax that you are using MySQL. MySQL does not have a native type for DateTimeTz, therefore the mapping always falls back to the native DATETIME type. See the DBAL documentation (footnote 15): http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#mapping-matrix

The problem here is not the nullable flag but the type you use. Therefore you get the irrgeular update statements from the schema tool. Use Doctrine's "datetime" type instead and you'll be fine. You won't be able to store time zone information for a date time in MySQL anyways as it does not have a type for this.

[doctrinebot]

Comment created by ruscon:

Steve Müller, you right. Thanks.

[doctrinebot]

Issue was closed with resolution "Can't Fix"