MySQL 5.7 datetime precision support #5428

New Issue

admin · 2026-01-22T15:07:36+01:00

admin commented

2026-01-22 15:07:36 +01:00

Originally created by @jonny-no1 on GitHub (Feb 25, 2017).

Originally assigned to: @Majkl578 on GitHub.

/**
 * @ORM\Column(type="datetime", precision=6)
 */
private $updatedAt;

Source: https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

MySQL 5.7 has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:
CREATE TABLE t1 (t TIME(3), dt DATETIME(6));
The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

This has already been done for Mongo DB, see https://github.com/doctrine/mongodb-odm/issues/1061 and https://github.com/doctrine/mongodb-odm/issues/1063.

Originally created by @jonny-no1 on GitHub (Feb 25, 2017). Originally assigned to: @Majkl578 on GitHub. ```php /** * @ORM\Column(type="datetime", precision=6) */ private $updatedAt; ``` Source: https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html > MySQL 5.7 has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision: > > To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example: > > ```mysql > CREATE TABLE t1 (t TIME(3), dt DATETIME(6)); > ``` > > The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.) This has already been done for Mongo DB, see https://github.com/doctrine/mongodb-odm/issues/1061 and https://github.com/doctrine/mongodb-odm/issues/1063.

admin added the Improvement Duplicate labels 2026-01-22 15:07:36 +01:00

admin closed this issue

2026-01-22 15:07:37 +01:00

admin commented

2026-01-22 15:07:37 +01:00

@coudenysj commented on GitHub (Aug 21, 2017):

See https://github.com/doctrine/dbal/issues/1020

@coudenysj commented on GitHub (Aug 21, 2017): See https://github.com/doctrine/dbal/issues/1020

admin commented

2026-01-22 15:07:38 +01:00

@holtkamp commented on GitHub (Mar 30, 2018):

Also see https://github.com/doctrine/dbal/issues/2873

@holtkamp commented on GitHub (Mar 30, 2018): Also see https://github.com/doctrine/dbal/issues/2873

admin commented

2026-01-22 15:07:38 +01:00

@TomHAnderson commented on GitHub (Sep 7, 2018):

Here's a work around: https://blog.tomhanderson.com/2018/09/datetime-with-microseconds-for-mysql-in.html

@TomHAnderson commented on GitHub (Sep 7, 2018): Here's a work around: https://blog.tomhanderson.com/2018/09/datetime-with-microseconds-for-mysql-in.html

admin commented

2026-01-22 15:07:38 +01:00

@flaushi commented on GitHub (Apr 1, 2019):

@TomHAnderson Thanks for that!
In order for your code to also be compatible with postgres, I added

if($platform instanceof MySqlPlatform)
            return 'TIMESTAMP(6) WITHOUT TIME ZONE';
        else
            return 'DATETIME(6)';

@flaushi commented on GitHub (Apr 1, 2019): @TomHAnderson Thanks for that! In order for your code to also be compatible with postgres, I added ``` if($platform instanceof MySqlPlatform) return 'TIMESTAMP(6) WITHOUT TIME ZONE'; else return 'DATETIME(6)'; ```

admin commented

2026-01-22 15:07:38 +01:00

@TomHAnderson commented on GitHub (Apr 1, 2019):

@flaushi Using the Timestamp data type in MySQL can be odd. I recommend you use DATETIME(6) for your MySQL instead of timestamp: https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql

@TomHAnderson commented on GitHub (Apr 1, 2019): @flaushi Using the Timestamp data type in MySQL can be odd. I recommend you use `DATETIME(6)` for your MySQL instead of timestamp: https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql

admin commented

2026-01-22 15:07:39 +01:00

@flaushi commented on GitHub (Apr 1, 2019):

You are right, I had a typo.

if($platform instanceof PostgreSqlPlatform)
            return 'TIMESTAMP(6) WITHOUT TIME ZONE';
        else
            return 'DATETIME(6)';

@flaushi commented on GitHub (Apr 1, 2019): You are right, I had a typo. ``` if($platform instanceof PostgreSqlPlatform) return 'TIMESTAMP(6) WITHOUT TIME ZONE'; else return 'DATETIME(6)'; ```

admin commented

2026-01-22 15:07:39 +01:00

@flaushi commented on GitHub (Apr 1, 2019):

Actually I don't even understand, why doctrine cuts off the microsecond part.
Until I ran into trouble with this, I was naturally assuming microseconds would be stored.
AFAIK, a timestamp has 8 bytes on mysql in any case, so it also has nothing to do with saving storage. I bet I am not the only one who sorts by date (I use uuids as primary key). So, anybody seeking for the newest row will run into trouble if more than one row is created in the same second.
https://github.com/doctrine/dbal/issues/2873

@flaushi commented on GitHub (Apr 1, 2019): Actually I don't even understand, why doctrine cuts off the microsecond part. Until I ran into trouble with this, I was naturally assuming microseconds would be stored. AFAIK, a timestamp has 8 bytes on mysql in any case, so it also has nothing to do with saving storage. I bet I am not the only one who sorts by date (I use uuids as primary key). So, anybody seeking for the newest row will run into trouble if more than one row is created in the same second. https://github.com/doctrine/dbal/issues/2873

admin commented

2026-01-22 15:07:39 +01:00

@Majkl578 commented on GitHub (Apr 2, 2019):

Actually I don't even understand, why doctrine cuts off the microsecond part.

Wery likely due to portability and compatibility, but as seen in doctrine/dbal#2873 this is something that can be improved in DBAL 3.0.

I'm going to close this issue in favor of doctrine/dbal#2873, please continue discussion there.

@Majkl578 commented on GitHub (Apr 2, 2019): > Actually I don't even understand, why doctrine cuts off the microsecond part. Wery likely due to portability and compatibility, but as seen in doctrine/dbal#2873 this is something that can be improved in DBAL 3.0. I'm going to close this issue in favor of doctrine/dbal#2873, please continue discussion there.

admin commented

2026-01-22 15:07:40 +01:00

@Majkl578 commented on GitHub (Apr 2, 2019):

Duplicate of doctrine/dbal#2873

@Majkl578 commented on GitHub (Apr 2, 2019): Duplicate of doctrine/dbal#2873

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5428