QT 6.8.0: Comparison of QDateTime is wrong

[Christian Ehrlicher]

The psql driver now stores and retrieves all timestamps as utc - otherwise it is not possible to retrieve a useful value when the server and client has different time zones.

[TheoSys]

I tried now to print out the seconds since epoch and this is indeed different. The code

MSG_DEBUG("Epoch seconds: " << evId.toSecsSinceEpoch() << " (" << invalidDate.toSecsSinceEpoch() << ")");

printed:

2024-11-04 11:18:02: DEBUG:   Epoch seconds: -2208988800 (-2208992400)

Because the numbers are negative, evId is really greater then invalidDate. This explains the behavior of the if. Looks like this is a problem with PostgreSQL.

[JonB]

@Christian-Ehrlicher
So the bug I just linked is no longer relevant?
Christian, I can't find it, but just a few weeks ago I believe some asked here about a problem parsing/converting timestamps of datetime strings from PSQL and I think you answered. Do you recall it?

[Christian Ehrlicher]

You are right, the parsing failed for 6.8.0 but here it looks like a valid QDateTime is available so I would guess it's the utc thing. This was also mentioned in the release notes as important behavior change. I don't see another way to get a useful datetime from the database otherwise.

[JonB]

@Christian-Ehrlicher
Agreed. But purely FMI, it's annoying me that I cannot locate it, do you recall the link to the post here you made or a Qt bug number? I'd just like to find it :)

EDIT Ah, it was https://forum.qt.io/topic/159172/qsqlquery-postgres-and-timestamp-qdatetime-invalid. (Cannot find this from Google, even with title, irritating/surprising.) OP said they had filed a bug report but did not give link.

[TheoSys]

@JonB The difference is precisely 3600 seconds (1 hour). Although my invalidDate should be in UTC time, as well as the one from the database (evId), the timestamp from the database is the local time, which is MET (UTC+1). If my guess is right I have a problem when the program is running in a different time zone. In this special case I can add 24 hours and compare then. But because the timestamps converted to a string are equal something is strange.

# date -u -d @-2208988800
Mo 01 Jan 1900 00:00:00 UTC
# date -u -d @-2208992400
So 31 Dec 1899 23:00:00 UTC

[JonB]

@TheoSys
Just what was expected --- a UTC/local tz difference! Which is why @Christian-Ehrlicher suggests storing everything and comparing everything in UTC. (An issue I faced years ago, MS SQL Server and not Qt.) Or I believe I see Postgres has AT TIME ZONE, I don't know if you can make use of that when reading from PSQL (perhaps not as Qt/PSQL is generating the SQL to read the value from a datatime field)? Or, although I know nothing about Postgres, I may have come across a (SO?) post for Postgres saying you can do something like "set the timezone for a session" (to UTC) to control tz conversion?

[Christian Ehrlicher]

@JonB said in QT 6.8.0: Comparison of QDateTime is wrong:

something like "set the timezone for a session" (to UTC) to control tz conversion?

That's what the psql driver does now - it sets the timezone to utc.

[JonB]

@Christian-Ehrlicher
Then, if I understand right, that does not chime with

@TheoSys said in QT 6.8.0: Comparison of QDateTime is wrong:

the timestamp from the database is the local time, which is MET (UTC+1). If my guess is right I have a problem when the program is running in a different time zone.

? I am lost as to what tzs each of the OP's two QDateTimes are in (he should look at them). I think I should leave it then between you two PSQL users :)

[TheoSys]

@JonB said in QT 6.8.0: Comparison of QDateTime is wrong:

? I am lost as to what tzs each of the OP's two QDateTimes are in (he should look at them). I think I should leave it then between you two PSQL users :)

As I wrote above, the database field is defined without a timezone. According to PostgreSQL documentation this means it is stored unchanged and will not change, regardless in what timezone the database server is running.
The QDateTime is, according to the documentation, defined as UTC unless a time zone is set. Because of this I assumed that both timestamps are equal, which is not true. However if I do a select (with pgadmin4) like

SELECT date_part('epoch', ev_id) FROM event WHERE ev_num = 2109;

I get

-2208988800

Which is the same value as I get from

evId.toSecsSinceEpoch()

So far so well. This is what I expect. And this should be the correct epoch with UTC time.
When I set QDateTime(QDate(1900, 1, 1), QTime(0, 0, 0)) then it seems that Qt is taking this as a local time and subtract 1 hour (in my case). This would explain the difference. To me this looks like an error in Qt. Unless an explicit time zone was specified, the time should be taken as UTC.

[JonB]

@TheoSys
You were supposed to call QDateTime::timeZone() and QDateTime::timeSpec() on each of the two instances. (I have a feeling timespec() might be different?)
Assuming they are different, then figure back from there why each one is each.

Qt is taking this as a local time and subtract 1 hour (in my case). This would explain the difference. To me this looks like an error in Qt. Unless an explicit time zone was specified, the time should be taken as UTC.

What makes you assert (the second part of) this? Why "error", why do you think it should be taken as UTC? I suspect it's either "local time" or "unspecified". A lot of "default" time handling is taken as local time rather than UTC. If you want your constructed datetime to be taken as UTC I think you need to set that, e.g. by picking the constructor which takes a QTimeZone?

[Christian Ehrlicher]

https://doc.qt.io/qt-6/qdatetime.html#QDateTime-5

Constructs a datetime with the given date and time, using local time.

[TheoSys]

@Christian-Ehrlicher said in QT 6.8.0: Comparison of QDateTime is wrong:

Constructs a datetime with the given date and time, using local time.

I overlooked this. Then it's my fault.

[Christian Ehrlicher]

@TheoSys said in QT 6.8.0: Comparison of QDateTime is wrong:

Then it's my fault

No problem. Datetime with databases is not that easy as it seems so there might still be problems in the drivers even though I spent a lot of time with this 🙂