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

New Issue

Closed

opened 2026-01-22 14:27:48 +01:00 by admin · 6 comments

admin commented 2026-01-22 14:27:48 +01:00

Owner

Copy Link

Originally created by @doctrinebot on GitHub (Mar 19, 2014).

Originally assigned to: @beberlei on GitHub.

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}

Originally created by @doctrinebot on GitHub (Mar 19, 2014). Originally assigned to: @beberlei on GitHub. 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}

admin added the Bug label 2026-01-22 14:27:48 +01:00

admin closed this issue 2026-01-22 14:27:48 +01:00

admin commented 2026-01-22 14:27:49 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 5, 2014):

Comment created by ruscon:

have some news about this problem ?

@doctrinebot commented on GitHub (Apr 5, 2014): Comment created by ruscon: have some news about this problem ?

admin commented 2026-01-22 14:27:49 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 6, 2014):

Comment created by @ocramius:

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

@doctrinebot commented on GitHub (Apr 6, 2014): Comment created by @ocramius: [~ruscon] does the DDL update statement persist in the diffs even after running it?

admin commented 2026-01-22 14:27:50 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 6, 2014):

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 commented on GitHub (Apr 6, 2014): 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; ```

admin commented 2026-01-22 14:27:50 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 7, 2014):

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 commented on GitHub (Apr 7, 2014): 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.

admin commented 2026-01-22 14:27:50 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 7, 2014):

Comment created by ruscon:

Steve Müller, you right. Thanks.

@doctrinebot commented on GitHub (Apr 7, 2014): Comment created by ruscon: Steve Müller, you right. Thanks.

admin commented 2026-01-22 14:27:50 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 7, 2014):

Issue was closed with resolution "Can't Fix"

@doctrinebot commented on GitHub (Apr 7, 2014): Issue was closed with resolution "Can't Fix"

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label Bug

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3775