QGIS can't create layers with DateTime with UTC data

Question

I was creating a geopackage of points with a DateTime field, and the QGIS (version 3.32.3-Lima) seems to treat it as naive localtime data.

Layer/CreateLayer/NewGeopackageLayer:

Layer properties/AttributeForm/ my datetime field/ -- Choose default DisplayFormat, Default value=now()

Layer/edit/Add Point: (This shows my current local time of creating the point)

Save file and do an ogrinfo -al on the file:

% ogrinfo UtcTimezoneTests.gpkg
INFO: Open of `UtcTimezoneTests.gpkg'
      using driver `GPKG' successful.
1: UtcTimezoneTests (Point)
drf@V51292 ~ % ogrinfo -al UtcTimezoneTests.gpkg
INFO: Open of `UtcTimezoneTests.gpkg'
      using driver `GPKG' successful.

Layer name: UtcTimezoneTests
Geometry: Point
Feature Count: 1
Extent: (-69.513188, 36.074548) - (-69.513188, 36.074548)
Layer SRS WKT:
GEOGCRS["WGS 84",
    ENSEMBLE["World Geodetic System 1984 ensemble",
        MEMBER["World Geodetic System 1984 (Transit)"],
        MEMBER["World Geodetic System 1984 (G730)"],
        MEMBER["World Geodetic System 1984 (G873)"],
        MEMBER["World Geodetic System 1984 (G1150)"],
        MEMBER["World Geodetic System 1984 (G1674)"],
        MEMBER["World Geodetic System 1984 (G1762)"],
        MEMBER["World Geodetic System 1984 (G2139)"],
        ELLIPSOID["WGS 84",6378137,298.257223563,
            LENGTHUNIT["metre",1]],
        ENSEMBLEACCURACY[2.0]],
    PRIMEM["Greenwich",0,
        ANGLEUNIT["degree",0.0174532925199433]],
    CS[ellipsoidal,2],
        AXIS["geodetic latitude (Lat)",north,
            ORDER[1],
            ANGLEUNIT["degree",0.0174532925199433]],
        AXIS["geodetic longitude (Lon)",east,
            ORDER[2],
            ANGLEUNIT["degree",0.0174532925199433]],
    USAGE[
        SCOPE["Horizontal component of 3D system."],
        AREA["World."],
        BBOX[-90,-180,90,180]],
    ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
FID Column = fid
Geometry Column = geometry
datetime: DateTime
otherDateTime: DateTime
OGRFeature(UtcTimezoneTests):1
  datetime (DateTime) = 2024/04/25 12:14:12.705
  otherDateTime (DateTime) = (null)
  POINT (-69.5131875565197 36.0745480577934)

I would think that the QGIS defaults should create either UTC or timezone aware data, not timezone-naive data that now() produces.

Instead of the timezone-naive stamp that this produced, how do I:

1. save the time in the Geopackage file with local time zone
2. save the time in the Geopackage file with UTC stamp

Edit:

I tried these formulas for defaults:

datetime:    now()
DateTimeTZ:   format_date( now(),'yyyy-MM-ddTHH:mm:sst')
DateTimeUTC: format_date(datetime_from_epoch(epoch(now())+3600000*4),'yyyy-MM-ddTHH:mm:ssZ')

and got:

with

...
OGRFeature(UtcTimezoneTests):1
  datetime (DateTime) = 2024/04/25 12:14:12.705
  DateTimeTZ (DateTime) = (null)
  DateTimeUTC (DateTime) = (null)
  POINT (-69.5131875565197 36.0745480577934)

OGRFeature(UtcTimezoneTests):2
  datetime (DateTime) = 2024/04/25 13:10:42.304
  DateTimeTZ (DateTime) = (null)
  DateTimeUTC (DateTime) = 2024/10/25 13:04:56
  POINT (-67.63556092185 37.2342586262659)

OGRFeature(UtcTimezoneTests):3
  datetime (DateTime) = 2024/04/25 13:11:07.891
  DateTimeTZ (DateTime) = (null)
  DateTimeUTC (DateTime) = 2024/11/25 13:04:22
  POINT (-67.4698879834968 34.6939402381834)

OGRFeature(UtcTimezoneTests):4
  datetime (DateTime) = 2024/04/25 13:30:46.396
  DateTimeTZ (DateTime) = 2024/04/25 13:30:46
  DateTimeUTC (DateTime) = 2024/04/25 17:30:46
  POINT (-63.3280645246666 31.1043599071972)

Note that the last point isn't in ISO9660 format and they all seems to lack timezones.

and when I close and re-import the file/layer they are squashed to the local timezone:

QGIS 3.36.2-Maidenhead:

Same for QGIS 3.36.2-Maidenhead. With the same forumlas in the form, adding another point shows consistent timezone stamps on the create and identify windows, but saves the file with non-ISO non-TZ timestamps and reads them back in as inconsistent timezone-naive stamps:

OGRFeature(UtcTimezoneTests):5
  datetime (DateTime) = 2024/04/25 14:17:39.723
  DateTimeTZ (DateTime) = 2024/04/25 14:17:39
  DateTimeUTC (DateTime) = 2024/04/25 18:17:39
  POINT (-67.4227641748578 35.9721232848221)

Edit: the closest I've gotten still drops the Z in a geopackage.

I defined the datetime field as:

• A custom ISO8601 with Z: yyyy-MM-ddTHH:mm:ssZ towards https://www.geopackage.org/spec121/#table_column_data_types
• An "Overwrite Field Format" with the same
• A default with the same format, but a 3600000*local_offset formula

and it ends up in the .gpkg file with the expected UTC time numbers but non-conformant timezone-naive data lacking the Z:

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

It is interesting that the .000 was added by something in the processing chain. I'm not sure what formats QGIS's geopackage time data, but it doesn't quite seem to be the string in "Overwrite Field Format".

Answer 1

Would it be possible to create a new function with the function editor? Click on the green Plus-sign at the bottom left corner, and name it maybe time_utc, then save and load.

    from qgis.gui import *
    from datetime import timezone 
    import datetime 
    
    @qgsfunction(group='Python', referenced_columns=[])
    def time_utc():
        """
        Calculates the current UTC time..
        <h2>Example:</h2>
        <ul>
          <li>'2024-04-25 18:16:23.503094+00:00'</li>
        </ul>
        """
        return str(datetime.datetime.now(timezone.utc))