QDateTime UTC persistence issue

[ahsanullah-8bit]

Sup!

I've been trying to use ODB in a Qt 6.9 project. Upon taking a look at the source code of the Qt profile, it seems the QDateTime is converted back and forth using a custom format string yyyy-MM-ddTHH:mm:ss.zzz at line 64 and 45.

I see, you guys might be trying to make it work with the built-in SQLite methods, which doesn't append the suffix Z. But the problem here is, loading the time using that custom format string defaults the timeSpec to Qt::LocalTime. You persist a QDateTime in Qt::UTC and it reads it back as Qt::LocalTime. Is there a fix for this or should I do something myself?

[boris-kolpackov]

Hm, I am actually not sure that we treat QDateTime as being in UTC timezone when mapped as TEXT (unlike INTEGER). I think we just store it as is and leave it up to the application to interpret as being in UTC or local timezone. We could have included explicit time zone, but then the representation won't be interoperable with SQLite functions (https://www.sqlite.org/lang_datefunc.html).

On the other hand, it seems possible for an application to "reinterpret" local QDateTime as UTC with the setTimeSpec(Qt::UTC) call.

[ahsanullah-8bit]

Hm, I am actually not sure that we treat QDateTime as being in UTC timezone when mapped as TEXT (unlike INTEGER). I think we just store it as is and leave it up to the application to interpret as being in UTC or local timezone. We could have included explicit time zone, but then the representation won't be interoperable with SQLite functions (https://www.sqlite.org/lang_datefunc.html).

On the other hand, it seems possible for an application to "reinterpret" local QDateTime as UTC with the setTimeSpec(Qt::UTC) call.

As I said, it's pretty reasonable to be compatible with SQLite's own functionality. But it should at least be mentioned in docs, as I had to go through the code itself. It's not bad, but ....

The problem is, without Z at the end, the datetime is read as Qt::LocalTime. If you use setTimeSpec(Qt::UTC), the standard conversion from Local to UTC happens, stripping -5 from the hours in my case. I had to "reinterpret" it through raw format yyyy-MM-ddTHH:mm:ss.zzz string (same as odb does), then append Z and read it back using QDateTime.

I'm not complaining, you guys build an amazing thing right here! The docs should just warn others about this.

You may close this issue now, if it's not of use anymore.

[boris-kolpackov]

If you use setTimeSpec(Qt::UTC), the standard conversion from Local to UTC happens, stripping -5 from the hours in my case.

Hm, according to the Internet, it doesn't convert, just changes the timespec. I just tried it myself to make doubly sure:

QDateTime dt (
    QDateTime::fromString (
      QString::fromLatin1 ("2025-07-22T15:36:32.123"),
      "yyyy-MM-ddTHH:mm:ss.zzz"));

cout << dt.toString ("yyyy-MM-ddTHH:mm:ss.zzz t").toLatin1 ().constData () << endl;
dt.setTimeSpec (Qt::UTC);
cout << dt.toString ("yyyy-MM-ddTHH:mm:ss.zzz t").toLatin1 ().constData () << endl;

On my machine, with local time being SAST (GMT+2), I get:

2025-07-22T15:36:32.123 GMT+2
2025-07-22T15:36:32.123 UTC

Having thought some more on this, I think we should just make the default mapping UTC-only. That is, assert in set_image() that timespec is UTC and then set timespec to UTC in get_image() (or, better yet, parser it as UTC as in the custom mapping below). And if someone wants to store local time in the database (which feels like just asking for trouble), then they can provide their own custom mapping along these lines:

#pragma db map type(QDateTime) as(std::string) \
  to(std::string ((?).toString ("yyyy-MM-ddTHH:mm:ss.zzz").toLatin1 ().constData ())) \
  from(QDateTime::fromString (QString::fromLatin1 ((?).c_str ()), "yyyy-MM-ddTHH:mm:ss.zzz"))

BTW, you can use the same technique to get UTC time currently:

#pragma db map type(QDateTime) as(std::string) \
  to(std::string ((?).toString ("yyyy-MM-ddTHH:mm:ss.zzz").toLatin1 ().constData ())) \
  from(QDateTime::fromString (QString::fromLatin1 ((?).c_str ()) + 'Z', "yyyy-MM-ddTHH:mm:ss.zzzt"))

[ahsanullah-8bit]

Oh, yeah. My bad!

I meant QDateTime.toUTC(). I didn't use setTimeSpec(...).

I think we should just make the default mapping UTC-only.
And if someone wants to store local time in the database (which feels like just asking for trouble) ...

Yes, someone recommended to not store LocalTime in a database at all.