UTC imported as UTC but subsequently reported as a local time zone when reopened

[djwalkr]

What is the bug or the crash?

Issue: When a CSV file is imported into QGIS with a date time column formatted following ISO 8600 including the addition of Zulu time, it is properly identified a UTC in the metadata. When exported as a GeoPackage and then reopened the time column will be expressed using the time zone value for the operating system of the computer. Note this time is not adjusted to the time zone of the computer it is reported as in the time zone of the computer. Thus 14:00:00 UTC will be 14:00:00 EST (or whatever the cpu time zone is).

Steps to reproduce the issue

1. Format a date time stamp that matches ISO 8600 precisely (with Zulu time) for features in a CSV
2. Import the CSV with the date time field set to DateTime
3. View attribute table (UTC will show as the time zone)
4. Export to GeoPackage.
5. View attribute table of newly created GeoPackage (your time zone will now appear in table WITHOUT adjusting the time to your zone).

Versions

This bug seems to impact all versions of QGIS that I've worked with, and there are many threads that discuss time stamps including that this is a 'default' behaviour. However, to be version specific the bug can be reproduced on:

QGIS version 3.28.15-Firenze
QGIS code revision 252ad49ddcb
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 13.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

Note this bug is not about 'enabling time zone support' or adding time UTC offsets to the program code, it is that QGIS is misreporting time zonse. To fix it does not require time zones be adjusted (that would require time zone support), only that the zone information be left alone. If the system has already recognized it as UTC then save it as such. If the zone is not UTC (and QGIS doesn't support it - which is basically all zones) the user should be warned that the zone is not supported and then the zone should be set to 'unsupported' or whatever NOT the zone for the operating system. Alternatively, ALL time zones should be reported as NA if QGIS lacks the ability to properly save a UTC time stamp as UTC in a GeoPackage. This is a bug and not a request because QGIS is fundamentally changing time stamp values by misrepresenting the zone (also called lying) by adding zone information to files based on the geolocation of a computer. It is equivalent to QGIS taking a WGS84 lat long layer and assigning a CRS of UTM Zone (insert zone here based on computer geolocation) while leaving the actual coordinates as decimal degrees and reporting it is UTM!

[agiudiceandrea]

@djwalkr thanks for reporting. May you please provide a sample layer and exact steps and parameters (how you exactly import the csv layer and with which parameters, and how you exactly export the layer and with which parameters)?

[drf5n]

It may be an issue with the Properties/AttributeForm/DisplayFormat--If you export it directly, the data is copied directly into the gpkg file. If you touch the data in any way -- Edit the attribute table and add a second, or attempt to replace the space with a T, it reclassifies it as belonging to your local timezone and drops the UTC flag

Here's a csv file with timestamps in the geopackage-conformant format:

Latitude,Longitude,Name,Timestamp
48.1,0.25,"First point","2024-04-27T02:33:39Z"
49.2,1.1,"Second point","2024-04-27T02:34:05Z"
47.5,0.75,"Third point","2024-04-27T02:34:14Z"

Importing the layer with autodetect and the X & Y possible names:

gets a file with an attribute table like:

Exporting the geopackage:

...you get a geopackage file with non-conformant DATETIME :

% echo ".headers on\n select * from csvtest;" |sqlite3 ~/csvtest.gpkg       
fid|geom|Latitude|Longitude|Name|Timestamp
1|GP|48.1|0.25|First point|4/27/24 02:33:39 (UTC)
2|GP|49.2|1.1|Second point|4/27/24 02:34:05 (UTC)
3|GP|47.5|0.75|Third point|4/27/24 02:34:14 (UTC)
% 

I've found awkward ways to write conformant geopackage timestamps, but you have to override and hardcode the timezone as "Z' in the display format, and then export the displayed string:

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

and

#57262