QGIS can't create conformant geopackage files with UTC "Z" timestamps

[drf5n]

What is the bug or the crash?

QGIS appears to filters the timezone information off of geojson and geopackage data and writes geopackage files with non-conformant timezone naive timestamps.

Steps to reproduce the issue

Follow along with https://gis.stackexchange.com/questions/480462/qgis-create-layers-with-datetime-with-utc-data

1. Create a geopackage point layer with a Date/Time field
2. Define the display, Overwrite Field Format and Default as yyyy-MM-ddTHH:mm:ssZ
   ** Display Format: yyyy-MM-ddTHH:mm:ssZ
   ** Overwrite Field Format: yyyy-MM-ddTHH:mm:ssZ
   ** Default format_date(datetime_from_epoch(epoch(now())+3600000*4),'yyyy-MM-ddTHH:mm:ssZ')
3. Add some points and save the file
4. examine the resultant gpkg file with sqlite:

% echo ".headers on\n select * from UtcTest4;" |sqlite3 ./UtcTest4.gpkg
fid|geometry|utcTimestamp
1|GP|2024-04-27T02:33:39.000
%

5. note that the Overwrite Field Format was not used--The Z is missing and there are milliseconds in 2024-04-27T02:33:39.000

Versions

<style type="text/css"> p, li { white-space: pre-wrap; } </style>

QGIS version	3.36.2-Maidenhead	QGIS code revision	6d25052
Qt version	5.15.2
Python version	3.9.5
GDAL/OGR version	3.3.2
PROJ version	8.1.1
EPSG Registry database version	v10.028 (2021-07-07)
GEOS version	3.9.1-CAPI-1.14.2
SQLite version	3.35.2
PDAL version	2.3.0
PostgreSQL client version	unknown
SpatiaLite version	5.0.1
QWT version	6.1.6
QScintilla2 version	2.11.5
OS version	macOS 14.2
Active Python plugins
FreehandRasterGeoreferencer	0.8.3
quick_map_services	0.19.34
MagneticDeclination	2.4.4
DEMto3D	3.51
LAStools	2.1.0
GeoCoding	2.19
latlontools	3.6.20
processing	2.12.99
grassprovider	2.12.99
db_manager	0.1.20
MetaSearch	0.3.6

QGIS version 3.36.2-Maidenhead QGIS code revision [6d25052](https://github.com/qgis/QGIS/commit/6d250527200) Qt version 5.15.2 Python version 3.9.5 GDAL/OGR version 3.3.2 PROJ version 8.1.1 EPSG Registry database version v10.028 (2021-07-07) GEOS version 3.9.1-CAPI-1.14.2 SQLite version 3.35.2 PDAL version 2.3.0 PostgreSQL client version unknown SpatiaLite version 5.0.1 QWT version 6.1.6 QScintilla2 version 2.11.5 OS version macOS 14.2

Active Python plugins
FreehandRasterGeoreferencer
0.8.3
quick_map_services
0.19.34
MagneticDeclination
2.4.4
DEMto3D
3.51
LAStools
2.1.0
GeoCoding
2.19
latlontools
3.6.20
processing
2.12.99
grassprovider
2.12.99
db_manager
0.1.20
MetaSearch
0.3.6

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

Per https://www.geopackage.org/spec/#table_column_data_types a conformant gpkg DateTime should be:

ISO-8601 date/time string in the form YYYY-MM-DDTHH:MM[:SS.SSS]Z with T separator character, Z suffix for coordinated universal time (UTC), and encoded in either UTF-8 or UTF-16. Seconds and fractional seconds are OPTIONAL. Fractional seconds MAY have fewer or more than three digits. Stored as SQLite TEXT (see TEXT above).

If QGIS is writing gpkg files, it should meet the standard. (I also think that if QGIS is reading non-standard .gpkg files, it should assume UTC.)

Some of my experiments (with screenshots) in trying to create gpkg file with UTC timestamps are shown on:

https://gis.stackexchange.com/questions/480462/qgis-create-layers-with-datetime-with-utc-data

I tried to create a new QGIS profile, but failed. The "New Profile" button didn't seem to exist:

[drf5n]

One hack to working with UTC data is to set your Timezone environment variable to UTC (TZ=UTC) before starting QGIS and then QGIS won't display in local time.

>>> print(QtCore.QDateTime.currentDateTime())

PyQt5.QtCore.QDateTime(2024, 5, 8, 20, 28, 28, 393)
>>> print(QtCore.QDateTime.currentDateTimeUtc())

PyQt5.QtCore.QDateTime(2024, 5, 8, 20, 28, 29, 879, PyQt5.QtCore.Qt.TimeSpec(1))

Writing data with UTC timezones still seems awkward.

For Geopackage files, it seems like QGIS should always write it with the standard yyyy-mm-ddTHH:MM:SSZ format. Which would make sense if TZ=UTC, but might have problems if TZ is not UTC--it needs something more to know if it should translate what seems to be a naive yyyy-mm-ddTHH:MM:SS timestamp from local time to UTC.

https://gis.stackexchange.com/questions/316882/valid-datetime-in-geopackage suggests using the validate_gpkg.py script:

https://github.com/OSGeo/gdal/blob/master/swig/python/gdal-utils/osgeo_utils/samples/validate_gpkg.py

If you File/create/geopackage, create a point geometry layer, add a datetime field, create, and then edit add a points and select a date on the default calendar form:

... and then save the file, and run the OSGEO validator on the resultant file with the option that actually checks the table data, you get this sort of error:

 % python3 ~/Work/gdal/swig/python/gdal-utils/osgeo_utils/samples/validate_gpkg.py --extra junkDatetimeTest.gpkg 
In column datetimeutc, text 2024-05-09T00:00:00.000 found which is not a valid DATETIME

Looking at https://stackoverflow.com/a/4030812/1653571 it looks like actually assigning any timezone in Qt requires an extra step after parsing a string, such as date.setTimeSpec(Qt::UTC); An extra step that isn't often done within QGIS's processing:

https://github.com/search?q=repo%3Aqgis%2FQGIS%20setTimeSpec&type=code