[Akonadi] [Bug 483060] Akonadi sending UTC timestamps as local time to MySQL (and this failing around DST change) - QMYSQL bug?

[Daniel Vrátil]

https://bugs.kde.org/show_bug.cgi?id=483060

Daniel Vrátil <dvratil at kde.org> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|CONFIRMED                   |RESOLVED
      Latest Commit|                            |https://invent.kde.org/pim/
                   |                            |akonadi/-/commit/c98b05b79a
                   |                            |a849ebba2f4c309484428dd08df
                   |                            |042
         Resolution|---                         |FIXED

--- Comment #9 from Daniel Vrátil <dvratil at kde.org> ---
Git commit c98b05b79aa849ebba2f4c309484428dd08df042 by Daniel Vrátil.
Committed on 12/03/2024 at 07:41.
Pushed by dvratil into branch 'master'.

Fix handling of UTC vs. local time for database engines

Each database handles datetimes slightly differently. Our biggest
problem was MySQL, where the QMYSQL driver strips timezone, so
the MySQL server assumes the time is a local time. Akonadi was,
however, passing a UTC time, which then MySQL tried to interpret
as local time, which lead to two issues:
  1) it failed if the time was not a valid time in local timezone (DST)
  2) it required special handling when reading the time back from DB
     because MySQL returned UTC time but with local timezone set.

So instead we make sure we handle datetime properly when insert it
into the database. This can be handled from a singular place in
QueryBuilder, so it's fully transparent to the caller.

There's no migration for the existing database, since at worst the
time is slightly off and shouldn't have actual impact on functionality.

This has also been fixed independently in Qt, so in the future
we might be able to drop this completely.

M  +2    -0    autotests/server/CMakeLists.txt
A  +139  -0    autotests/server/dbdatetimetest.cpp     [License: LGPL(v2.0+)]
A  +1    -0    autotests/server/unittestenv
M  +1    -1    src/server/dbmigrator/dbmigrator.cpp
M  +1    -1    src/server/handler/itemfetchhelper.cpp
M  +0    -3    src/server/storage/entities-source.xsl
M  +0    -3    src/server/storage/entities.xsl
M  +6    -1    src/server/storage/querybuilder.cpp
M  +34   -24   src/server/utils.h

https://invent.kde.org/pim/akonadi/-/commit/c98b05b79aa849ebba2f4c309484428dd08df042

-- 
You are receiving this mail because:
You are the assignee for the bug.