QT 6.8.0: Comparison of QDateTime is wrong
-
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.
@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.
-
@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?@JonB The difference is precisely 3600 seconds (1 hour). Although my
invalidDateshould 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 The difference is precisely 3600 seconds (1 hour). Although my
invalidDateshould 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@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 hasAT 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? -
@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 hasAT 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?@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.
Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
@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.
@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 :) -
@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 :)@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.
TheQDateTimeis, 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) likeSELECT date_part('epoch', ev_id) FROM event WHERE ev_num = 2109;I get
-2208988800Which 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 setQDateTime(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 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.
TheQDateTimeis, 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) likeSELECT date_part('epoch', ev_id) FROM event WHERE ev_num = 2109;I get
-2208988800Which 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 setQDateTime(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.@TheoSys
You were supposed to call QDateTime::timeZone() and QDateTime::timeSpec() on each of the two instances. (I have a feelingtimespec()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? -
https://doc.qt.io/qt-6/qdatetime.html#QDateTime-5
Constructs a datetime with the given date and time, using local time.
-
https://doc.qt.io/qt-6/qdatetime.html#QDateTime-5
Constructs a datetime with the given date and time, using local time.
@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.
-
T TheoSys has marked this topic as solved on
-
@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.
@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 🙂
