QT 6.8.0: Comparison of QDateTime is wrong

TheoSys

I retrieve a timestamp from a PostgreSQL database. The timestamp is in my case one that indicates that there is no valid timestamp available. It's 1900-01-01T00:00:00Z. The if in the following code is true, but why?

QJsonObject event;
...
QDateTime evId = query.value(23).toDateTime();              // The timestamp from the database (1900-01-01T00:00:00Z)
QDateTime invalidDate(QDate(1900, 1, 1), QTime(0, 0, 0));   // Reference date which marks an invalid timestamp

if (evId > invalidDate)    // TRUE! Why??
    event.insert("ev_id", evId.toString(Qt::ISODate));

Why is the expression of the if in the above code true?

A.T.

jsulm

@TheoSys Print out both datetimes to see what they really are.
Also timezone could play a role here I guess.

JonB

@TheoSys
Just a few weeks ago someone reported a problem parsing datetimes only for PostgreSQL. Might this be related? Try searching the forum, or I will post if I can locate it.

Otherwise check exactly what you are getting back in evId from the parse. QDateTime has toSecsSinceEpoch() or toStdSysMilliseconds(), call that on both your values to see if/how they differ.

TheoSys

@jsulm: I added already a debugging output:
The code:

MSG_DEBUG("ev_id: " << evId.toString(Qt::ISODate).toStdString() << " (" << invalidDate.toString(Qt::ISODate).toStdString() << ")");

printed out:

2024-11-04 10:48:10: DEBUG:   ev_id: 1900-01-01T00:00:00Z (1900-01-01T00:00:00)

The only difference is the missing Z in the QDateTime set in the code. The timezone was never set and the database field is defined as:

ev_id timestamp without time zone NOT NULL DEFAULT '1900-01-01 00:00:00'::timestamp without time zone

So the timezone should be UTC in any case.

JonB

@TheoSys
Although it's not the post I recall and tried to search for, have you seen e.g. https://bugreports.qt.io/browse/QTBUG-86450

Not possible as per e.g. https://www.postgresqltutorial.com/postgresql-timestamp/

"When you query timestamptz from the database, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection."

Is this relevant? I still suggest you do the comparisons I mentioned so you can (hopefully) diagnose why the test passes and what is going on.

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 🙂