Extension defining data type for representing timestamps with a UTC offset (and possibly a time zone region)?

[heidivanparys]

I came across a discussion on the QGIS-Developer mailinglist on better support for time zones in QGIS (involving @rouault).

Copying the relevant part in here:

Also I wonder if it would be possible to work with the GPKG developers to provide better timezone support there as well.

This is more about finding how to convince the GeoPackage spec maintainers that it is worth adding it than a technical one. I guess they wouldn't want to break compatibility with the core specification, so a clean way forward would be to deal with that as an extension, since GeoPackage has a mechanism to indicate that a given file uses extensions.

The gpkg_extensions table (https://www.geopackage.org/spec/#_extensions) could for example have one record to declare each (layer, field) that uses an extended date-time format

table_name	column_name	extension_name	definition	scope
{name_of_table_using_an_extended_datetime_column}	{name_of_column_using_extended_datetime}	ogr_extended_datetime 1	https://gdal.org/geopackage_extended_datetime.html 2	read-write

What would be to decide is if the extension consists in relaxing the constraints of the existing DATETIME data type (to accept any time zone, or lack of time zone), or adding a new DATETIME_EXTENDED data type.

I would like to hear

1. whether this would indeed be the best way forward;
2. whether there would any support to make such an extension, and implement it.

In a national context, timestamps in datasets are often in the local time. I definitely agree that we, as data providers, should work towards exchange also our national data with a clear indication of the time zone. However, it would be great to be able not to have to convert the timestamps to UTC, but to be able to provide the timestamps in +01:00 and +02:00. Not because we cannot do that, it's more from a user-friendliness perspective.

I also noticed that IETF is working on a new standard, Date and Time on the Internet: Timestamps with additional information. See also https://github.com/ietf-wg-sedate/draft-ietf-sedate-datetime-extended/. It would be an extension of RFC 3339, Date and Time on the Internet: Timestamps. A conforming timestamp would e.g. be 2023-03-14T15:40:00+01:00[Europe/Copenhagen]. It is currently a draft, just to be clear.

So, perhaps, such an GeoPackage extension could even support timestamps following that format, with the (optional) IANA time zone region (in that draft standard called the IXDTF format).

The name of this new data type could e.g. be ZONEDDATETIME (see https://nodatime.org/3.1.x/api/NodaTime.ZonedDateTime.html and https://tc39.es/proposal-temporal/docs/#Temporal-ZonedDateTime).

Any feedback would be very welcome!

Footnotes

1. if the OGC SWG would accept to officialize it as a registered extension, that would become gpkg_extended_datetime ↩

2. or URL to updated GeoPackage spec if officialized ↩

[jyutzler]

We opened an issue with the OAB. https://portal.ogc.org/?m=projects&a=view&project_id=228&tab=5&act=details&issue_id=1695

The OAB moved to continue using UTC as it is consistent with industry best practices. Therefore, I am closing this ticket.

[cnreediii]

@jyutzler Thanks for doing this. Clarifies CDB work which in draft states: "The specification of date and time in any CDB metadata SHALL be specified in UTC" followed with appropriate references and notes.