-
-
Notifications
You must be signed in to change notification settings - Fork 1.3k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Add microseconds support on datetime/time types #2873
Comments
Any progress with this issue? |
It was a really unexpected finding. Are there any workarounds? |
@nick4fake Yes, you can write something like this: https://github.com/PabloKowalczyk/Todora/blob/master/src/DateTimeImmutableMicrosecondsType.php Note: This class covers only Don't forget about type override: doctrine:
dbal:
types:
datetime_immutable: \Todora\DateTimeImmutableMicrosecondsType Also you can read this: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/known-vendor-issues.html#datetime-datetimetz-and-time-types |
Adding |
Before this can be considered, portability needs to be assessed, since the underlying data types must support saving fractional seconds.
@morozov What do you think about this for 3.0? |
@Majkl578 as for the portability assessment, I think we already have a decent majority (SQL Server supports fractions too). It doesn't look like a breaking change, so it can go to |
MariaDB has also 0-6 fractional seconds from 5.3 https://mariadb.com/kb/en/library/microseconds-in-mariadb/ |
The status as of now: Based on
So changing this is trivial, we just need to ensure it will not explode and this needs to be considered on per platform basis as Majkl said. |
This PR was started because I created a custom type for date time with microseconds: |
…uzet) This PR was submitted for the master branch but it was merged into the 4.3 branch instead (closes #10616). Discussion ---------- [Messenger] Describe the doctrine transport Add documentation relative to the PR symfony/symfony#29007 TODO - [ ] Document the max granularity in seconds for delay (doctrine/dbal#2873) Commits ------- 27c0f9d [Messenger] Describe the doctrine tranport
|
Is it possible to just overwrite or re-register the datetime type? ( In a symfony-setting like so:
|
@flaushi please remember that not everyone have |
@PabloKowalczyk Yes, absolutely, but one can replace Carbon with DateTime eaasily. For the sake of completeness, I solved my problem posted above. The reason was that postgres returns in format
|
I overwrote the DateTimeImmutable type, made the length dependant on the 'precision' fieldDeclaration, and simply added '.u' to the datetime formats. It caused some unexpected behaviour because by default mysql rounds times instead of truncates. We're used to truncating times in PHP (with the format method you only print the parts you need), but mysql rounds the values that fall outside the column definition. This behaviour can be changed in mysql 8: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_time_truncate_fractional. When using mysql 5.6, 5.7 or 8 without the |
Yes, mysql and postgres have strange behaviours, I agree. A really good advice is to use |
I am coming back to this issue, because now we have a use case e.g. in symfony/symfony#33785. I tried to change the used type in symfony messenger, but it relies on doctrine's builtin datetime type. I can well understand that the symfony team will not be willing to introduce a custom DBAL type on their side, but just selecting a new microseconds-based one will be possible I guess. |
Hi, please let me reanimate this issue. I found that microseconds-precision (6) was the default for SQLServer2012Platform: But all other platforms had (0). I would say the dates should be stored with the maximal precision available for proper sorting and comparisons. And it seems relevant than the If the precision would exceed the maximum supported by the platform, this maximum should be used instead. Is there anything preventing us to implement it that way? If not, I would be happy to propose a pull-request for that. |
@kylekatarnls, looks good. But is there any platform that does not support microseconds (precision = 6)? |
According to @Majkl578 There is at least SQLite that only support 0 or 3: #2873 (comment) And some other platforms/versions to be checked. But there is Travis unit tests in this repo to check every supported platform, so we could just give a try and add unit tests for the precision and see if a platform fail on Travis. |
Yes, right, SQLite doesn't make thinks easier all the time... So, great idea! |
So here is the summary: PostgreSQL: 0-6: https://www.postgresql.org/docs/10/static/datatype-datetime.html I would add that custom types like the one proposed by @flaushi here #2873 (comment) has no way to distinguish precision set to 0 from precision not set. Annotation As a first step |
I added microseconds to The migrations I had to manually create (since the diff doesn't see the change), but afterwards the datetime fields are created with the correct sql declaration. doctrine.yaml doctrine:
dbal:
platform_service: PostgreSQLMicrosecondsPlatform
types:
datetime: DateTimeMicrosecondsType PostgreSQLMicrosecondsPlatform.php <?php
use Doctrine\DBAL\Platforms\PostgreSQL100Platform;
class PostgreSQLMicrosecondsPlatform extends PostgreSQL100Platform
{
/**
* @inheritDoc
*/
public function getDateTimeFormatString()
{
return sprintf('%s.u', parent::getDateTimeFormatString());
}
} DateTimeMicrosecondsType.php use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\PostgreSQL100Platform;
use Doctrine\DBAL\Types\DateTimeType;
class DateTimeMicrosecondsType extends DateTimeType
{
/**
* @inheritDoc
*/
public function getSQLDeclaration(array $column, AbstractPlatform $platform)
{
if ($platform instanceof PostgreSQL100Platform) {
return 'TIMESTAMP(6) WITHOUT TIME ZONE';
}
return parent::getSQLDeclaration($column, $platform);
}
} |
Is there a reason why you extend the DateTime type instead of just setting its declaration in the Platform? This seems to work for me (but I'm no expert when it comes to Doctrine internals): # config/packages/doctrine.yaml
doctrine:
dbal:
platform_service: App\Doctrine\Platform\PostgreSQLPlatform namespace App\Doctrine\Platform;
use Doctrine\DBAL\Platforms\PostgreSQL100Platform;
class PostgreSQLPlatform extends PostgreSQL100Platform
{
public function getDateTimeFormatString(): string
{
return str_replace('s', 's.u', parent::getDateTimeFormatString());
}
public function getDateTimeTzFormatString(): string
{
return str_replace('s', 's.u', parent::getDateTimeTzFormatString());
}
public function getTimeFormatString(): string
{
return str_replace('s', 's.u', parent::getTimeFormatString());
}
public function getDateTimeTypeDeclarationSQL(array $column): string
{
return str_replace('(0)', '(6)', parent::getDateTimeTypeDeclarationSQL($column));
}
public function getDateTimeTzTypeDeclarationSQL(array $column): string
{
return str_replace('(0)', '(6)', parent::getDateTimeTzTypeDeclarationSQL($column));
}
public function getTimeTypeDeclarationSQL(array $column): string
{
return str_replace('(0)', '(6)', parent::getTimeTypeDeclarationSQL($column));
}
} |
@jzecca Can't remember exactly why, but I think I wanted to just change the |
+1 - we've had to put in a workaround for 10 years now so would be great to finally see this fixed. |
In MariaDB there's a bug (I think?) that converts |
Hi according to the response in this thread, it's not a bug https://jira.mariadb.org/browse/MDEV-4560?jql=text%20~%20%22greatest%20datetime%22 |
I think it's not related (MariaDB 10.5.16)
And here's the 'bug', it seems that converting 0 to datetime(0) makes the problem go away.
|
Hi indeed you're right : forcing type resolves issue, and it's what i did explain here akeneo/pim-community-dev#17711 This is a huge difference between mysql/maria, and honestly I don't know what to think. |
I'm talking to him as well :) We ended up solving the last mysteries with JSON so Akeneo should at least be somewhat compatible with MariaDB :) Let's ignore this problem for this thread. And yes, it's impossible for Doctrine to just assume things here. |
We're using postgres 15.5 and it seems to be storing with microseconds when using |
Does this even work? I have added a custom database platform, but doctrine seems to keep using the EDIT: Aparently this feature was deprecated and has been removed in doctrine/dbal:^4.0, however the version constraint of doctrine/DoctrineBundle on dbal is loose and also allows configuration for Working example using driver middleware: <?php
declare(strict_types=1);
namespace App\Doctrine\Middleware;
use App\Doctrine\Platform\MySQLPlatform;
use Doctrine\DBAL\Driver;
use Doctrine\DBAL\Driver\Middleware;
use Doctrine\DBAL\Driver\Middleware\AbstractDriverMiddleware;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\ServerVersionProvider;
final class PlatformMiddleware implements Middleware
{
public function wrap(Driver $driver): Driver
{
return new class ($driver) extends AbstractDriverMiddleware {
/**
* {@inheritDoc}
*/
public function getDatabasePlatform(
ServerVersionProvider $versionProvider
): AbstractPlatform {
return new MySQLPlatform();
}
};
}
} |
I think this issue can be closed. As it is not desirable to add this to the It should always be a conscious decision of the developer to choose a specific platform, where features like datetime precision should be considered. Features can easily be added through driver middleware in the application itself. I have added a full example as a gist. |
This was suggested on doctrine/orm#6510, the user has the need of sorting entries with a more accurate precision. Some comments about this on #1515
The text was updated successfully, but these errors were encountered: