Why `datetimetz` removes the time zone information in sqlite platform ?

[dnclain]

The problem

I'm using doctrine 2.14.1 with sqlite databases. Those databases are also used by other applications in other languages, or PHP with a custom made ORM, so I need to ensure that doctrine and other ORM in other languages behave in the same way with most of column types supported by doctrine (except 'object'). When I use datetimetz type, I expect this column type to behave differently than the datetime type. So I store externally a date with timezone in that format :

$date->format('Y-m-d H:i:sO')

Sadly, when reading from doctrine, it fails because the expected format is Y-m-d H:i:s (in $platform->getDateTimeTzFormatString()).

The Question

So the question is why the datetimetz behaves the same way than datetime in sqlite (no problem with postgres) ? Is there a technical problem that prevent that platform to store the timezone ? The tests I've made show that at least sqlite 3.34.1 stores correctly the timezone. It seems to do that for a long time now (https://www.sqlite.org/lang_datefunc.html). Good time to update the SQLite platform in DBAL ?

Proposed solution

Change the result of Doctrine\DBAL\Platforms\SqlitePlatform::getDateTimeTzTypeDeclarationSQL() to Y-m-d H:i:sO

Thanks in advance.

[derrabus]

Seems like this feature simply hasn't been implemented in DBAL. Feel free to do so.

[phansys]

As far as I can check, the timezone support is available at least from SQLite 2.8.7, released on 2003-12-04. See:

• https://www.sqlite.org/src/timeline?c=68ef9b45bd3abded&y=a
• https://www.sqlite.org/src/info/68ef9b45bd3abded

I've created #6006 as an improvement, but given this feature surely exists even before the first release of doctrine/dbal, I think we can consider that change as a fix.